WAQS Querying: Eager loading

7 reasons to use WAQS

WAQS documentation

 

In my audits on EF performance issues, 45% of them are caused by lazy loading and 45% are caused by Eager loading. So, as Lazy Loading, it is a major issue in Entity Framework developers usage.

So, what is the issue with eager loading?

The Include method

With Entity Framework, eager loading is associated to the Include method.

Contrary to lazy loading, eager loading allows you to load an entity graph in one SQL query.

A beginner developer could think that it’s the best way. But it is not.

 

So it’s very important to remind basics.

First, as I already wrote, object concept does not exist in SQL.

 

Then, a very important point to know is the fact that Entity Framework uses only one result set per LINQ To Entities query.

Imagine a basic model with a Customer entity with only two properties  (Id and Name) and an Order entity with three properties (Id, CustomerId and OrderDate).

Now imagine that, in DB, we only have two customers with three orders for each of them.

To get the customers with their orders loaded using L2E, we can use the Include method:

var query = context.Customers.Include(c => c.Orders);


If we look to the SQL result, this query returns a result set like this:



Customer.Id

Customer.Name

Order.Id

Order.CustomerId

Order.Date

C1Id

C1Name

O1

C1Id

O1Date

C1Id

C1Name

O2

C1Id

O2Date

C1Id

C1Name

O3

C1Id

O3Date

C2Id

C2Name

O4

C2Id

O4Date

C2Id

C2Name

O5

C2Id

O5Date

C2Id

C2Name

O6

C2Id

O6Date



As you can see, customer data are repeated for each order.



Here the quantity of data is ridiculously small but, with a huge amount of data, we can easily imagine what it could be, particularly if Customer has some important size columns like image for example.



 



Moreover, in addition to a too important transfer from the DB to the server, you have to know that having many entities in one row has a big impact on Entity Framework entity materialization cost.



 



In addition, Entity Framework Include method does not allow you to load only one piece of relative data for “to many” relationships (to only load current year customer’s orders for example).



 



WAQS Include



I already wrote about EF Include in the following posts:



EF: Why Include method is an anti-pattern IMHO?



EF: Why Include method is an anti-pattern IMHO even with many to one navigation properties? 2/3



EF: Why Include method is an anti-pattern IMHO? 3/3



EF: Why Include method is an anti-pattern IMHO? Conclusion



EF: why Include method is an anti-pattern IMHO? Part 5: many to many relationships



But a lot of people’s feedbacks were the fact that my code was too complex comparing to Include method which is so easy to use…



With WAQS, I wanted to have the performance of my way with the easily of the Include method.



 



To resume these posts, with a performance point of view, it’s better to have one query per entity set. So two in our sample.



 



The issue with this way is the fact that you could sometimes have trunked data graphs.

Indeed, it could happen if we have a concurrent DELETE or INSERT between the two queries execution.



Anyway, comparing to performance improvement, we often can assume the risk.



BTW, we will see at the end of this post how to have a “basic” Include.



 



WAQS uses the ExecuteQueries logic to execute Include queries (one per entity set) in one HTTP request and, if Parallel option is true, SQL queries are executed in parallel on the DB.



 



In addition, comparing to Entity Framework, WAQS takes advantage of the fact that it’s a meta-framework and not a framework.



Developers you code a Framework like Entity Framework don’t know the entities model which implies, at runtime, some test on the model to determine the query to execute. With a meta-framework like WAQS, these tests could be done when you generate the code so at pre compilation and, with this way, the executed code could be optimal.



 



Base on this logic, WAQS generates one Include method per navigation property.



var customers = await _context.Customers.AsAsyncQueryable().IncludeOrders().ExecuteAsync();


In addition, WAQS supports partial Include:



var customers = await _context.Customers.AsAsyncQueryable().IncludeOrdersWithExpression (
orders => orders.Where(o => _context.DbDateTime.Year == o.OrderDate.Year))
.ExecuteAsync();


Note that, if you want to load more than one graph level, you can use the following code:



var customers = await _context.Customers.AsAsyncQueryable().IncludeOrdersWithExpression (orders => orders
     .IncludeOrderDetailsWithExpression (
         ordersDetails => ordersDetails.IncludeProduct())
     .IncludeInvoice()).ExecuteAsync();


Or both:


var customers = await _context.Customers.AsAsyncQueryable().IncludeOrdersWithExpression (orders => 
    orders.Where(o => _context.DbDateTime.Year == o.OrderDate.Year).IncludeOrderDetails()).ExecuteAsync();



 



Note that there is a trap when you use many queries to do an Include. Base on SQL engine working, you must use an order by including the key if you use pagination in your query (First(OrDefault), Single(OrDefault), Take, Skip) in order to be sure to load orders (in our sample) associated to loaded customers.



 



Even if I do not recommend it for performance aspect, you can get customers with their orders in only one SQL query like this:



var customers = (await _context.Customers.AsAsyncQueryable().Select(c => new { Customer = c, Orders = c.Orders })
.ExecuteAsync()).AsEnumerable().Select(co => co.Customer);
This entry was posted in 12253, 16868, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>