实体框架包括扩展返回一吨数据 [英] Entity Framework include Extension Returns a Ton of Data

问题描述

我有两个实体，User和UserPermission。 User实体包含所有常规字段，Id，用户名，电子邮件等，UserPermission实体有两个值UserId和PermissionId。我编写了一个最初使用Include扩展的存储库方法GetUserWithPermissions并做了类似的事情：

I have two entities, User and UserPermission. The User entity contains all your normal fields, Id, Username, Email, etc and the UserPermission entity has two values, UserId and PermissionId. I have written a repository method GetUserWithPermissions that originally utilized the Include extension and did something like this:

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

它运作良好，但问题是会有一堆与任何关联的UserPermission实体给定用户并使用Include扩展基本上只是将两个表展平为一个，因此对于与User关联的每个UserPermission，都会重复所有用户字段。返回的数据如下所示：

It works great but the issues is that there are going to be a bunch of UserPermission entities associated with any given user and using the Include extension essentially just flattens the two tables into one so ALL of the user fields are repeated for every single UserPermission associated with a User. The returned data looks something like this:

Id      Username      Email      ...      PermissionId
1       johndoe       [e-mail]      1
1       johndoe       [e-mail]      2
1       johndoe       [e-mail]      3
1       johndoe       [e-mail]      4
1       johndoe       [e-mail]      5
1       johndoe       [e-mail]      6
1       johndoe       [e-mail]      7

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

The only difference between each row is the last column PermissionId. If we have 50 permissions defined for the user, that is a large chunk of repeated data being returned when I do not think it is necessary. Obviously my other option is to do something like this:

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

上面的代码完成同样的事情，返回的数据大大减少，但两次旅行的权衡正在对数据库进行。

The above code accomplishes the same thing with drastically less data being returned but with the trade off that two trips are being made to the database.

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

Which method is better? Returning a lot of repeated data or making two trips to the database?

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

Here is the SQL query that is generated by the Entity Framework

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

谢谢

尼克

推荐答案

这是怎么样呢KS。 包含的集合确实导致父实体的列重复（请参阅此处以获取极好的示例和解释：我可以在EntityFramework中的ObjectSet上使用多少包含以保持性能？）

It's the way how it works. Include of collections leads indeed to duplication of the columns of the parent entity (see here for great example and explanation: How many Include I can use on ObjectSet in EntityFramework to retain performance?)

你有一个没有一般规则的权衡，哪种方式更好：一次往返包括但是没有重复数据的重复数据或两次往返。什么是更好/更高效？如果你想要一个确切的答案，我认为你必须逐个衡量。

And you have a trade-off without a general rule which way is better: One roundtrip with Include but duplicated data or two roundtrips without duplicated data. What is better/more performant? I think you have to measure it case by case if you want an exact answer.

我可以想象，根据经验，我们可以说：如果父母有很多列和子集合只有很少，子集合可能很长，那么这是一个候选人更喜欢两次往返以避免数据重复。

I could imagine that as a rule of thumb we could say: If the parent has many columns and the child collection only a few and the child collection can possibly be very long, then this is a candidate to prefer two roundtrips to avoid the data duclication.

如果你不想用 Include 急切加载，你可以依赖延迟加载，也可以使用explicite loading： / p>

If you don't want eager loading with Include you can either rely on lazy loading or you can use explicite loading:

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

这篇关于实体框架包括扩展返回一吨数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！