C# LINQ To Entity Include + Where 方法

LINQ To Entities Include + Where Method

提问by gds03

I have NxN table, imagine:

我有 NxN 表，想象一下：

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

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.

UserAddresses 包含用户和地址的 FK。据我所知，由实体框架用户创建的实体包含一个到 UserAddresses 的集合。Address 包含一个 UserAddresses 的集合，一个特定的 UserAddress 包含一个对 User 和一个 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.

现在我想通过 linq 进行下一个查询。对于特定的用户 ID，仅获取启用标志设置为 true 的 userAddresses。对于特定的用户 ID，userAddresses 可以包含多个条目，但仅为该特定用户设置一个。

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!

但我真正想要的是不加载该用户的所有用户地址...只有一个包含启用，设置为 TRUE！

Somebody can help me to do this query?

有人可以帮我做这个查询吗？

采纳答案by ChaseMedallion

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

EF 无法部分加载关联属性。尝试选择匿名类型以仅获取您需要的内容：

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.

这不会加载 result.User.UserAddresses，但 result.Addresses 将拥有您想要的内容。

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.

如果您真的想将所有内容作为 User 类的一部分返回，则需要分离 result.User，然后更新 result.User.UserAddresses 以指向 result.Addresses。

回答by Amir Chatrbahr

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

另一种替代选择是使用Load()代替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:

请记住，Load()在某些情况下，EF 可能会忽略该方法：

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

1. 如果您将 EF 与延迟加载功能一起使用，则获取您的对象会带来所有在您的类中标记为 Virtual 的关联集合。因此，通过这样做，context.User.Single( x => x.id == USER_ID );您将获得与用户关联的所有用户地址，除非您通过Virtual从 User 类中的属性中删除关键字来关闭集合的延迟加载。

2. 如果您在程序中添加/删除 UserAddresses 集合并调用 context.SaveChanges(); 在不处理上下文的情况下，下次加载 User 对象时，将从 EF 上下文缓存加载 UserAddresses 集合，而不是从 DB（您的最新更改）加载。在这种情况下，您需要在从上下文中获取用户之前处理您的上下文并实例化一个新的上下文。例如，如果您的 User 在您的 UserAddresses 集合中有 5 个项目，并且您将其中一个项目设置为 disabled( item.Enabled = false) 然后在context.SaveChanges()不处理您的上下文的情况下调用，则下次当您从同一上下文中获取 User 对象时，它已经有 5 个项目它的集合来自上下文缓存，它会忽略您的Load()方法。

PS:

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.

1. context.Configuration.LazyLoadingEnabled = true;
2. context.Configuration.ProxyCreationEnabled = true;
3. UserAddresses 已在您的 User 类中定义为 Virtual 。