EF Core 3.1 - Sql server IN operator translation (without AsEnumerable()/ToList())

Question

I have just done the upgrade from EF Core 2.2 to EF Core 3.1. I have some queries like this:

...
userIdList.All(u => _context.ConversationUsers.Count(x => x.ContextOwnerCode == t.ContextOwnerCode && x.CompanyCode == u.CompanyCode && x.ConversationId == t.Id && x.UserId == u.ElementId &&
                            (x.GroupId == null || x.GroupId == 0) && (x.Deleted == null || !(bool)x.Deleted)) > 0) &&
                    _context.ConversationUsers.Count(x => x.ConversationId == t.Id && (x.GroupId == null || x.GroupId == 0) &&
                        (x.Deleted == null || !(bool)x.Deleted) && userIdList.Count(u => u.CompanyCode == x.CompanyCode && u.ElementId == x.UserId) == 0) == 0)
...

here the userIdList is a list that needs to be compared with data in the database. With EF Core 3 this function is no more translated.

One solution could be to use AsEnumerable() or ToList() but in my case is not a good solution since I have a huge amount of data and it is not possible to get all the data on the client side.

EDIT

This is the full query:

var result = _context.Conversations.Where(t => t.ContextOwnerCode == contextOwnerCode &&
                ((docYear == 0 && docCode == 0) ||
                    (docYear != 0 && docCode != 0 && t.DocumentYear != null && t.DocumentCode != null && t.DocumentYear == docYear && t.DocumentCode == docCode)) &&
                (
                (senderGroup != null && senderGroup.CompanyCode > 0 && senderGroup.ElementId > 0 && recipientGroup != null && recipientGroup.CompanyCode > 0 && recipientGroup.ElementId > 0 &&
                    (userIdList == null || userListCount == 0) &&
                    t.CreatedChatGroupId != null && t.ChatGroupId != null && t.CreatedChatGroupId > 0 && t.ChatGroupId > 0 &&
                    ((senderGroup.CompanyCode == t.CreatedCompanyCode && senderGroup.ElementId == t.CreatedChatGroupId &&
                    recipientGroup.CompanyCode == t.ChatGroupCompanyCode && recipientGroup.ElementId == t.ChatGroupId) ||
                    (senderGroup.CompanyCode == t.ChatGroupCompanyCode && senderGroup.ElementId == t.ChatGroupId &&
                    recipientGroup.CompanyCode == t.CreatedCompanyCode && recipientGroup.ElementId == t.CreatedChatGroupId))) ||
                (((senderGroup != null && senderGroup.CompanyCode > 0 && senderGroup.ElementId > 0 &&
                        (recipientGroup == null || (recipientGroup.CompanyCode == 0 && recipientGroup.ElementId == 0))) ||
                    ((senderGroup == null || (senderGroup.CompanyCode == 0 && senderGroup.ElementId == 0)) &&
                        recipientGroup != null && recipientGroup.CompanyCode > 0 && recipientGroup.ElementId > 0)) &&
                    userIdList != null && userListCount > 0 &&
                    ((senderGroup != null && t.CreatedChatGroupId != null && t.ChatGroupCompanyCode == null && t.ChatGroupId == null &&
                            senderGroup.CompanyCode == t.CreatedCompanyCode && senderGroup.ElementId == t.CreatedChatGroupId) ||
                        (recipientGroup != null && t.CreatedChatGroupId == null && t.ChatGroupCompanyCode != null && t.ChatGroupId != null &&
                            recipientGroup.CompanyCode == t.ChatGroupCompanyCode && recipientGroup.ElementId == t.ChatGroupId)) &&
                    userIdList.All(u =>
                        _context.ConversationUsers.Count(x => x.ContextOwnerCode == t.ContextOwnerCode && x.CompanyCode == u.CompanyCode && x.ConversationId == t.Id && x.UserId == u.ElementId &&
                            (x.GroupId == null || x.GroupId == 0) && (x.Deleted == null || !(bool)x.Deleted)) > 0) &&
                    _context.ConversationUsers.Count(x => x.ConversationId == t.Id && (x.GroupId == null || x.GroupId == 0) &&
                        (x.Deleted == null || !(bool)x.Deleted) && userIdList.Count(u => u.CompanyCode == x.CompanyCode && u.ElementId == x.UserId) == 0) == 0) ||
                ((senderGroup == null || (senderGroup.CompanyCode == 0 && senderGroup.ElementId == 0)) &&
                    recipientGroup != null && recipientGroup.CompanyCode > 0 && recipientGroup.ElementId > 0 &&
                    (userIdList == null || userListCount == 0) &&
                    t.CreatedChatGroupId == null && t.ChatGroupCompanyCode != null && t.ChatGroupId != null &&
                    recipientGroup.CompanyCode == t.ChatGroupCompanyCode && recipientGroup.ElementId == t.ChatGroupId &&
                    _context.ConversationUsers.Count(x => x.ConversationId == t.Id && (x.GroupId == null || x.GroupId == 0) && (x.Deleted == null || !(bool)x.Deleted)) == 0) ||
                ((senderGroup == null || (senderGroup.CompanyCode == 0 && senderGroup.ElementId == 0)) &&
                    (recipientGroup == null || (recipientGroup.CompanyCode == 0 && recipientGroup.ElementId == 0)) &&
                    userIdList != null && userListCount > 1 &&
                    userIdList.All(u =>
                        _context.ConversationUsers.Count(x => x.ContextOwnerCode == t.ContextOwnerCode && x.CompanyCode == u.CompanyCode && x.ConversationId == t.Id && x.UserId == u.ElementId &&
                            (x.GroupId == null || x.GroupId == 0) && (x.Deleted == null || !(bool)x.Deleted)) > 0) &&
                    _context.ConversationUsers.Count(x => x.ConversationId == t.Id && (x.GroupId == null || x.GroupId == 0) &&
                        (x.Deleted == null || !(bool)x.Deleted) && userIdList.Count(u => u.CompanyCode == x.CompanyCode && u.ElementId == x.UserId) == 0) == 0)
                )
                ).FirstOrDefault();

Answer 1

My recommendation here is to convert the above query to SQL Store Procedure.

There is a good tool called LinqPad you can use to make the conversion easier.

P.S. in the above code you can try to use !Any(...) method instead of Count(...) == 0.