如何手动加载相关实体在N：N的关系？ [英] How to manually load related entities in a N:N relationship?

问题描述

我使用EF5当的关系是1：N，如果我想加载相关实体我做到以下几点：

I am using EF5 and when the the relationship is 1:N, if I want to load related entities I do the following:

• 使用T-SQL，我从数据库加载主实体那样的T-SQL：

• With T-SQL I load from database the main entities with a T-SQL like that:

select * 
from MainEntities 
where ...

与T-SQL我加载相关实体

with T-SQL I load the related entities

select * 
from RelatedEntities 
where IDMainEntity IN (---)

此时EF填充主的财产导航与相关实体的实体。此外，在的DbContext 我有每种类型的所有实体。

At this point EF populate the property navigation of the main entities with the related entities. Also, in the local property of the type of each entity in the dbContext I have all the entities of each type.

但是，如果我做同样的一个N：N的关系，我没有关系的中间表的实体，当我执行查询我在当地的的DbContext 每种类型的实体，但不填充属性导航。

However, if i do the same with a N:N relationship, I don't have the entity of the middle table of the relation, and when I execute the queries I have in the local of the dbContext the entities of each type, but the property navigation is not populated.

我想知道是什么原因，如果它存在，有些另类。

I would like to know why and if it exists some alternative.

，因为我想用T-SQL的创建动态查询，我用这种方式。如果我使用预先加载我没有同样的灵活性，动态查询比当我使用TSQL，这是效率较低。如果我使用明确加载我做N个附加的查询，在主要企业的业绩，有了我的​​路，我只有一个附加查询每条记录的一个，因为我得到的所有相关实体的一次。如果我使用延迟加载我有同样的问题，N其他查询

I use this way because I want to use T-SQL for create dynamic queries. If I use eager loading I don't have the same flexibility to dynamic queries than when I use TSQL, and it is less efficient. If I use explicit loading I to do N additional queries, one of each record in the results of the main entity With my way, I only one additional query, because I get all the related entities at once. If I use lazy loading I have the same problem, N additional queries.

为什么当关系是N EF不填充相关的属性：N

Why EF does not populate the related properties when the relation is N:N?

感谢。

推荐答案

你所谈论的就是所谓的的功能关系跨度或关系Fixup时的确实 - 因为你已经注意到 - 它不为许多到许多关系的工作。它仅当关联的至少一个端部具有多重性1（或0..1），也就是说，它适用于一对许多或一对一的关系的工作原理。

The feature you are talking about is called Relationship Span or Relationship Fixup and indeed - as you have noticed - it does not work for many-to-many relationships. It only works if at least one end of the association has multiplicity 1 (or 0..1), i.e. it works for one-to-many or one-to-one relationships.

关系跨度依赖于有一个外键的实体。如果它有一个明确的外键属性不要紧（外键关联的）或仅在相应的数据库表的外键列，而不在模式（一个属性的独立协会）。在这两种情况下，当该实体被加载的FK值将被加载到的上下文。在此基础上外键值EF能够弄清楚，如果具有相同的主键值，因为这FK值相关的实体附加到上下文，如果是，它可以修正的关系，也就是说，它可以填充导航性能正常。

Relationship Span relies on an entity having a foreign key. It doesn't matter if it has an explicit foreign key property (foreign key association) or only a foreign key column in the corresponding database table without a property in the model (independent association). In both cases the FK value will be loaded into the context when the entity gets loaded. Based on this foreign key value EF is able to figure out if a related entity that has the same primary key value as this FK value is attached to the context and if yes, it can "fixup the relationship", i.e. it can populate the navigation properties correctly.

现在，在一个多一对多的关系都相关实体没有一个外键。外国密钥存储在这种关系的联系表， - 如你所知 - 链接表中没有相应的模型实体。其结果是外键将永远不会被加载，因此，上下文无法确定哪个附相关的实体，并且不能修正内容的多对多的关系，并填充导航集合

Now, in a many-to-many relationship both related entities don't have a foreign key. The foreign keys are stored in the link table for this relationship and - as you know - the link table does not have a corresponding model entity. As a result the foreign keys will never be loaded and therefore the context is unable to determine which attached entities are related and cannot fixup the many-to-many relationship and populate the navigation collections.

在这里EF会支持你打造一个多一对多的关系与人口导航集合了正确的对象图唯一的LINQ查询渴望加载...

The only LINQ queries where EF will support you to build the correct object graph with populated navigation collections in a many-to-many relationship are eager loading...

var user = context.Users.Include(u => u.Roles).First();

...或者懒加载...

...or lazy loading...

var user = context.Users.First();
var rolesCount = user.Roles.Count();
// Calling Count() or any other method on the Roles collection will fill
// user.Roles via lazy loading (if lazy loading is enabled of course)

...或与结果到导航集合的直接分配明确的装载

...or explicit loading with direct assignment of the result to the navigation collection:

var user = context.Users.First();
user.Roles = context.Entry(user).Collection(u => u.Roles).Query().ToList();

所有其他的方式来加载相关实体 - 样突出，直接的SQL语句，甚至明确加载不分配到导航收集利用，即 .Load（）而不是 .Query（）了ToList（）在过去代码段以上 - 不会修正内容的关系，将离开导航集合空

All other ways to load the related entities - like projections, direct SQL statements or even explicit loading without assignment to the navigation collection, i.e. using .Load() instead of .Query().ToList() in the last code snippet above - won't fixup the relationship and will leave the navigation collections empty.

如果您打算，而不是LINQ查询我能唯一的选择来执行主要的SQL查询看到的是，你写你自己的关系管理。你必须查询链路表除了两个相关实体的表。你可能需要一个辅助型（不是一个实体），并收集保存链接表，并可能是一个辅助程序，通过检查你作为找到实体的主键值填充导航集合两个FK列中的值附着在 DbSet< T>。当地收藏和FK值辅助集合

If you intend to perform mainly SQL queries rather than LINQ queries the only option I can see is that you write your own relationship management. You would have to query the link table in addition to the tables for the two related entities. You'll probably need a helper type (that is not an entity) and collection that holds the two FK column values of the link table and probably a helper routine that fills the navigation collections by inspecting the primary key values of the entities you find as attached in the DbSet<T>.Local collections and the FK values in the helper collection.

这篇关于如何手动加载相关实体在N：N的关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！