Entity Framework include Extension Returns a Ton o

2019-07-21 15:58发布

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

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       john@email.com      1
1       johndoe       john@email.com      2
1       johndoe       john@email.com      3
1       johndoe       john@email.com      4
1       johndoe       john@email.com      5
1       johndoe       john@email.com      6
1       johndoe       john@email.com      7

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?

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

Thanks

Nick

3条回答
闹够了就滚
2楼-- · 2019-07-21 16:33

Just wondering if you could do a query that selects a new object with the permisisions as a list.

this is all COMPLETELY psuedo code/untested.not compiled (so modify as you need if you try it ;) )

var userinfo = from u in dbContext.Users
    Where(u => u.Username.Equals(username))
    Select new { User = u, Permissions = u.UserPermissions.ToList() };

second note this is not tested or even writen in an editor to test if it compiles. Just a quick shot from the hip.

an idea to consider?

查看更多
地球回转人心会变
3楼-- · 2019-07-21 16:43

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.

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;
查看更多
闹够了就滚
4楼-- · 2019-07-21 16:43

I asked similar question. There are some suggestions how to limitate duplication. But I guess it would be difficult to make Entity Framework generate those queries.

查看更多
登录 后发表回答