0

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

Community
  • 1
  • 1
ManirajSS
  • 2,295
  • 5
  • 26
  • 50

2 Answers2

0

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.

Florian Schmidinger
  • 4,682
  • 2
  • 16
  • 28
  • So navigation property query is better than complied query in this case? – ManirajSS Jan 21 '15 at 08:48
  • I dont fully get what you mean by compiled query. However Linq to Entities provides only a small amount of supported functions. If you exceed those it would require to get a bigger amount of data from the database then do the computation and possible reduction with the full data which will then reduce performance. Have a look here https://msdn.microsoft.com/en-us/library/bb738550.aspx – Florian Schmidinger Jan 21 '15 at 09:03
  • for example if you use some Regex to filter your data you would have to call for example .ToList() or .AsEnumerable() which will send the SQL statement to the Database and Map the resultset to objects. Then you can use Linq to Objects to do your Regex based filtering – Florian Schmidinger Jan 21 '15 at 09:09
  • In your example i dont see any compiled Queries – Florian Schmidinger Jan 21 '15 at 09:13
0

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.

Ilya Sulimanov
  • 7,636
  • 6
  • 47
  • 68