Converting SQL query to LINQ query

Question

This is the SQL query which needs to converted to LINQ query.

SELECT  pq.DocumentQueueID, 
    pq.DocumentQueueName, 
    pq.DepartmentName, 
    pq.UserLocation,
    ISNULL(T.DocumentCount, 0) DocCount, 
    ISNULL(CONVERT(VARCHAR(50),T.OldestDocumentDate),'') IngestionDateTime,
    ISNULL(B.UserName, '') UserName
FROM [dbo].[listPLDQueues] pq 
LEFT OUTER JOIN 
(
    SELECT dds.CurrentDocumentQueue, 
    SUM(dds.ImportPageCount) as DocumentCount, 
    MIN(dds.IngestionDateTime) as OldestDocumentDate
    FROM [dbo].[dataDocumentStats] dds
    GROUP BY dds.CurrentDocumentQueue
) AS T ON T.CurrentDocumentQueue = pq.DocumentQueueID
LEFT OUTER JOIN
(   SELECT duq.DocumentQueueID, UserName = 
    STUFF((SELECT ', ' + uq.UserDisplayName
    FROM [dbo].[dataUserQueues] uq
    WHERE uq.DocumentQueueID = duq.DocumentQueueID
    FOR XML PATH('')),1,2,'')
 FROM [dbo].[dataUserQueues] duq
 GROUP BY duq.DocumentQueueID
 ) AS B ON B.DocumentQueueID = pq.DocumentQueueID 
WHERE UPPER(WorkflowType) = 'INDEXING'

What i have done so far in LINQ query..

 var indexSummary = _eimStatsDB.listPLDQueues
          .Join(_eimStatsDB.dataDocumentStats,
              pld => pld.DocumentQueueID,
              dds => dds.CurrentDocumentQueue,
              (pld, dds) => new { pldQueues = pld, dataDocument = dds })            
          .Where(a => a.pldQueues.WorkflowType.ToLower() == "indexing")
          .GroupBy(a => a.pldQueues.DocumentQueueID)
          .ToList()
          .Select(a => new
          {
              DocumentQueueId = a.Key,
              DocumentQueueName = a.Select(i => i.pldQueues.DocumentQueueName).FirstOrDefault(),
              DepartmentName = a.Select(i => i.pldQueues.DepartmentName).FirstOrDefault(),
              DocumentCount = a.Sum(i => i.dataDocument.ImportPageCount),
              OldestDocumentDate = a.Min(i => i.dataDocument.IngestionDateTime),
              UserLocation = a.Select(i => i.pldQueues.UserLocation).FirstOrDefault(),
              IsChecked = false
          });

        var userNames = _eimStatsDB.dataUserQueues
            .GroupBy(e => e.DocumentQueueID)
            .ToList()
            .Select(e => new
            {
                DocumentId = e.Key,
                UserName = string.Join(",", e.Select(i => i.UserDisplayName))
            });

        var listPLDQueue = from pldqueue in _eimStatsDB.listPLDQueues
                           where pldqueue.WorkflowType == "Indexing"
                           select pldqueue;

        var result = from pldqueue in listPLDQueue
                     join iS in indexSummary
                     on pldqueue.DocumentQueueID equals iS.DocumentQueueId into pldjoin
                     from pld in pldjoin.DefaultIfEmpty()
                     join un in userNames
                     on pld.DocumentQueueId equals un.DocumentId into gj
                     from subuser in gj.DefaultIfEmpty()
                     select new
                     {
                         DocumentQueueId = pld.DocumentQueueId,
                         DocumentQueueName = pld.DocumentQueueName,
                         DepartmentName = pld.DepartmentName,
                         DocumentCount = (pld.DocumentCount == null ? 0 : pld.DocumentCount),
                         OldestDocumentDate = (pld.OldestDocumentDate == null? Convert.ToDateTime(string.Empty) : pld.OldestDocumentDate),
                         UserLocation = pld.UserLocation,
                         IsChecked = pld.IsChecked,
                         Usernames = (subuser == null ? string.Empty : subuser.UserName)
                     };

The last query which returns result gives error : "Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context."

Is there any other better way of achieving this where all the different LINQ queries can be combined together?

Tetsuya Yamamoto · Accepted Answer

After examining entire query structure, I found other two join sources besides listPLDQueue are IEnumerable collections with anonymous type parameter, where Entity Framework only able to refer IEnumerable with primitive type as type parameter or IQueryable when perform join operation.

Try dropping or commenting all ToList() method to assign IQueryable for both indexSummary and userNames, then consider giving proper class name instead using anonymous type like this:

var indexSummary = _eimStatsDB.listPLDQueues
          .Join(_eimStatsDB.dataDocumentStats,
              pld => pld.DocumentQueueID,
              dds => dds.CurrentDocumentQueue,
              (pld, dds) => new { pldQueues = pld, dataDocument = dds })            
          .Where(a => a.pldQueues.WorkflowType.ToLower() == "indexing")
          .GroupBy(a => a.pldQueues.DocumentQueueID)
        //.ToList() --> this converts IQueryable to IEnumerable, which should be dropped
          .Select(a => new listPLDQueues() // change this assignment to your model class name
          {
              DocumentQueueId = a.Key,
              DocumentQueueName = a.Select(i => i.pldQueues.DocumentQueueName).FirstOrDefault(),
              DepartmentName = a.Select(i => i.pldQueues.DepartmentName).FirstOrDefault(),
              DocumentCount = a.Sum(i => i.dataDocument.ImportPageCount),
              OldestDocumentDate = a.Min(i => i.dataDocument.IngestionDateTime),
              UserLocation = a.Select(i => i.pldQueues.UserLocation).FirstOrDefault(),
              IsChecked = false
          });

var userNames = _eimStatsDB.dataUserQueues
            .GroupBy(e => e.DocumentQueueID)
          //.ToList() --> this converts IQueryable to IEnumerable, which should be dropped
            .Select(e => new dataUserQueues() // change this assignment to your model class name
            {
                DocumentId = e.Key,
                UserName = string.Join(",", e.Select(i => i.UserDisplayName))
            });

Each assignment will return IQueryable (T is assigned to DB model class name, i.e. IQueryable and IQueryable) which suitable to use them in result assignment containing join query.

Converting SQL query to LINQ query

Answers (1)

Related Questions