Using LINQ expressions to build dynamic queries in Entity Framework

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 to try it out yourself. 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:

// AndOrExpression.cs
using RoundTheCode.LinqExpressions_Example.Data;
using RoundTheCode.LinqExpressions_Example.Data.Models.Types;
using System;
using System.Linq;
using System.Linq.Expressions;
 
namespace RoundTheCode.LinqExpressions_Example.Expressions
{
	public partial class AndOrExpression : ExpressionBase<FilmTimeEntity>
	{
		public override IQueryable<FilmTimeEntity> GetExampleQuery(LinqExpressionsDbContext context)
		{
			var expression = GetExpression(context);

			// Store the filter as a dynamic query.
			return context.FilmTimeEntities.Where(expression);
		}

		protected override Expression<Func<FilmTimeEntity, bool>> GetExpression(LinqExpressionsDbContext context)
		{
			var beginTime = DateTimeOffset.Parse("2019-08-03 12:00");
			var endTime = DateTimeOffset.Parse("2019-08-04 11:00");
			// We want to build the expression for the "Where" clause

			// Example of Query we will eventually run.
			// context.FilmTimeEntities.Where(ft => ft.FilmId == 3 && (ft.StartTime < beginTime || ft.StartTime >= endTime));

			// Time to build up the clause in the ANY field
			var ftParameter = Expression.Parameter(typeof(FilmTimeEntity), "ft"); // ft =>

			var ftIdProperty = Expression.Property(ftParameter, "FilmId"); // ft.FilmId
			var ftIdClause = Expression.Equal(ftIdProperty, Expression.Constant(3)); // ft.FilmId == 3

			// Begin the OrElse statement
			var ftStartTimeProperty = Expression.Property(ftParameter, "StartTime"); // ft.StartTime
			var ftStartTimeFirstClause = Expression.LessThan(ftStartTimeProperty, Expression.Constant(beginTime)); // ft.StartTime < beginTime
			var ftStartTimeSecondClause = Expression.GreaterThanOrEqual(ftStartTimeProperty, Expression.Constant(endTime)); // ft.StartTime >= endTime

			// Or statement
			var ftOrElseClause = Expression.OrElse(ftStartTimeFirstClause, ftStartTimeSecondClause); // (ft.StartTime < beginTime || ft.StartTime >= endTime)

			// AndAlso statement 
			var ftAndClause = Expression.AndAlso(ftIdClause, ftOrElseClause); // ft.FilmId == 3 && (ft.StartTime < beginTime || ft.StartTime >= endTime)

			// Lambda Expression
			return Expression.Lambda<Func<FilmTimeEntity, bool>>(ftAndClause, ftParameter);

		}
	}
}

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:

// AnyExpression.cs
using RoundTheCode.LinqExpressions_Example.Data;
using RoundTheCode.LinqExpressions_Example.Data.Models.Types;
using System;
using System.Linq;
using System.Linq.Expressions;
 
namespace RoundTheCode.LinqExpressions_Example.Expressions
{
	public partial class AnyExpression : ExpressionBase<FilmEntity>
	{
		public override IQueryable<FilmEntity> GetExampleQuery(LinqExpressionsDbContext context)
		{
			var expression = GetExpression(context);

			// Store the filter as a dynamic query.
			return context.FilmEntities.Where(expression);
		}

