实体框架 - 渴望加载两对多关系 [英] Entity Framework - Eager load two many-to-many relationships

问题描述

对不起，这很长时间，但至少我觉得我得到了所有的信息才能理解和帮助？

我想加载数据我的数据库使用热切的加载。

数据设置在五个表中，设置两个级别的m：n关系。因此，有三个表格包含数据（按层次结构从上到下排列）：

  CREATE TABLE [dbo]。[关系]（
 [relation_id] [bigint] NOT NULL 
）
 
 CREATE TABLE [dbo]。[ways]（
 [way_id] [bigint] NOT NULL 
 
 
 CREATE TABLE [dbo]。[nodes]（
 [node_id] [bigint] NOT NULL，
 [纬度] [int] NOT NULL，
 [longitude] [int] NOT NULL 
）
  

前两个真的只包括自己的ID（钩住其他与此不相关的数据）。

在这三个数据表之间是两个m：n表，其中有一个排序提示： / p>

  CREATE TABLE [dbo]。[relations_ways]（
 [relation_id] [bigint] NOT NULL，
 [way_id] [bigint] NOT NULL，
 [sequence_id] [smallint] NOT NULL 
）
 
 CREATE TABLE [dbo]。[ways_nodes]（
 [way_id ] [bigint] NOT NULL，
 [node_id] [bigint] NOT NULL，
 [sequence_id] [smallint ] NOT NULL 
）
  

这本质上是OpenStreetMap数据结构的一部分。我让Entity Framework从这个数据库构建它的对象，并且完全按照表来设置类。
m：n表确实存在为类。 （我在EF中了解，您可以建立您的对象m：n关系，而不需要明确的中间类 - 我应该尝试以这种方式更改对象模型？）

我想做什么：我的入口点正是一个关系项。

我认为最好先加载中间m：n关系，然后在循环中迭代迭代，并加载最低的一个。我尝试以下述方式执行此操作

  IQueryable< relation> query = context.relations; 
 query = query.Where（...）; //过滤到一个
 query = query.Include（r => r.relation_members）; 
 relation rel = query.SingleOrDefault（）; 
  

加载关系，所有它只是一次访问数据库的1：n信息 - 好的，好。但是我注意到它仅加载1：n表，而不是中间的数据表方式。

如果我修改这样的行，这不会改变： p>

  query = query.Include（r => r.relation_members.Select（rm => rm.way））; 
  

所以我不能把这个中间级别加载到这里，似乎是吗？

我根本无法工作的是加载数据的节点级别。我尝试了以下内容：

  foreach（rel_relation_members中的relation_member rm）{
 IQueryable< way_node> query = rm.way.way_nodes.AsQueryable（）; 
 query = query.Include（wn => wn.node）; 
 query.Load（）; 
} 
  

这样做工作，热切地加载中级方式和所有1：n信息的way_node在每个迭代的一个语句中，但不来自节点的信息（纬度/经度）。如果我访问这些值之一，我触发另一次到数据库的旅程来加载一个单个节点对象。

最后一次行程是致命的，因为我要加载1个关系 - >各种方式 - > 2000个节点。所以最后我打到服务器1 + 300 + 300 * 2000 ...改进的空间，我想。

但是怎么样？我不能得到这个最后一个语句写入有效的语法和热切加载。
不感兴趣;有没有办法在一个行程中加载整个对象图，从一个关系开始？

解决方案

将整个图形加载到一个往返将是：

  IQueryable< relation> query = context.relations; 
 query = query.Where（...）; //过滤到一个
 query = query.Include（r => r.relation_members 
 .Select（rm => rm.way.way_nodes 
 .Select（wn => ; wn.node）））; 
 relation rel = query.SingleOrDefault（）; 
  

但是，由于你说 Include 最多 ...选择（rm => rm.way）没有工作，这不太可能会有效。 （如果它的工作性能可能不是很有趣，因为生成的SQL的复杂性以及此查询将返回的数据和实体的数量。）

首先要进一步研究的是为什么 .Include（r => r.relation_members.Select（rm => rm.way））不起作用，因为它似乎正确。您的模型和映射到数据库是否正确？

通过显式加载获取节点的循环应如下所示：

