Improve Entity Framework Performance
In a recent
article on using LINQ with Entity Framework, I recommended using Entity Framework's navigation properties in your LINQ statements instead of joins. The code is easier to write and the SQL generated for you will automatically include the necessary joins.
However, the following won't generate an SQL statement that joins the Orders table to the Employees table because the LINQ query doesn't reference the Employees objects:
Dim res = From o In nw.Orders
Where o.Freight > 0
Select o
For Each ord In res
EmpName = ord.Employee.FirstName
'…code using the employee name
Next
In this case, the navigation properties aren't being used until the For…Each loop that follows the LINQ statement. The compiler isn't smart enough to spot that, so the SQL statement that's generated from the LINQ query won't include a join to the Employee table. Instead, Employee rows will probably be fetched one by one from the database as the code loops through the collection of Order objects retrieved through the LINQ query. This would be terrifically inefficient if, as this code does, every Order is going to have its related Employee object processed.
The solution is to give LINQ some advice about what you intend to do. In the same way that ADO.NET will process requests faster if you tell it more (for instance, if you know that only a single row will be retrieved, using ADO.NET's SingleResult option will give you better performance), LINQ will work faster for you if you tell it what you need. In this case, using the Include keyword tells LINQ that it should add a join to the SQL statement to fetch the related Employee rows:
Dim res = From o In nw.Orders.Include("Employees")
Where o.Freight > 0
Select o
As a developer, you'll have to make some decisions here. If, for instance, the loop in the previous example had an If statement that ensured that only some of the Employee records were going to be processed, it might make sense to omit the Include keyword. With the Include keyword gone, only the Employee rows that were actually needed in the loop would be fetched. You may need to run some tests to see if using Include makes your application run faster or slower. Fortunately, as with SQL, switching between the two versions of the LINQ statement is easy to do (delete or add the Includes), so it's relatively easy to do some time trials and see what gives you the best performance.
Posted on 10/26/2011