EntityFramework Core - Composite key filtering

In EF Core, you can easily filter to a list of known values, by using LINQ like the following:

int[] ids = { 1, 3, 7 };
myContext.Posts.Where(s => ids.Contains(s.Id))

But what to do, when the list of values you want to filter consists of two or more values each, like the below example where we'd want to filter by a Type and a Name:

var filterBy = new[] { ( PostTypes.Blog, "First"), (PostTypes.KnowledgeBase, "Last") }
myContext.Posts.Where(s => filterBy.Contains(s.Type .... s.Name ?))

The StackOverflow post here has a number of options and workarounds, where I'll focus on Option 6 - which is constructing an Expression which leads to the actual desired SQL in the end, which I imagine is something like this:

SELECT * FROM Posts WHERE (Type = 1 AND Name = 'First') OR (Type = 2 AND Name = 'Last') OR (...)

It's all expressions

The code is available here.

It is possible to programmatically construct arbitrarily complex expressions, like the ones C# makes for you when you create Lambdas. In reality, a piece of code like context.Posts.Where(s => s.Id = 1) isn't actually a piece of C# code, but a lambda of type Expression<Func<BlogPost, bool>>. The Expression<> part makes .NET know that this should be an expression tree, and not code that is compiled into IL.

We can also create Expression<>'s, by constructing them using the very handy Expression class which has methods like Expression.Constant(), Expression.Property(..) and so on.

Let's try replicating the following:

context.Posts.Where(post => 
    (post.Author == "Mike" && post.Title == "Intro to mysql") || 
    (post.Author == "Mike" && post.Title == "How the world works"))

We start by defining our argument post and the two properties on that argument: Author and Title.

ParameterExpression postArgument = Expression.Parameter(typeof(BlogPost), "post");

MemberExpression propertyAuthor = Expression.Property(postArgument, nameof(BlogPost.Author));
MemberExpression propertyTitle = Expression.Property(postArgument, nameof(BlogPost.Title));

Now, we have the elements needed to make the rest of the expression. Lets create a an expression that does two comparisons, and a boolean AND, like post.Author == "Mike" && post.Title == "Intro to mysql":

// Create two constants as needed
ConstantExpression constantAuthor = Expression.Constant("Mike");
ConstantExpression constantTitle = Expression.Constant("Intro to mysql");

// Expression: post.Author = 'Mike' && post.Title = 'Intro to mysql'
BinaryExpression andExpr = Expression.AndAlso(
    Expression.Equal(propertyAuthor, constantAuthor),
    Expression.Equal(propertyTitle, constantTitle));

We can now join this binary expression with other expressions, in new Binary Expressions. We can use this to string together many individual AND's with new OR's.

// Assume we have created many AND expressions
BinaryExpression orExpression = Expression.OrElse(firstAnd, secondAnd);

// Chain the previous OR together with more expressions
BinaryExpression orExpression = Expression.OrElse(orExpr, thirdAnd);
BinaryExpression orExpression = Expression.OrElse(orExpr, fourthAnd);

And in the end, the orExpression can be provided to a .Where() call on an IQueryable<BlogPost>. But we need to transform it a bit first - we need to let the expression tree know exactly how to pass values to our post argument, the one we created in the beginning. We can do this by wrapping our expression in a LambdaExpression, which Expression.Lambda() conveniently creates for us.

// Define the target type, which is a `Func<BlogPost, bool>`
Type delegateFunc = typeof(Func<,>).MakeGenericType(typeof(BlogPost), typeof(bool));

// Pass this, our 'orExpression' and our `postArgument` to create a new LambdaExpression
Expression<Func<BlogPost, bool>> lambda = (Expression<Func<BlogPost, bool>>)Expression.Lambda(delegateFunc, orExpression, postArgument);

With this part done, we now have an expression that wraps many individual AND's. This can be use in our query:


Be sure to check out the working code example for this post at my Github. It also prints out the actual SQL being run so you can verify what's being translated behind the scenes.

Michael Bisbjerg

Michael Bisbjerg