c# - 您可以在 Entity Framework 导航属性上使用投影吗？

Question

这是我无法回答的问题。我目前有以下简单的代码片段。

return _context.UserProfiles
                .Single(p => p.ID == userID)
                    .Organisations
                    .Select(p => new { ID = p.ID })
                    .First()
                    .ID;

这只是获取第一个用户配置文件(作为 SELECT * FROM UserProfiles WHERE... 提取)，这很好。问题是组织导航属性。

我在迁移中使用代码优先，因此在 UesrProfiles 和组织(多对多)之间有一个由 EF 自动创建的联结表。我最终得到了 Organizations 的用户配置文件导航属性和 Members 的组织导航属性。所以，简单。

我对组织导航属性的问题是它没有投影。在选择上，我首先将以下 SQL 发送到数据库服务器。

SELECT 
    [Extent2].[ID] AS [ID], 
    [Extent2].[Description] AS [Description], 
    [Extent2].[ShortDescription] AS [ShortDescription], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[Address] AS [Address], 
    [Extent2].[City] AS [City], 
    [Extent2].[State] AS [State], 
    [Extent2].[PostCode] AS [PostCode], 
    [Extent2].[Country] AS [Country], 
    [Extent2].[Created] AS [Created], 
    [Extent2].[CreatedBy] AS [CreatedBy]
    FROM  [dbo].[OrganisationMembers] AS [Extent1]
    INNER JOIN [dbo].[Organisations] AS [Extent2] ON [Extent1].[Organisation_ID] = [Extent2].[ID]
    WHERE [Extent1].[UserProfile_ID] = @EntityKeyValue1

因为我只转换 ID，所以我不应该得到以下信息吗？

SELECT 
    [Extent2].[ID] AS [ID]
    FROM  [dbo].[OrganisationMembers] AS [Extent1]
    INNER JOIN [dbo].[Organisations] AS [Extent2] ON [Extent1].[Organisation_ID] = [Extent2].[ID]
    WHERE [Extent1].[UserProfile_ID] = @EntityKeyValue1

如我所料，加入是正确的，但列不是。我想我可以为联结表创建另一个对象，然后编写代码手动加入和选择，但这是我希望 EF 会为我做的事情。

更新

我只是不想玩弄从 First() 返回的原始用户配置文件的排序和预测。如果我将代码更改为:

return _context.UserProfiles
   .Select(p => new { ID = p.ID, Organisations = p.Organisations.Select(q => new { ID = q.ID }) })
   .Single(p => p.ID == userID)
   .Organisations
   .First()
   .ID;

我最终遇到了这个怪物:

SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[C1] AS [C1], 
    [Project1].[Organisation_ID] AS [Organisation_ID]
    FROM ( SELECT 
        [Limit1].[ID] AS [ID], 
        [Extent2].[Organisation_ID] AS [Organisation_ID], 
        CASE WHEN ([Extent2].[Organisation_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (2) [Extent1].[ID] AS [ID]
            FROM [dbo].[UserProfiles] AS [Extent1]
            WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]
        LEFT OUTER JOIN [dbo].[OrganisationMembers] AS [Extent2] ON [Limit1].[ID] = [Extent2].[UserProfile_ID]
    )  AS [Project1]
    ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC

但是，我猜它是按照我的意愿转换我的 ID。

更新 2

根据 Gongdo 的回答，我更新了以下内容:

    return _context.UserProfiles
                    .Where(p => p.ID == userID)
                    .Take(1)
                    .SelectMany(p => p.Organisations)
                    .Select(p => new { ID = p.ID })
                    .First().ID;

哪些输出:

SELECT 
    [Limit2].[Organisation_ID] AS [Organisation_ID]
    FROM ( SELECT TOP (1) 
        [Extent2].[Organisation_ID] AS [Organisation_ID]
        FROM   (SELECT TOP (1) [Extent1].[ID] AS [ID]
            FROM [dbo].[UserProfiles] AS [Extent1]
            WHERE [Extent1].[ID] = @p__linq__0 ) AS [Limit1]
        INNER JOIN [dbo].[OrganisationMembers] AS [Extent2] ON [Limit1].[ID] = [Extent2].[UserProfile_ID]
    )  AS [Limit2]

我离我想要的输出更近了。谢谢。

Answer 1

Single、SingleOrDefault、First 和 FirstOrDefault 本身就是投影方法。所以你需要保持 IQueryable 直到你真正需要投影它。按照您的意愿进行操作。

return _context.UserProfiles
                .Where(p => p.ID == userID)
                .SelectMany(p => p.Organisations)
                .Select(p => new { ID = p.ID })
                .First()
                .ID;