pre> foreach（rel.relation_members中的relation_member rm）{
context.Entry（rm）.Reference（r => r.way）.Query（）
.Include（w => w.way_nodes.Select（wn => wn.node））
.Load（）;
}


Sorry for this being so long, but at least I think I got all info to be able to understand and maybe help?

I would like to load data from my database using eager loading.

The data is set up in five tables, setting up two Levels of m:n relations. So there are three tables containing data (ordered in a way of hierarchy top to bottom):

CREATE TABLE [dbo].[relations](
    [relation_id] [bigint] NOT NULL
)

CREATE TABLE [dbo].[ways](
    [way_id] [bigint] NOT NULL
)

CREATE TABLE [dbo].[nodes](
    [node_id] [bigint] NOT NULL,
    [latitude] [int] NOT NULL,
    [longitude] [int] NOT NULL
)

The first two really only consist of their own ID (to hook other data not relevant here into).

In between these three data tables are two m:n tables, with a sorting hint:

CREATE TABLE [dbo].[relations_ways](
    [relation_id] [bigint] NOT NULL,
    [way_id] [bigint] NOT NULL,
    [sequence_id] [smallint] NOT NULL
)

CREATE TABLE [dbo].[ways_nodes](
    [way_id] [bigint] NOT NULL,
    [node_id] [bigint] NOT NULL,
    [sequence_id] [smallint] NOT NULL
)

This is, essentially, a part of the OpenStreetMap data structure. I let Entity Framework build it's objects from this database and it set up the classes exactly as the tables are. The m:n tables do really exist as class. (I understand in EF you can build your objects m:n relation without having the explicit in-between class - should I try to change the object model in this way?)

What I want to do: My entry point is exactly one item of relation.

I think it would be best to first eager load the middle m:n relation, and then in a loop iterate over that and eager load the lowest one. I try to do that in the following way

IQueryable<relation> query = context.relations;
query = query.Where( ... ); // filters down to exactly one
query = query.Include(r => r.relation_members);
relation rel = query.SingleOrDefault();

That loads the relation and all it's 1:n info in just one trip to the database - ok, good. But I noticed it only loads the 1:n table, not the middle data table "ways".

This does NOT change if I modify the line like so:

query = query.Include(r => r.relation_members.Select(rm => rm.way));

So I cannot get the middle level loaded here, it seems?

What I cannot get working at all is load the node level of data eagerly. I tried the following:

foreach (relation_member rm in rel.relation_members) {
    IQueryable<way_node> query = rm.way.way_nodes.AsQueryable();
    query = query.Include(wn => wn.node);
    query.Load();
}

This does work and eagerly loads the middle level way and all 1:n info of way_node in one statement for each iteration, but not the Information from node (latitude/longitude). If I access one of these values I trigger another trip to the database to load one single node object.

This last trip is deadly, since I want to load 1 relation -> 300 ways which each way -> 2000 nodes. So in the end I am hitting the server 1 + 300 + 300*2000... room for improvment, I think.

But how? I cannot get this last statement written in valid syntax AND eager loading. Out of interest; is there a way to load the whole object graph in one trip, starting with one relation?

解决方案

Loading the whole graph in one roundtrip would be:

IQueryable<relation> query = context.relations;
query = query.Where( ... ); // filters down to exactly one
query = query.Include(r => r.relation_members
    .Select(rm => rm.way.way_nodes
        .Select(wn => wn.node)));
relation rel = query.SingleOrDefault();

However, since you say that the Include up to ...Select(rm => rm.way) didn't work it is unlikely that this will work. (And if it would work the performance possibly isn't funny due to the complexity of the generated SQL and the amount of data and entities this query will return.)

The first thing you should investigate further is why .Include(r => r.relation_members.Select(rm => rm.way)) doesn't work because it seems correct. Is your model and mapping to the database correct?

The loop to get the nodes via explicit loading should look like this:

foreach (relation_member rm in rel.relation_members) {
    context.Entry(rm).Reference(r => r.way).Query()
        .Include(w => w.way_nodes.Select(wn => wn.node))
        .Load();
}

这篇关于实体框架 - 渴望加载两对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！