LINQ To Entities Include + Where Method

Question

I have NxN table, imagine:

User(id, ...) <- UserAddresses(id, userId, addressId, enabled, ...) -> Addresses(id, ...)

UserAddresses contains the FK to user and to address. For what I know, the Entity created by the Entity Framework User, contains a collection to UserAddresses. The Address contains a collection to UserAddresses, and a specific UserAddress contains one refenrece to User and to one Address.

Now I want to make the next query by linq. For a specific user id, get only the userAddresses with enabled flag setted to true. For a specific user id, userAddresses can contain multiple entries but only one is setted for this specific user.

The query I can do is:

context.User.Include( x => x.UserAddresses )
            .Include( x => x.UserAddresses.Select(y => y.Address) )
            .Single( x => x.id == USER_ID )

but what i really want is not to load all UserAddresses for that user... Only the one that contains enabled, setted to TRUE!

Somebody can help me to do this query?

Solution 1

There is no way in EF to partially load an association property. Try selecting into an anonymous type to take only what you need:

var result = context.User
   .Where(u => u.Id == userId)
   .Select(u => new {
       Addresses = u.UserAddresses.Select(ua => ua.Address)
            .Where(a => a.Enabled),
       User = u // if you need this as well 
   })
   .Single();

This won't load result.User.UserAddresses, but result.Addresses will have exactly what you want.

If you really want to return everything as part of the User class, you'd need to detach result.User and then update result.User.UserAddresses to point to result.Addresses.

Solution 2

Another alternative option is using Load() instead of Include():

var foundUser = context.User.Single(x => x.Id == USER_ID);

context.Entry(foundUser).Collection(u =>
u.UserAddresses).Query().Where(userAddress =>
userAddress.Enabled).Load();

Keep in mind that Load() method could be ignored by EF in some scenarios:

1. If you are using EF along with the Lazy Loading feature, fetching your object brings all associated collections which have been marked as Virtual in your class. So by doing context.User.Single( x => x.id == USER_ID ); you'll get all UserAddresses associated to the User unless you turn off Lazy Loading for your collection by removing the Virtual keyword from property in User class.

2. If you are adding/removing UserAddresses collection in your program and you call context.SaveChanges(); without disposing your context, next time when you load User object, UserAddresses collection will be loaded from EF context cache not from DB (your latest changes). In this case you need to Dispose your context and instantiate a new context before getting User from context. For example if you have your User with 5 items in your UserAddresses collection, and you make one of the items disabled(item.Enabled = false) and then call context.SaveChanges() without disposing your context, next time when you get User object from same context, it already has 5 items in its collection which comes from context cache and it ignores your Load() method.

PS:

Lazy Loading feature is ON if all the conditions below applied:

1. context.Configuration.LazyLoadingEnabled = true;
2. context.Configuration.ProxyCreationEnabled = true;
3. UserAddresses has been defined Virtual in your User class.