Entity Framework always includes data that is in context even if I don't ask for it

Question

I am using MVC.NET web api, EF with DB first, and I have lazy loading turned off on my context. EF is returning way too much data, even with LazyLoading turned off.

For example, I have Users with one Role. When I query for Users and Include Role, the Role.Users property is automatically filled with data since Users have been loaded into the context.

Why can't I get EF to give me JUST what I request? Or am I missing something big here?

public partial class User
{
    public int UserID { get; set; }
    public string Title { get; set; }
    public string Email { get; set; }
    public int RoleID { get; set; }

    ....

    public virtual Role Role { get; set; }
} 

public partial class Role
{
    public int RoleID { get; set; }
    public string RoleName { get; set; }

    ....

    public virtual ICollection<User> Users { get; set; }
} 




return db.Users.Include(u => u.Role);
// ^^ user.Role.Users is filled with 1000s of users

TL;DR - I want EF to never load data into navigation properties/collections unless I .Include() it directly. When serializing to JSON I want just what I ask for explicitly. It seems that even with lazy loading off, navigation properties that are already in the context (ie usually "circular references") will be loaded and returned.

Solution 1

The behaviour your are seeing is called Relationship Fixup and you cannot disable it.

If you are loading users with roles to serialize them and sent them to somewhere I guess that you don't want to track changes of entities in the context they have been loaded in. So, there is no need to attach them to the context and you can use:

return db.Users.Include(u => u.Role).AsNoTracking();

Or use a projection into an object specialized for serialization, as suggested by @STLRick.

Solution 2

You are right that with lazy loading on, you will get back navigation properties because they are "touched" by the serializer which causes them to be loaded. Lazy loading should be off if you want the properties to come back as null. That said, it "seems" that once entities are loaded into the context (through other queries, for example), they will be processed by the serializer. So the answer is to tell the serializer not to return the navigation properties. The best way I've been able to find to do this is to use DTOs (Data Transfer Objects). This allows you to return exactly the data you want rather than your actual entities.

Your DTO might look something like this:

public partial class UserDto
{
    public UserDto(user User)
    {
        UserID = user.UserID;
        Title = user.Title;
        //... and so on
    }
    public int UserID { get; set; }
    public string Title { get; set; }
    public string Email { get; set; }
    public int RoleID { get; set; }

    //exclude the Role navigation property from your DTO
}

...and then you could do something like this:

return db.Users.Include(u => u.Role).Select(user => new UserDto(user));

Solution 3

You can select only what you need by using Select().

var users = _db.Users.Select(x => new
{
    UserID = x.UserID,
    Title = x.Title,
    Email = x.Email,
    RoleID = x.RoleID
}).AsEnumerable();

Solution 4

I don't want it to load anything besides what I tell it to include.

It looks like you need to use Explicit Loading. Basically, you could load the specific entities like this:

context.Include("Roles")

To my best knowledge that should not include related entities. Lazy loading should indeed be disabled and you could load navigational properties explicitly with Load.

View more solutions

Comments

[Yinda Yin]

with LazyLoading turned off -- erm, isn't that eager loading?

[Rick Petersen]

Just a small heads-up. Lazy-loading means it will only load what it needs when it needs it... turning that off is counter-intuitive for what you're looking for.

[Judo]

Can you post code - your model plus the data access code.

[chris1234p]

From what I understand and have tested - lazy loading means when you access a property on an entity that is a collection, it loads that collection. I have it off because I don't want it to load anything besides what I tell it to include. My issue is that even with this turned off, it still includes collections that have been loaded in the Context, so Users have one Role, and Roles have many users as a navigation property, and it is already loaded with 1000s of users even though I didn't tell EF to include it.

[Yahia]

@chris1234p no you told it to not be lazy... being "not lazy" means that it should load all at once thus not waiting till it might be needed (on first access of the collection)... which in turn is "eager loading" (opposite of "lazy loading").

[chris1234p]

Wow, so with lazy loading off, you are saying that if I select one User without .Including anything, it is going to load the entire graph ? What kind of loading do I need where I just load what I explicitly tell it?

[Felix]

No it's not like this, lazy loading will load data whenever you access it, when eager load will load it straight away when you create your select command. Anyway, you are getting these data just because you accessing it, if you would not touch these properties EF will not load them from DB. You should play around with SQL profiler to take a look when and which queries are executed.

[Tyler]

I have the same problem as @chris1234p. In my case, I'm trying to load a list of comments, and each comment has a parent comment. The parent comments are being populated (against my wishes), and it's as though EF has declared open season on that table as soon as I touch it for the first time. I do not experience the same problem with other relationships that are not yet "in scope", e.g. author.

[Rick Petersen]

If part of the requirement is wanting the objects to still be attached to the context and therefore updateable and such, then this is a better solution than mine below. Make sure you always include the things you need; if you do, I agree with Serge, this will probably get you closer to what you want.

[chris1234p]

Hello Serge, that is what I am doing, but it seems that in order for this to include only what I tell it to include, I need to use a brand new context. Otherwise if data is already in the context, it will be loaded in the data that is returned, even if I don't ask for it. It is like it is trying to "help" me by giving me stuff that I don't ask for.

[chris1234p]

For some reason when I tried AsNoTracking, I would get errors during serialization.

[Slauma]

@chris1234p: Maybe ask a new question and describe those errors. I was focussing on your question how you can avoid that all navigation properties between entities are populated.

[user1843640]

with lazy loading on you will get back navigation properties because they are touched by the serializer.