query and create objects with a one to many relationship using LINQ

Question

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.

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))
    }
}

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.

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();

possible duplicate of [What is the syntax for an inner join in linq to sql?](http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — Konrad Kokosa, Dec 18 '13 at 22:39
No. I don't want an inner join, my query is a left join. I'm also specifically looking for the way to create the list of objects that have the one-to-many relationship the DB structure has. Just doing a join isn't going to get me that. — Mike Roberts, Dec 18 '13 at 22:53
@MikeRoberts you should tag either EntityFramework or LinqToSQL — seddik, Dec 18 '13 at 23:30

Mike Roberts · Accepted Answer · 2013-12-19T20:27:03.137

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();

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.

score 0 · Answer 2 · answered Dec 18 '13 at 23:22

0

I Always create a virtualized Property for Relations

so just extend (add a property) to your order class :

  public class Order{
  ...

  List<Suborder> _suborders;

  public List<Suborder> Suborders{
  get {
        return _suborders ?? (_suborders = MyContext.Suborders.Where(X=>X.order_id==this.id).ToList());
       }
  ...
  }

so data will be fetched (pulled) only when you call the getters

answered Dec 18 '13 at 23:22

seddik

639
1
8
20

Wouldn't that still cause multiple queries to be sent to the DB? That's mostly what I'm trying to prevent so I can maintain good performance. – Mike Roberts Dec 19 '13 at 02:41
no, when you query the Order it won't query the suborders, only if you use something like `mylist=myorder.SubOrders` the will read from the DB – seddik Dec 19 '13 at 09:21

score 0 · Answer 3 · answered Dec 19 '13 at 12:00

How about this code ?

You can get a local cache.

List<Orders> orders = new List<Orders>();

private void UpdateCache(List<int> idList)
{
    using (var db = new Test(Settings.Default.testConnectionString))
    {
        DataLoadOptions opt = new DataLoadOptions();
        opt.LoadWith<Orders>(x => x.Suborders);
        db.LoadOptions = opt;
        orders = db.Orders.Where(x => idList.Contains(x.Id)).ToList();
    }
}

private void DumpOrders()
{
    foreach (var order in orders)
    {
        Console.WriteLine("*** order");
        Console.WriteLine("id:{0},name:{1}", order.Id, order.Name);

        if (order.Suborders.Any())
        {
            Console.WriteLine("****** sub order");

            foreach (var suborder in order.Suborders)
            {
                Console.WriteLine("\torder id:{0},id{1},name:{2}", suborder.Order_id, suborder.Id, suborder.Name);
            }
        }
    }
}

private void button1_Click(object sender, EventArgs e)
{
    UpdateCache(new List<int> { 0, 1, 2 });
    DumpOrders();
}

Output example below

*** order
id:0,name:A
****** sub order
    order id:0,id0,name:Item001
    order id:0,id1,name:Item002
    order id:0,id2,name:Item003
*** order
id:1,name:B
****** sub order
    order id:1,id0,name:Item003
*** order
    id:2,name:C
****** sub order
    order id:2,id0,name:Item004
    order id:2,id1,name:Item005

query and create objects with a one to many relationship using LINQ

3 Answers3

Linked