使用LINQ查询并创建具有一对多关系的对象 [英] query and create objects with a one to many relationship using LINQ

问题描述

在数据库中，我有两个具有一对多关系的表:

In the DB, I have a two tables with a one-to-many relationship:

orders          suborders
-----------     -----------
id              id
name            order_id
                name

我想查询这些表，最后得到一个订单对象列表，每个订单对象都包含一个子订单对象列表(或空列表).我也想在单个数据库查询中执行此操作，以使其表现良好.

I'd like to query these tables and end up with a list of order objects, each of which contains a list (or empty list) of suborder objects. I'd also like to do this in a single DB query so it performs well.

在传统的SQL查询领域，我会做类似的事情(原谅伪代码):

In traditional SQL query land, I'd do something like (forgive the pseudocode):

rs = "select o.id, o.name, so.id, so.name from orders o left join suborders so on o.id = so.order_id order by o.id"

orders = new List<Order>
order = null
foreach (row in rs) {
    if (order == null || row.get(o.id) != order.id) {
        order = new Order(row.get(o.id), row.get(o.name), new List<Suborders>)   
        orders.add(order)
    }    

    if (row.get(so.id) != null) {
        order.suborders.add(new Suborder(row.get(so.id) row.get(so.name))
    }
}

有没有办法使用LINQ-to-Entities获得相同的结果对象结构?请注意，我想从查询中获取新对象，而不是Entity Framework生成的对象.

Is there a way to get this same resulting object structure using LINQ-to-Entities? Note that I want to get new objects out of the query, not the Entity Framework generated objects.

以下内容使我接近，但引发异常:"LINQ to Entities无法识别该方法..."

The following gets me close, but throws an exception: "LINQ to Entities does not recognize the method..."

var orders = 
    (from o in Context.orders
     join so in Context.suborders on o.id equals so.order_id into gj
     select new Order 
     {
         id = o.id,
         name = o.name,
         suborders = (from so in gj select new Suborder
         {
             so.id,
             so.name
         }).ToList()
     }).ToList();

推荐答案

该解决方案最终变得非常简单.关键是使用组联接来使SQL进行子订单的左联接，并添加第二个ToList()调用以强制运行查询，因此您不必尝试在SQL Server上创建对象. /p>

The solution ends up being pretty simple. The key is to use a group join to get SQL to do the left join to suborders, and add a second ToList() call to force the query to be run so you're not trying to do object creation on the SQL server.

orders = Context.orders
    .GroupJoin(
        Context.suborders,
        o => o.id,
        so => so.order_id,
        (o, so) => new { order = o, suborders = so })
    .ToList()
    .Select(r => new Order
    {
        id = r.order.id,
        name = r.order.name,
        suborders = r.suborders.Select(so => new Suborder
        {
            id = so.id,
            name = so.name
        }.ToList()
    }).ToList();

此代码仅对所有对象及其子对象进行SQL查询.它还使您可以将EF对象转换为所需的对象.

This code only makes a single query to SQL for all objects and their child objects. It also lets you transform the EF objects into whatever you need.

这篇关于使用LINQ查询并创建具有一对多关系的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！