The power of Entity Framework Core and LINQ Expression Tree combined

7 min readJun 19, 2022

Note: Is not mandatory but highly recommended to read first this post where we create the sorting class and give some bases for this post.

Important

This will be a long post because it tries to explain complex concepts, so bear with me on this and follow along. I’m pretty sure that you will find it quite interesting and hopefully didactic!

Entity Framework Core

Entity Framework Core is a great data access framework. It is based on the old Entity Framework which was available until the .Net Framework version 4.7.x.

EF Core can serve as an object-relational mapper (O/RM) which enables .NET developers to work with a database using .NET objects and eliminates the need for most of the data-access code that typically needs to be written.

If you don’t know EF Core, I recommend checking out the official documentation by Microsoft which will give you a great overview and core fundamentals, there are also a bunch of courses on e-learning platforms like (Udemy, Pluralsight, etc…) that offer a detailed view of EF and all its features.

For the sake of simplicity, I’ll refer to Entity Framework Core as only EF during this article.

What is an expression tree?

Expression trees represent code in a tree-like data structure, where each node is an expression
source Microsoft Docs

In other words, Expression Trees is a very power full tool to express how a code should perform in runtime, doing modifications to the executable code, and the creation of dynamic queries using LINQ.

So let’s begin creating an abstract Repository class so our database resources can inherit.

This is just a good practice for decoupling database infrastructure code from your service/application and taking the advantage of dependency injection to access I/O bound resources like files or databases.

Let’s break down the AddSorting method

The Queryable object

What is an IQueryable<TEntity>?

The IQueryable interface is a data source agnostic interface that uses LINQ to allow us to express using code how a data collection should be queried, filtered, and ordered.

For example

comments
  .Where(comment => 
    comment.Description.Contains('great post') && 
    comment.CreatedAt > '2022-01-01')
  .OrderByDescending(comment => comment.CreatedAt);

Will be translated to the appropriated SQL statement depending on the database technology.

If you are using MySQL for example, the code above will be translated by EF to

SELECT `c`.`Id`, `c`.`Description`, `c`.`CreatedAt`
FROM `Comment` AS `c`
WHERE (LOCATE(CONVERT('great post' USING utf8mb4) COLLATE utf8mb4_bin, `c`.`Description`) > 0) AND (`c`.`CreatedAt` > '2022-01-01 00:00:00.000000'))
ORDER BY `c`.`CreatedAt` DESC

This is just an example and the actual query can change depending on the EF version you are using since they are constantly evolving and improving the framework.

This is a great functionality of Entity Framework since it takes away the need for us to build the SQL statements ourselves, avoiding big chunks of strings in the code that can’t be easily tested in isolation.

By using EF we can unit test everything since all queries are C# code.

Note: Performance has been always a source of debate when talking about EF and is not my intention to discuss this topic here. As I said before, EF is constantly evolving and improvements are always added to the framework.
My suggestion is during the development phase to enable EF to output all SQL statements somewhere, for example, your application log output so you can check how the queries look like and follow Microsoft guidelines for an efficient query.

Expression Tree

We start building the tree expression by creating a parameter expression that will be used to set up the property we want to sort as a parameter input to the LINQ Sort By function.

After we create an expression that represents accessing the property we want to sort. This is known as Member Expression.

We can see lambda expressions as an abstract runtime builder of a .Net method body.

For example

public int Sum(int x, int y) 
{
  return x + y;
}

Can be expressed in a LambdaExpression tree as

using System;
using System.Linq.Expressions;
using System.Collections.Generic;ParameterExpression paramExprX = Expression.Parameter(typeof(int), "x");
ParameterExpression paramExprY = Expression.Parameter(typeof(int), "y");LambdaExpression lambdaExpr = Expression.Lambda(
    Expression.Add(
        paramExprX,
        paramExprY
    ),
    new List<ParameterExpression>() { paramExprX, paramExprY }
);Console.WriteLine(lambdaExpr);
// Outputs (x, y) => (x + y)Console.WriteLine(lambdaExpr.Compile().DynamicInvoke(1, 2));
// Outputs 3

