Improving LINQ to entity queries

Improving LINQ to Entities Queries

I think we can all agree that Entity Framework (EF) makes .NET programming a lot easier than any other platform because it deals with most of the boilerplate data representation tasks. Instead of having to maintain connections and write complex SQL queries, EF will do it for you. All you need to do is generate a data model and write the appropriate LINQ expressions against it. However, the way these expressions are structured may have tremendous impact on the total query performance. In this post, I’ll try to show some of the possible pitfalls and their solutions regarding LINQ to Entities queries in an enterprise application.

Data Model

Suppose we have the following simple data model for a globalizable e-shop:

[frame type="shadow" width="400" height="265"]

model

We are now tasked with allowing users to search for products by name, price range and category name, while displaying all relevant information. Let’s suppose we have a repository class with a property containing an instance of the EF context:

Before we write the query we should create an appropriate class that will contain all of our filter properties:

Note that the Language property will be populated from the system depending on the way we’ve chosen to resolve the user’s locale. It is required data so that we know in which language we should be performing the filtering.

LINQ to Entities Query

We can now write an initial version of the query residing as a method within the product repository class:

With this LINQ expression we managed to select all requested data without having to write a much more complex SQL query. Entity Framework will do that for us. This expression has a couple of hidden flaws; however, they will only surface when we start adding more filter properties and the database grows larger. For one, the empty filter switches will not be omitted by EF when the respective value is null. Instead, it will be carried into the SQL as CASE statements. Also, the call of the Any extension method on the navigation properties will execute a separate subquery to fetch the requested results instead of joining on the provided keys (the ID which is implicitly provided by the navigation property and Language). Things get even worse if we add pagination and sorting to the client side – which we will ultimately need to do – but for the sake of keeping the example simple, let’s ignore this for now.

So what can we do to improve the query’s expression?

Optimizing LINQ to Entities – Where Clauses

In order to avoid having CASE statements in our output SQL we have to rewrite our where expression block. Here is where LINQ’s extension methods come into play. Instead of writing…

…we should use the Where LINQ extension method wrapped inside an if-block. For example:

Using this approach, the filter will only be added to the query’s expression if the filter had a value worth including. Imagine how the code would look if we had to do this for 20 or even more filters. What if we could define our own extension methods that could wrap this logic and benefit from LINQ’s fluent API? To achieve that, we need to create a static class somewhere in our solution that will contain the extension methods. The static class itself would look something like this:

An interesting side effect of moving this logic away from the Entity Framework is the ability to use string.IsNullOrWhiteSpace instead of string.IsNullOrEmpty. The former is not supported in LINQ to Entities context and will generate a runtime exception. However, because the if-blocks are not inside the LINQ to Entities context, we can use the null or whitespace check freely. As an addition to nullable and string filters, we may also have collections with IDs or strings depending on the requirements. That is why there is an extra method defined that checks if the collection has elements and applies the filter to the expression. Using the above code we can rewrite our query like this:

With this approach the output SQL will contain only the filters that need to be attached without losing code readability. However, we still need to remove the unnecessary subqueries generated by the Any calls on the navigation properties.

Using Join and Anonymous Classes in Expressions

Using join in LINQ queries is simple and easy but our problem is passing the joined data down to the extension methods we previously created, that’s where the anonymous class comes in:

It may look as if we’re about to select a lot more data than we actually need but the beauty of EF is that it will determine exactly which columns from the database we need by evaluating the very last select – the one before ToList(). In short, making that anonymous object will have no performance impact because it’s part of the expression only, not the actual SQL. Now, instead of making subqeuries the select script will use joins which will make the database’s work easier and more time-efficient.

d761fd08 ffc9 4ba1 bbed f818f1c3961b
1 reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">