CODEWITHSUNDEEP
entity-frameworklinqcountgroup-bylinq-to-entities
Jenan
Jenan

Reputation: 3320

How can I fix linq query to select count of ids with group by?

I want to create this SQL query to linq:

SELECT  
    COUNT(m.FromUserId) AS Messages,
    m.FromUserId AS UserId
FROM    
    dbo.ChatMessages m
INNER JOIN 
    dbo.ChatMessagesRead mr ON mr.ChatMessageId = m.ChatMessageId
WHERE   
    m.ToUserId = @toUserId
GROUP BY 
    m.FromUserId

I have tried create following linq query:

var messages = from m in _dbContext.ChatMessages
               join mread in _dbContext.ChatMessagesRead on m.ChatMessageId equals mread.ChatMessageId
               where m.ToUserId == userId
               group m by m.FromUserId into g
               select new
                      {
                         UserId = g.Key,
                         Messages = g.Count()
                      };

var messagesList = messages.ToList();

But this doesn't work.

How can I fix this linq query?

I get this exception:

Expression of type 'System.Func2[Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier2[Project.BL.ChatMessages.ChatMessages,Project.BL.ChatMessages.ChatMessagesRead],System.Int32]' cannot be used for parameter of type 'System.Func2[<>f__AnonymousType12[Project.BL.ChatMessages.ChatMessages,Project.BL.ChatMessages.ChatMessagesRead],System.Int32]' of method 'System.Collections.Generic.IEnumerable1[System.Linq.IGrouping2[System.Int32,Project.BL.ChatMessages.ChatMessages]] _GroupBy[<>f__AnonymousType12,Int32,ChatMessages](System.Collections.Generic.IEnumerable1[<>f__AnonymousType12[Project.BL.ChatMessages.ChatMessages,Project.BL.ChatMessages.ChatMessagesRead]], System.Func2[<>f__AnonymousType12[Project.BL.ChatMessages.ChatMessages,Project.BL.ChatMessages.ChatMessagesRead],System.Int32], System.Func2[<>f__AnonymousType1`2[Project.BL.ChatMessages.ChatMessages,Project.BL.ChatMessages.ChatMessagesRead],Project.BL.ChatMessages.ChatMessages])'"

Upvotes: 0

Views: 630

Answers (3)

Vincent
Vincent

Reputation: 3746

I'm facing the same issue and I've found that there is an opened issue on the Entity Framework Core bugtracker

The only workaround for now seems to split the request in two. 

var filtered = (from m in _dbContext.ChatMessages
                join mread in _dbContext.ChatMessagesRead on m.ChatMessageId equals mread.ChatMessageId
                where m.ToUserId == userId
               select m).ToList();

var messages = from m in filtered
               group m by m.FromUserId into g
               select new
                  {
                     UserId = g.Key,
                     Messages = g.Count()
                  };

Upvotes: 1

Muhammad Mohsin Muneer
Muhammad Mohsin Muneer

Reputation: 433

you can try this

var res = ctx.MyTable  // Start with your table
        .GroupBy(r => r.id) / Group by the key of your choice
        .Select( g => new {Id = g.Key, Count = g.Count()}) // Create an anonymous type w/results
        .ToList(); // Convert the results to List

Upvotes: 1

Gabor
Gabor

Reputation: 3246

Your code should work. However I created another version of your query using extension methods.

var messages =
    _dbContext
        .ChatMessages
        .Where(message => message.ToUserId == userId)
        .Join(
            _dbContext.ChatMessageRead,
            message => message.ChatMessageId,
            readMessage => readMessage.ChatMessageId,
            (m, mr) => m.FromUserId
        )
        .GroupBy(id => id)
        .Select(group =>
            new
            {
                UserId = group.Key,
                Messages = group.Count()
            }
        );

Could you please try it if it also throws the same exception or not?

Upvotes: 0

Related Questions