Here we are trying to identify which method we should use. The caveat with ordering a collection is we can have several “order by” clauses in a query, but how do we express this using LINQ?

Something like this

myColletion
  .Where(x => x.Name == nameParam)
  .OrderBy(x => x.CreatedAt)
  .ThenBy(x => x.UpdatedAt)
  .ThenByDescending(x => x.CodeNumber)
  ....

As you can see, we can’t just chain OrderBy method calls, because it doesn’t work like this.

LINQ needs to understand the sequence because it influences the end result.

So basically an IQueryable interface has access to OrderBy<> and OrderByDescending<>, and the return for both methods is an IOrderedQueryable which means that it already has at least 1 ordering setup, therefore, the IOrderedQueryable has access to ThenBy<> and ThenByDescending<>

That’s the reason for verifying in the switch case the type of the query expression, so we can chain properly the correct sorting method expressions.

After identifying which method we want to add to the queryable object OrderBy<>, ThenBy<>, OrderByDescending<>, or ThenByDescending<> we store the MethodCallExpession, which is basically the method body signature, for example

Expression<Func<int, int, int>> sum = (int x, int y) => x + y;
Console.WriteLine(sum.Body); // Outputs (x + y)

But since the LINQ methods have an expression as a function we parse to MethodCallExpession to make sure it is compatible with the LINQ extensions parameters.

Then we get the generic method definition, which you can see as the method blueprint.

After that, we use that blueprint to create our own method setting up the entity type and also which property type the method should use.

And in the end, we set up how the generic method we created should be invoked using the lambda defined at the beginning.

All these concepts can look very abstract and complex, and indeed is one of the most complex topics of the framework, but also one of the most powerful resources of .Net.

Once you get your head around it you see that everything becomes easier to understand.

Here is a more detailed demonstration of what is going on behind the scenes

You check a running example of the above code here https://dotnetfiddle.net/Qd71N0. I encourage you to play around, change the parameters, expressions, and methods and check the console output, this is the best way for learning.

Repository Class Example

So how this would look like in a repository class?

Is a good practice to create a class to hold all the possible filters and queries, and in this example, we have created a class that will hold the search string and the list of sorts (Sort class created here)

The concrete implementation of the repository class

In the example above the first line creates an instance of an IQueryable object

var catalogQuery = _context.Catalog.AsNoTracking();

So basically we get “Catalog” DbSet from the EF context which holds the abstract collection of catalogs and we use the AsNoTracking to signal EF to not track these objects in the context.

Is a good practice when we are querying for a read-only scenario to prevent EF from tracking all objects, this will improve the performance. More details here.

Once we have the “var catalogQuery” as an IQueryable instance, we can start a chain of LINQ expressions to this object.

Every time we call “catalogQuery.Where(…” what this is actually doing is adding a where expression and returning the instance of the object now with the where. This is called a Builder Pattern

So when we do “catalogQuery = catalogQuery.Where(…” we are replacing the instance reference from the variable catalogQuery with a new one containing the Where clause now.

Looping over the sorting collection, what is does, it receives the Queryable object and adds a sorting expression to this object for each Sort item in the list.

And in the end returns the Queryable object with all the queries, filters, and ordering attached to it.

The last part executes everything and returns the result.

return await catalogQuery.ToListAsync(cancellation);

By now we have a fully unit testable dynamic sorting mechanism where no matter which properties you want to sort, the Entity type, or the number of changes you will do on the Entity itself, adding or removing properties, this Sort mechanism is Close for Modifications and Open of Extensibility (one of the principles of SOLID - Open-Close) because basically, this code doesn’t need to change anymore do accommodate any future change in the software, but it can be extended.

Conclusion

Hope this can show all the flexibility, extensibility, and power of the .Net framework features.

As a role of thumb for me, if I’m doing something that looks excessively difficult, is resulting in a code that is becoming scary to change in the future, or you feel that is too much work for an apparently small thing this usually is because there’s a simpler way for doing it you just need to study the framework.

References

Was this useful to you? I’m keen to hear your thoughts.