		protected override Expression<Func<FilmEntity, bool>> GetExpression(LinqExpressionsDbContext context)
		{
			var startTime = DateTimeOffset.Parse("2019-08-04");

			// We want to build the expression for the "Where" clause
			 
			// Example of Query we will eventually run.
			// _context.FilmEntities.Where(f => _context.FilmTimeEntities.Any(ft => ft.FilmId == f.Id && ft.StartTime >= startTime));

			var fParameter = Expression.Parameter(typeof(FilmEntity), "f"); // f =>

			// Create the FilmTime Entities Query
			var ftQueryExpression = context.Set<FilmTimeEntity>().AsQueryable().Expression; // _context.FilmTimeEntities
			 
			// Build up the Any part of the query from Film Times
			var ftParameter = Expression.Parameter(typeof(FilmTimeEntity), "ft"); // ft =>
			var ftFilmIdProperty = Expression.Property(ftParameter, "FilmId"); // ft.FilmId
			var ftFilmIdEquals = Expression.Equal(ftFilmIdProperty, Expression.Property(fParameter, "Id")); // ft.FilmId == f.Id

			var ftStartTimeProperty = Expression.Property(ftParameter, "StartTime"); // ft.StartTime
			var ftStartTimeEquals = Expression.GreaterThanOrEqual(ftStartTimeProperty, Expression.Constant(startTime)); // ft.StartTime >= startTime

			// Builds up the AND expression
			var ftFullQueryExpression = Expression.AndAlso(ftFilmIdEquals, ftStartTimeEquals); // ft => ft.FilmId == f.Id && ft.StartTime >= startTime

			// Make the Lambda Expression for Film Time
			var ftLambda = Expression.Lambda<Func<FilmTimeEntity, bool>>(ftFullQueryExpression, ftParameter);

			// Join in an ANY statement
			var anyMethod = typeof(Queryable).GetMethods().FirstOrDefault(method => method.Name == "Any" && method.GetParameters().Count() == 2); // Use reflection to find the ANY method
			var anyFilmTimeMethod = anyMethod.MakeGenericMethod(typeof(FilmTimeEntity)); // Any is a generic method, so create a method specific to FilmTimeEntity

			var anyCall = Expression.Call(anyFilmTimeMethod, ftQueryExpression, ftLambda); // (f => _context.FilmTimeEntities.Any(ft => ft.FilmId == f.Id && ft.StartTime >= startTime)

			return Expression.Lambda<Func<FilmEntity, bool>>(anyCall, fParameter);
		}
	}
}

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:

// IncludeExpression.cs
using RoundTheCode.LinqExpressions_Example.Data;
using RoundTheCode.LinqExpressions_Example.Data.Models.Types;
using System;
using System.Linq;
using System.Linq.Expressions;
 
namespace RoundTheCode.LinqExpressions_Example.Expressions
{
	public partial class IncludeExpression : ExpressionBase<FilmEntity>
	{
		public override IQueryable<FilmEntity> GetExampleQuery(LinqExpressionsDbContext context)
		{
			var expression = GetExpression(context);

			// Store the filter as a dynamic query.
			return context.FilmEntities.Where(expression);
		}

		protected override Expression<Func<FilmEntity, bool>> GetExpression(LinqExpressionsDbContext context)
		{
			var beginTime = DateTimeOffset.Parse("2019-08-03 12:30");
			var endTime = DateTimeOffset.Parse("2019-08-03 16:30");

			// We want to build the expression for the "Where" clause

			// Example of Query we will eventually run.
			// context.FilmEntities.Include("FilmTimes").Where(f.FilmTimes.Any(ft => ft.StartTime >= beginTime && ft.StartTime < endTime));

			var fParameter = Expression.Parameter(typeof(FilmEntity), "f"); // f =>

			// Get Film Times between beginTime and endTime
			var fFilmTimeProperty = Expression.Property(fParameter, "FilmTimes"); // f.FilmTimes

			// Time to build up the clause in the ANY field
			var ftParameter = Expression.Parameter(typeof(FilmTimeEntity), "ft"); // ft =>

			var ftStartTimeProperty = Expression.Property(ftParameter, "StartTime"); // ft.StartTime
			var ftBeginTimeClause = Expression.GreaterThanOrEqual(ftStartTimeProperty, Expression.Constant(beginTime)); // ft.StartTime >= startTime
			var ftEndTimeClause = Expression.LessThan(ftStartTimeProperty, Expression.Constant(endTime)); // ft.StartTime < endTime

			// Builds up the AND expression
			var ftFullQueryExpression = Expression.AndAlso(ftBeginTimeClause, ftEndTimeClause); // ft => ft.StartTime >= beginTime && ft.StartTime < endTime

			// Make the Lambda Expression for Film Time
			var ftLambda = Expression.Lambda<Func<FilmTimeEntity, bool>>(ftFullQueryExpression, ftParameter);

			// Join in an ANY statement
			var anyMethod = typeof(Enumerable).GetMethods().FirstOrDefault(method => method.Name == "Any" && method.GetParameters().Count() == 2); // Use reflection to find the ANY method (We use Enumerable for collections)
			var anyFilmTimeMethod = anyMethod.MakeGenericMethod(typeof(FilmTimeEntity)); // Any is a generic method, so create a method specific to FilmTimeEntity

			var anyCall = Expression.Call(anyFilmTimeMethod, fFilmTimeProperty, ftLambda); // FilmTimes.Any(ft => ft.StartTime >= beginTime && ft.StartTime < endTime)

			// Return the lamba expression for film so we can put in the where clause.
			return Expression.Lambda<Func<FilmEntity, bool>>(anyCall, fParameter);
		}

	}
}

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.