时间戳

首页 > 解决方案 > EF Core 过滤包含与选择

entity-framework - EF Core 过滤包含与选择

问题描述

对不起我的英语不好。

当同时使用过滤包含和选择时，我遇到了 EF Core 5.0.9 的错误，我不知道是错误还是功能。:)

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

在这个时候，第一个元素不是从过滤的包含中选择的，只是从正常的包含中选择的。来自 Profiler 的 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();

知道为什么会这样，我该如何解决？

谢谢

标签： entity-frameworkasp.net-core.net-coreentity-framework-coreasp.net-core-3.1

解决方案

Include如果您有自定义投影Select，则将被完全忽略，因此您的过滤器也将被忽略。这不是错误，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();

推荐阅读