EF Core 3.1-Sql服务器IN运算符转换(不带AsEnumerable()/ToList())

Question

我刚刚完成了从EF Core 2.2到EF Core 3.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)
...

这里的userIdList是一个需要与数据库中的数据进行比较的列表。EF Core 3不再翻译此功能。

一种解决方案可能是使用AsEnumerable((或ToList((，但在我的情况下，这不是一个好的解决方案，因为我有大量的数据，不可能在客户端获得所有数据。

编辑

这是完整的查询：

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

我建议将上述查询转换为SQL存储过程。

有一个名为LinqPad的好工具可以用来简化转换。

p.S.在上面的代码中，您可以尝试使用!Any(...)方法而不是Count(...) == 0。