With Entity Framework how to create nested objects without one massive query resultset or hundreds of small queries?

Question

I'm using EF to populate objects which I then interact with in my business layer code. The objects have several levels but let's first simplify it to a typical master-detail example with Order and OrderLine.

Let's say I need to retrieve 50 orders each of which has about 100 order lines, and I need all that data. What's the most efficient way of doing this in EF?

If I do it like this:

var orders = context.Orders.Where(o => o.Status == "Whatever")
                .Include(order => order.OrderLines)
                .ToList();

Then it'll run a single query that pulls back 50x100 rows, then it cleverly assembles that into 50 Order objects, each with their own OrderLine objects.

If instead I don't do the Include() and I later iterate over each Order and its OrderLines, then it'll issue 50 separate queries to get the OrderLine data, i.e. one for each Order.

So far the .Include() seems great: I pull back a bit more data than needed from SQL but that's much better than issuing 50 extra queries. But is there a way I can choose to issue 2 queries, one to get Order and one to get OrderLine, and have EF connect the objects automatically?

A more realistic scenario where I want this is if the data is more complex. Let's say I want objects like this (where Product is the thing being bought in the OrderLine and ProductPart is a number of bits used to make the Product):

- Order
   - OrderLine (avg 100 per Order) 
       - Product (1 per OrderLine)
           - ProductPart (avg 20 per Product)

Now if I do a .Include() with ProductPart, it'll make the query results much bigger. Or if I don't Include() then it'll issue separate queries for each Product.

Is there a third way where I can get all the Order, OrderLine and Product data in one query and all the ProductPart data in another query, and magically have EF connect the objects up for me?

UPDATE:

I just read about AsSplitQuery() which seems to be what I'm looking for but is only available in EF Core 5 which isn't stable till Nov 2020 (?). I'm looking for a way to achieve this in EF6.

A bit more research and I found https://entityframework.net/improve-ef-include-performance which suggests two approaches when you have multiple object types coming off the parent object:

Execute multiple EF commands to pull back the same list of parent objects, but each time Include()-ing different child object types. EF will hook up the related objects it's already pulled from the db, apparently.
Use the EF+ library which seems it can do something like AsSplitQuery()

I'm not sure if this works with my case where there's more levels of nesting rather than just different types of objects off the parent. My guess is yes, if I'm clever about it. Any tips?

Jason · Accepted Answer · 2020-07-23 22:46:29Z

2

Something like this might destructure your object results a bit, but should perform two separate queries.

var ordersQuery = context.Orders.Where(o => o.Status == "Whatever");

var orderLineGroups = ordersQuery
   .SelectMany(o => o.OrderLines)
   .ToLookup(l => l.OrderID); // <- Not sure what your FK name is

var orders = ordersQuery.Select(o => new {
   Order = o,
   OrderLines = orderLineGroups[o.Id]
}).ToList();

answered Jul 23, 2020 at 22:46

Jason

7316 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guru Stron Over a year ago

Why do you need second one with anonymous object? You can just select orders and after that fill in orderlines in foreach.

Jason Over a year ago

Yes, you could, and you would have to include the .Where expression in the second statement as well. But it would work. Just more verbose.

Guru Stron · Accepted Answer · 2020-07-23 23:29:30Z

2

Not sure that it will work in EF6 but you can try rely on tracking and relationship fix up. Next one works for me in the latest EF Core:

public class Make
{
    public int MakeId { get; set; }
    public string Name { get; set; }
    public ICollection<Model> Models { get; set; }

    public Make()
    {
        Models = new HashSet<Model>();
    }
}

public class Model
{
    public int ModelId { get; set; }
    public string Name { get; set; }
    public Make Make { get; set; }
    public int MakeId { get; set; }
}


ctx.Makes.Where(m => m.Name=="3").SelectMany(m=> m.Models).ToList();
ctx.Makes.Where(m => m.Name=="3").ToList(); // makes have models filled in

answered Jul 23, 2020 at 23:29

Guru Stron

151k11 gold badges186 silver badges232 bronze badges

Comments

Jeremy Lakeman · Accepted Answer · 2020-07-24 04:29:45Z

1

If you are issuing tracking queries, then EF will fixup all the references while the results are being returned. You can also use the Load method to load the results & fixup references without constructing a list.

For example;

var orders = context.Orders
    .Where(o => o.Status == "Whatever")
    .ToList();

context.OrderLines
    .Where(l => l.Order.Status == "Whatever")
    .Include(l => l.Product) // maybe this is a reasonable tradeoff?
    .Load();

context.ProductPart
    .Where(p => p.Product.OrderLines.Any(l => l.Order.Status == "Whatever"))
    .Load();

answered Jul 24, 2020 at 4:29

Jeremy Lakeman

11.6k30 silver badges34 bronze badges

Collectives™ on Stack Overflow

With Entity Framework how to create nested objects without one massive query resultset or hundreds of small queries?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related