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?

In which line the error occurs? I think the problem is related to your join query when assigning result. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Nov 11, 2016 at 1:09
yes, that's correct the error is while assigning result..i am not able to figure it out.. — Nirav Parmar
– Nirav Parmar, Commented Nov 11, 2016 at 1:52
You're attempting to join a database object with collections stored in memory which throws NotSupportedException. Try remove ToList() method from userNames declaration before Select statement, since EF can only referred to join if the IEnumerable type parameter is any primitive types (e.g. int), but you can use IQueryable to do so. — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Nov 11, 2016 at 1:59

Community · Accepted Answer · 2017-05-23 12:01:19Z

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> (T is assigned to DB model class name, i.e. IQueryable<listPLDQueues> and IQueryable<dataUserQueues>) which suitable to use them in result assignment containing join query.

Collectives™ on Stack Overflow

Converting SQL query to LINQ query

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related