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])'"

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()
                  };

more