Performance - get data through navigation property vs compiled query

Question

I have compiled queries for both, the main entity customer and for related entities(orders).

var customer = MyService.GetCustomer<Customer>().where(c => c.Id == fooId).ToList();
var customerOrders = MyService.GetOrders<Order>().where(o => o.CustomerId == fooId && o.IsActive).ToList();

But I think I can get all orders through navigation properties instead of compiled query calls since customer is already loaded in memory by the code below:

var customerOrders = customer.Orders.where(o => o.IsActive).ToList();  // I didn't do filter further more

But when I measure the timings I couldn't find any considerable amount of difference (The DB has 500 customers and 4000 orders. And every particular customer has 30 active orders and around 400 inactive orders).

Which one of these two would have a better performance?

I could not fully understand this related question

Answer 1

Linq to Entities translates the Linq queries to SQL.

var customer = MyService.GetCustomer<Customer>().where(c => c.Id == fooId).ToList();

This can actually be simplified since c.Id is unique:

Customer customer = MyService.GetCustomer<Customer>().SingleOrDefault(c=> c.Id == fooId);

What you do here is just get a customer with a certain Id first. (constant and does not depend on the Count of the Orders you query)

Then you query the orders of this customer(this query is dependend on how many orders this customer have):

var customerOrders = customer.Orders.where(o => o.IsActive).ToList();

Then you do another query for which will lead to the exact same SQL statement as the one above.

var customerOrders = MyService.GetOrders<Order>().where(o => o.CustomerId == fooId && o.IsActive).ToList();

This is why the performance difference is only the first query.

Answer 2

You way depend of your case. if you're going to actively use related entities: Best way include this in you query:

using System.Data.Entity;
...
var customer = MyService.GetCustomer<Customer>().where(c => c.Id == fooId).Include(u => u.Orders).ToList();

In other cases prefer lazy loading.