c# - Count in Linq query with multiple join EF

Question

Hi Helpies I am new to EF and linq queries

        var chatEntryDetails = await
            (
                from chatEntry in dbChatEntry
                where chatEntry.ChatId == chat.Id
                join groupMembers in dbGroupMember on chatEntry.GroupMemberId equals groupMembers.Id
                join user in dbUser on groupMembers.UserId equals user.Id
                join countChatEntryUserStatus in dbChatEntryUserStatus.Where(e => e.DateReadByUser != null).Count() on chatEntry.Id equals countChatEntryUserStatus.ChatEntryId
                join chatEntryUserStatus in dbChatEntryUserStatus.Where(e => e.GroupMemberId == groupMemberId) on chatEntry.Id equals chatEntryUserStatus.ChatEntryId into ce
                from chatEntryUserStatus in ce.DefaultIfEmpty()
                orderby chatEntry.CreateDateTime descending
                select new ChatEntryDetails
                {
                    UserId = user.Id, GreetingName = user.GreetingName, GroupMemberId = chatEntry.GroupMemberId,
                    CreateDateTime = chatEntry.CreateDateTime, Details = chatEntry.Details, ChatType = ToApi(chatEntry.ChatType),
                    DateReadByUser = chatEntryUserStatus.DateReadByUser, ChatEntryId = chatEntry.Id, ReadByCount = countChatEntryUserStatus
                }
            )
            .PerPage(page, perPage)
            .ToListAsync();

When Executing this I got the following error

Could not find an implementation of the query pattern for source type 'IQueryable<<anonymous type: <anonymous type: <anonymous type: <anonymous type: ChatEntryDb chatEntry, GroupMemberDb groupMembers> <>h__TransparentIdentifier0, UserDb user> <>h__TransparentIdentifier1, IEnumerable<ChatEntryUserStatusDb> ce> <>h__TransparentIdentifier2, ChatEntryUserStatusDb chatEntryUserStatus>>'.  'Join' not found. [UserServerApiLib]

Everything works fine without this line

join countChatEntryUserStatus in dbChatEntryUserStatus.Where(e => e.DateReadByUser != null).Count() on chatEntry.Id equals countChatEntryUserStatus.ChatEntryId

Here I want the count chatEntryUserStatus where DateReadByUser is not null This line is supposed to get the count from chatEntryUserStatus table with few conditions there is one table chatEntry and they related as chatEntry.id = chatEntryUserStatus.ChatEntryId and for every chatEntry it has multiple records in chatEntryUserStatus so I want that count from chatEntryUserStatus for every chatEntry

If not count then If I can get all the chatEntryUserStatus records for every chatEntry

Thanks in advance

question from:https://stackoverflow.com/questions/66059251/count-in-linq-query-with-multiple-join-ef

Answer 1

Based on

Yes navigation properties are there

and looking at this Linq statement:

var chatEntryDetails = await
(
    from chatEntry in dbChatEntry
    where chatEntry.ChatId == chat.Id
    join groupMembers in dbGroupMember on chatEntry.GroupMemberId equals groupMembers.Id
    join user in dbUser on groupMembers.UserId equals user.Id
    join countChatEntryUserStatus in dbChatEntryUserStatus.Where(e => e.DateReadByUser != null).Count() on chatEntry.Id equals countChatEntryUserStatus.ChatEntryId
    join chatEntryUserStatus in dbChatEntryUserStatus.Where(e => e.GroupMemberId == groupMemberId) on chatEntry.Id equals chatEntryUserStatus.ChatEntryId into ce
    from chatEntryUserStatus in ce.DefaultIfEmpty()
    orderby chatEntry.CreateDateTime descending
    select new ChatEntryDetails
    {
        UserId = user.Id, 
        GreetingName = user.GreetingName, 
        GroupMemberId = chatEntry.GroupMemberId,
        CreateDateTime = chatEntry.CreateDateTime, 
        Details = chatEntry.Details, 
        ChatType = ToApi(chatEntry.ChatType),
        DateReadByUser = chatEntryUserStatus.DateReadByUser, 
        ChatEntryId = chatEntry.Id, 
        ReadByCount = countChatEntryUserStatus
    }
}
.PerPage(page, perPage)
.ToListAsync();

Then this looks like you may be able to leverage those navigation properties something like:

var chatEntryDetails = await context.ChatEntries
    .Where(c => c.Chat.Id == chatId)
    .Select(c => new ChatEntryDetails
    {
        UserId = c.GroupMember.User.Id,
        GreetingName = c.GroupMember.User.GreetingName,
        GroupMemberId = c.GroupMember.Id,
        CreateDateTime = c.CreateDateTime,
        Details = c.Details,
        ChatType = c.ChatType, 
        DateReadByUser = c.ChatEntryUserStatuses
            .Max(s => s.CreateDateTime),
        ChatEntryId = c.Id,
        ReadByCount = c.ChatEntryUserStatuses.Count()
   }).Skip(pageCount * pageSize)
   .Take(pageSize)
   .ToListAsync();

Which is considerably simpler than sifting through joins. The two deviations that you may need to look at are the ToApi() modifier that was on the ChatType, and the PerPage() method which seems to provide pagination. Anything in the Select needs to ultimately boil down to SQL through EF, so there's a good chance that even without the original "count" join issue, that ToApi() would have triggered an exception. (Earlier EF Core versions would have pulled the query client-side when detecting something like that. It saves you from an exception but usually kills the performance and memory footprint for the query) If I had to guess I'd say this is translating a string or numeric value into another value suited to the consumer of this data. If that is the case where you want to translate values such as 1,2,3,4 to "A","B","C","D" then this can be a bit tricky. Inside the LINQ expression you can use (? :) I.e:

ChatType = c.ChatType == 1 ? "A" : c.ChatType == 2 ? "B" : c.ChatType == 3 ? "C" : c.ChatType == 4 ? "D" : "?"

which is rather messy. Instead, I would typically recommend populating ChatType as-is in the ViewModel (ChatEntryDetails) and add a translated ApiChatType property which does a switch() statement across ChatType to return the appropriate translation.

For pagination use the stock-standard Linq pagination methods of Skip and Take to ensure these are translated to the SQL query as well. We can get the latest CreateDateTime from the user statuses using Max, though an OrderByDescending + FirstOrDefault + Select can work here as well. readByCount is provided by just asking for the Count of the related statuses.