c# - 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?

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