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?

What kind of `Include` are you using? As far as I know the `Include` method of `System.Data.Objects.ObjectQuery` receives only 1 argument of string? — King King, Oct 15 '13 at 16:45
@KingKing I have done a extension method, that format the lambda expression into string and pass that to "native" ObjectQuery include method. — gds03, Oct 15 '13 at 16:47
If so, the answer to your problem is `impossible`, `Include` is not such powerful, you can't perform any `filter` before including. — King King, Oct 15 '13 at 16:49

ChaseMedallion · Accepted Answer · 2013-10-16T10:30:23.647

16

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.

edited Oct 16 '13 at 10:30

answered Oct 15 '13 at 16:45

ChaseMedallion

20,860
17
88
152

I need to return the User entity with all things related, but not all Collection mapped.. So I think anonymous type is not right :/ – gds03 Oct 15 '13 at 16:49
@goncaloRD: Unfortunately `where` conditions are not possible using `Include`'s. This is your best option. – Ocelot20 Oct 15 '13 at 19:00
@ChaseMedallion thanks, This is exactly what I need. The query is done perfectly and EF objects are mapped as the way I want. I created a Type to have my objects typified, to be used in my Business Layer. – gds03 Oct 16 '13 at 11:03

score 1 · Answer 2 · edited Feb 05 '19 at 22:17

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:

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

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

LINQ To Entities Include + Where Method

2 Answers2

Linked