EF Core Filtered Include with Select

Question

不好意思。

我遇到了一个错误与EF Core 5.0.9时，使用过滤包括和选择在同一时间，我不知道是一个bug或功能。:)

return await _dbContext.User
.Where(u => !u.TOROLT)
.Where(u => ids.Contains(u.Id))
.Include(u => u.EventUsers.Where(eu => !eu.TOROLT && eu.EventId == eventId))
.Select(u => new UserDropDownDtoWithInviteData
{
Id = u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
EventUserId = u.EventUsers.First().Id,
IsCelebrated = u.EventUsers.First().IsCelebrated,
IsEventAdmin = u.EventUsers.First().IsEventAdmin,
IsInviteAccepted = u.EventUsers.First().IsInviteAccepted,
IsInvited = u.EventUsers.First().IsInvited,
})
.ToListAsync();

在这个例子中，第一个元素不是从经过过滤的include中选择的，而是从普通的include中选择的。SQL脚本从分析器:

SELECT
[u].[Id],
[u].[FirstName],
[u].[LastName],
(
SELECT TOP(1) [e].[Id]
FROM [dbo].[EventUser] AS [e]
WHERE [u].[Id] = [e].[UserId]) AS [EventUserId],
(
SELECT TOP(1) [e0].[IsCelebrated]
FROM [dbo].[EventUser] AS [e0]
WHERE [u].[Id] = [e0].[UserId]) AS [IsCelebrated],
(
SELECT TOP(1) [e1].[IsEventAdmin]
FROM [dbo].[EventUser] AS [e1]
WHERE [u].[Id] = [e1].[UserId]) AS [IsEventAdmin],
(
SELECT TOP(1) [e2].[IsInviteAccepted]
FROM [dbo].[EventUser] AS [e2]
WHERE [u].[Id] = [e2].[UserId]) AS [IsInviteAccepted],
(
SELECT TOP(1) [e3].[IsInvited]
FROM [dbo].[EventUser] AS [e3]
WHERE [u].[Id] = [e3].[UserId]) AS [IsInvited]
FROM [dbo].[User] AS [u]
WHERE ([u].[TOROLT] <> CAST(1 AS bit))
AND [u].[Id] IN (2, 1, 3, 4, 5)

但是如果是单独过滤包括和选择，那么它的工作很好，但这选择完整的记录，而不是他从数据库的一部分:

var a = await _dbContext.User
.Where(u => !u.TOROLT)
.Where(u => ids.Contains(u.Id))
.Include(u => u.EventUsers.Where(eu => !eu.TOROLT && eu.EventId == eventId))
.ToListAsync();
return a.Select(u => new UserDropDownDtoWithInviteData
{
Id = u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
EventUserId = u.EventUsers.First().Id,
IsCelebrated = u.EventUsers.First().IsCelebrated,
IsEventAdmin = u.EventUsers.First().IsEventAdmin,
IsInviteAccepted = u.EventUsers.First().IsInviteAccepted,
IsInvited = u.EventUsers.First().IsInvited,
})
.ToList();

知道为什么会这样吗?我怎么解决这个问题?

THX

Answer 1

Include被完全忽略，如果您有自定义投影Select，那么您的过滤器也将被忽略。这不是一个bug,Include只有当你从查询中得到整个实体时才能工作。

无论如何，考虑重写你的查询:

var query = 
from u in _dbContext.User
where !u.TOROLT && ids.Contains(u.Id)
from eu in u.EventUsers.Where(eu => !eu.TOROLT && eu.EventId == eventId)
.Take(1)
.DefaultIfEmpty()
select new UserDropDownDtoWithInviteData
{
Id = u.Id,
FirstName = u.FirstName,
LastName = u.LastName,
EventUserId = eu.Id,
IsCelebrated = eu.IsCelebrated,
IsEventAdmin = eu.IsEventAdmin,
IsInviteAccepted = eu.IsInviteAccepted,
IsInvited = eu.IsInvited,
};
var result = await query.ToListAsync();