实体框架包括扩展返回大量数据

我有两个实体：User 和 UserPermission。 User 实体包含所有常规字段，Id、Username、Email 等，UserPermission 实体有两个值：UserId 和 PermissionId。我编写了一个存储库方法 GetUserWithPermissions ，该方法最初使用 Include 扩展并执行如下操作：

return dbContext.Users.Include(u => u.UserPermission).Where(u => u.Username.Equals(username)).FirstOrDefault();

它工作得很好，但问题是，将会有一堆与任何给定用户关联的 UserPermission 实体，并且使用 Include 扩展本质上只是将两个表合并为一个表，因此对于与关联的每个 UserPermission 都会重复所有用户字段一个用户。返回的数据看起来像这样：

Id      Username      Email      ...      PermissionId
1       johndoe       [email protected] /cdn-cgi/l/email-protection      1
1       johndoe       [email protected] /cdn-cgi/l/email-protection      2
1       johndoe       [email protected] /cdn-cgi/l/email-protection      3
1       johndoe       [email protected] /cdn-cgi/l/email-protection      4
1       johndoe       [email protected] /cdn-cgi/l/email-protection      5
1       johndoe       jo[email protected] /cdn-cgi/l/email-protection      6
1       johndoe       [email protected] /cdn-cgi/l/email-protection      7

每行之间的唯一区别是最后一列 PermissionId。如果我们为用户定义了 50 个权限，那么当我认为没有必要时，就会返回大量重复数据。显然我的另一个选择是做这样的事情：

User user = dbContext.Users.Where(u => u.Username.Equals(username)).FirstOrDefault();
if (user != null)
    user.UserPermissions.ToList();
return user;

上面的代码完成了同样的事情，返回的数据大大减少，但需要对数据库进行两次访问。

哪种方法更好？返回大量重复数据或两次访问数据库？

这是实体框架生成的 SQL 查询

SELECT 
[Project2].[Id] AS [Id], 
[Project2].[Username] AS [Username], 
[Project2].[LoweredUsername] AS [LoweredUsername], 
[Project2].[CompanyId] AS [CompanyId], 
[Project2].[FirstName] AS [FirstName], 
[Project2].[LastName] AS [LastName], 
[Project2].[Email] AS [Email], 
[Project2].[C1] AS [C1], 
[Project2].[UserId] AS [UserId], 
[Project2].[PermissionValue] AS [PermissionValue]
FROM ( SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Username] AS [Username], 
    [Limit1].[LoweredUsername] AS [LoweredUsername],    
    [Limit1].[CompanyId] AS [CompanyId], 
    [Limit1].[FirstName] AS [FirstName], 
    [Limit1].[LastName] AS [LastName], 
    [Limit1].[Email] AS [Email], 
    [Extent2].[UserId] AS [UserId], 
    [Extent2].[PermissionValue] AS [PermissionValue], 
    CASE WHEN ([Extent2].[PermissionValue] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Username] AS [Username], 
        [Extent1].[LoweredUsername] AS [LoweredUsername],       
        [Extent1].[CompanyId] AS [CompanyId], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[Email] AS [Email]
        FROM [dbo].[Users] AS [Extent1]
        WHERE [Extent1].[LoweredUsername] = (LOWER(LTRIM(RTRIM(@p__linq__0)))) ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[UserPermissions] AS [Extent2] ON [Limit1].[Id] = [Extent2].[UserId]
)  AS [Project2]
ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC

Thanks

Nick

这就是它的运作方式。Include集合确实会导致父实体的列重复（请参阅此处以获得很好的示例和解释：我可以在 EntityFramework 中的 ObjectSet 上使用多少个 Include 以保持性能？ https://stackoverflow.com/questions/5521749/how-many-include-i-can-use-on-objectset-in-entityframework-to-retain-performance/5522195#5522195)

您需要进行权衡，但没有一般规则哪种方式更好：一次往返Include但有重复数据或两次往返没有重复数据。什么更好/性能更好？我认为如果你想要一个准确的答案，你必须具体情况具体分析。

我可以想象，根据经验，我们可以说：如果父母有many列和子集合只有一个few并且子集合可能非常长，那么最好选择两次往返以避免数据重复。

如果你不想急切加载Include您可以依赖延迟加载，也可以使用显式加载：

User user = dbContext.Users.Where(u => u.Username.Equals(username))
    .FirstOrDefault();
if (user != null)
    dbContext.Entry(user).Collection(u => u.UserPermissions).Load();
return user;