使用LINQ to Entities编写嵌套联接 [英] Writing a nested join with LINQ to Entities

问题描述

我很确定这里有个例子，因为我不知道确切的用语，所以我找不到它.所以，请客气.

I'm pretty sure there's an example of this somewhere on here, I just can't find it as I don't know the exact terms. So please, be kind.

问题应该很简单:

我有一个Azure SQL数据库，该数据库的设计有些错误(例如，缺少外键等).由于此时我无法重新设计数据库，因此必须通过查询手动处理关系.由于数据库也是我们软件的瓶颈，因此我必须尝试以尽可能少的数据库命中来完成查询.

I have an Azure SQL database, somewhat badly designed (ie missing foreign keys and such). As I cannot redesign the database at this point, I have to handle relations manually via queries. As the database is also the bottleneck of our software, I must try and complete queries with as little database hits as possible.

我有实体A，B，C和D.实体A有多个实体B(与未注册的外键连接)，实体B有多个实体C，实体C有多个实体D.

I have entities A, B, C and D. An entity A has several entities B (connected with an unregistered foreign key), an entity B has several entities C and an entity C has several entities D.

我具有实体A的ID，并希望以最佳方式返回所有连接的实体的树，直到Ds.

I have the ID of entity A, and would like to return a tree of all connected entities up to Ds, as optimally as possible.

开始:

from A in dbA.Where(e=>e.Id==IDParameter)
join B in dbB on A.Id equals B.AId into Bs

现在，我需要对B中的每个B进行C转换，然后对C中的C进行同样的转换.如果我采用obvoius的方式，先将Ds和Cs结合在一起，然后将Bs和Cs结合在一起，最后再将As和Bs，这意味着将所有Ds与Cs，然后将所有Ds连接起来，直到我终于到达IDParameter过滤器为止.似乎无效.

Now I'd need to do this with every B in Bs for C and again with Cs for D. If I go the obvoius way, and join Ds and Cs first, and then Bs and Cs, and finally the As and Bs, this means joining all Ds with Cs, then all... you get the point, until I finally get to the IDParameter filter. It just seems ineffective.

在单个查询中是否有适当的C#方法做到这一点?

Is there a proper C# way of doing this in a single query?

推荐答案

定义了关系后，您可以使用手动联接模拟EF的行为.您只需要使用组联接和投影.像这样:

You can simulate with manual joins what EF does when you have relationships defined. All you need is to use Group Joins and projections. Something like this:

var result =
    (from a in db.A
     where a.Id == IDParameter
     join b in db.B on a.Id equals b.AId into Bs
     select new
     {
         a,
         Bs =
            (from b in Bs
             join c in db.C on b.Id equals c.BId into Cs
             select new
             {
                 b,
                 Cs =
                    (from c in Cs
                     join d in db.D on c.Id equals d.CId into Ds
                     select new
                     {
                         c,
                         Ds = Ds.ToList()
                     }).ToList() 
             }).ToList()
     }).ToList();

这篇关于使用LINQ to Entities编写嵌套联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！