Entity Framework is a Object Relational Mapper (ORM) that relates objects and properties in your .NET application with tables and columns in your database. As a result of it's relationship between application and database, it has become widely used in .NET applications.
For the purpose of this article, it is assumed that your database is SQL Server. However, it is possible to support Entity Framework with other database engines like MySQL.
Entity Framework supports many of the clauses that appear in a SQL Server statement, such as WHERE and EXISTS. Because Entity Framework relies on objects from your database, it expects the clauses to contain a Lambda expression.
So what's this Lambda expression? Well it's basically a function that allows you to pass in an object, and write an IF statement based on the properties in that object. Here's an example:
f => f.Id == 3
The problem with Lambda Expressions in code
On most occasions, writing a Lambda expression directly is fine for what you need it to do. The problem comes if you want to write a Lambda expression where you want to use the same Lambda expression, but use different object names, or properties within it. You can't do that with Lambda expressions directly as you are passing in objects and properties, rather than strings.
LINQ expressions to the rescue
There is a way around this and it comes in the form of LINQ expressions. You can keep on building up each part of the LINQ expression bit by bit. And when you are happy, you can convert it into a Lambda expression ready to be used in Entity Framework.
The reason for this guide is that I was looking to use LINQ expressions on a project I was working on. However, there didn't seem like a great tutorial around, so once I got my head around it, I decided to build a sample application to share.
The sample application
You can download the application from this GitHub repository. The examples below are all related to this repository. SQL Server and .NET Core power the application and it contains three examples of building a dynamic query using LINQ expressions.
Each example contains a large number of comments stating the Lambda expression that we wish to produce and what part of the expression is being created, line-by-line.
We have built two database tables that Entity Framework will use to get the data from the database and output to the application. The Film table specifies the name of the film and the FilmTime table specifies the start times of the film. The Film table has a one-to-many relationship with FilmTime table. One film can have many start times.
Both the Film and FilmTime entity have been added to the DbContext in Entity Framework. We will use these entities in the DbContext to produce a query. The query will contain a Where clause and we will create a dynamic Lambda expression in the Where clause.
If you open a class on any .NET project, and import the library "System.Linq.Expressions", you can call the class "Expression" and there are a large number of static methods within this class. It's these methods that we will be using to create our dynamic Lambda expression.
1. The And Or Statement
The whole point of this example is to produce a Lambda expression that contains an AndAlso statement, but also to produce an OrElse statement. It's important to get the brackets in the right way round. Here is the example:
2. The Any Statement
In this example, we are doing a where clause on the Film entity, but we are also producing a subquery where we are query the FilmTime entity from the DbContext. Here is the example:
3. The Include Statement
This example is similar to the any statement, but rather than performing a sub query for the FilmTime entity, as we have included the FilmTime entity as a collection of Film entities, so we are going to supply that in an Include statement. Here is the example:
Should I build dynamic queries?
It's worth noting that building dynamic queries can get confusing very quickly. In my examples above, I've commented the part of the Lambda expression that it's creating. It certainly helped me out.
If you are going to build one, I would recommend adding as many comments as possible, particularly if someone else is likely to review your code.