LINQ To Entities Include + Where Method

.net c# entity-framework include linq

Question

Consider the NxN table I have:

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

The FK to user and to address is contained in UserAddresses. According to what I understand, the Entity Framework User's generated Entity has a collection of UserAddresses. A unique UserAddress holds a reference to both a User and an Address, and the Address comprises a collection of UserAddresses.

I now wish to create the following query using Linq. Get only the userAddresses with the enabled flag set to true for a certain user id. UserAddresses can have more than one entry for a given user id, but only one is set for this particular user.

What I can do is ask:

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

However, I don't actually want to load every UserAddress for that person. Only the one with enabled and set to TRUE!

Can somebody assist me with this query?

1
15
10/15/2013 4:40:09 PM

Accepted Answer

The association property cannot be partially loaded in EF. To take only the information you require, try selecting an anonymous type:

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

The result won't load. nonetheless, User.UserAddresses You can find just what you need at addresses.

You would need to detach result if you actually wanted to return everything as a component of the User class. User, followed by result update. The result is pointed to by User.UserAddresses. Addresses.

16
10/16/2013 10:30:23 AM

Popular Answer

Utilizing another alternative isLoad() in place ofInclude() :

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

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

Do not forget thatLoad() procedure might be disregarded by EF in some circumstances:

  1. When you fetch an object using EF and the Lazy Loading feature, all associated collections that have been designated as Virtual in your class are also brought. then by doingcontext.User.Single( x => x.id == USER_ID ); Unless you disable lazy loading for your collection by deleting the, you'll receive all UserAddresses related to the User.Virtual keyword taken from a User class attribute.

  2. If you call context while adding or removing items from the UserAddresses collection in your program. SaveChanges(); without erasing your context, the UserAddresses collection will be loaded from the EF context cache rather than the database the next time you load a User object (your latest changes). In this scenario, you must dispose of your current context and create a new one before obtaining the user from context. For instance, if your User has five items in your UserAddresses collection and you disable one of them (item.Enabled = false and then dialcontext.SaveChanges() When you retrieve a User object from the same context again later on, it already contains five items in its collection that came from context cache, thus it ignores your context without disposing of it.Load() method.

PS:

If all of the following criteria were met, the lazy loading functionality would be enabled:

  1. context.Configuration. If true, LazyLoadingEnabled;
  2. context.Configuration. ProxyCreationEnabled is set to true.
  3. In your User class, UserAddresses has been specified as Virtual.


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow