Entity Framework always includes data that is in c

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.

Answer 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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

First: Turn Lazy Loading on.

Second: If you want to filter down what you retrieve and return, then do a custom return object or something.

from u in db.Users
join r in db.Roles
  on u.RoleID equals r.RoleID
select new { u.UserID, u.Title, u.Email, r.RoleName }

Or something like that. You will have a minimal return object and your object graph will be tiny.