Entity Framework include Extension Returns a Ton of Data

Question

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

Looks like I misunderstood your question - I take it this is about optimization, the results for both of your approaches are the same. — BrokenGlass, Jul 27 '11 at 17:57
@BrokenGlass So you are saying returning a bunch of duplicated data in one database call is the same as returning significantly less data with two database calls? — Nick Olsen, Jul 27 '11 at 18:00
No - the resulting `User` entities are the same, performance might differ. — BrokenGlass, Jul 27 '11 at 18:20

score 4 · Accepted Answer · edited May 23 '17 at 10:34

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;

Your third paragraph is example what I was thinking and this situation fits what you stated, a parent with many columns and a large collection of child entities. Thanks for the tip. — Nick Olsen, Jul 27 '11 at 18:26

score 0 · Answer 2 · answered Jul 27 '11 at 18:11

0

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?

answered Jul 27 '11 at 18:11

MikeScott8

720
2
10
17

Interesting, but: I believe it will lead to the same duplication of data as `Include` because the database only sends one result set back and this has always a square or rectangle shape of columns and rows. EF "dispatches" this data matrix into entity properties, thereby resolving the data duplication. It happens on the client, not on the database server. (BTW: You need to remove `ToList()`. It doesn't work in a projected collection.) – Slauma Jul 27 '11 at 18:19
as I said it was a shot in the dark. :) – MikeScott8 Aug 05 '11 at 20:31

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

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.

edited May 23 '17 at 11:55

Community

1
1

answered Aug 07 '11 at 19:50

SiberianGuy

24,674
56
152
266

Entity Framework include Extension Returns a Ton of Data

3 Answers3

Linked