How can I reuse the logic in a .Include.Where() call while working with Entity Framework Core in .NET?

Question

I am writing a database query using LINQ in .NET and I want to be able to not duplicate the code I put in my Where method calls.

I want to return Blogs that have fresh Comments, and want to filter both Blogs and Comments at the same time (so, no blogs without comments, and no old comments)

That requires me to write something like:

ctx.Blogs.Include(blog => blog.Comments.Where(comment => comment.Created < dateTime))
    .Where(blog => blog.Comments.Any(comment => comment.Created < dateTime))
    .Select(b => new BlogEntryDTO(b));

Note, how comment => comment.Created < dateTime) is exactly the same.

(and of course, this is a toy example, real query has a much more complicated filter)

I do the obvious and try to extract the filter as an Expression:

public static IQueryable<BlogEntryDTO> GetBlogsExpression(MyContext ctx, DateTime dateTime)
{
    Expression<Func<Comment, bool>> inTime = comment => comment.Created < dateTime;

    return ctx
        .Blogs.Include(blog => blog.Comments.Where(inTime))
        .Where(blog => blog.Comments.Any(inTime))
        .Select(b => new BlogEntryDTO(b));
    }

But that produces a compile time error:

Cannot resolve method 'Where(Expression<Func<Comment,bool>>)', candidates are:
IEnumerable<Comment> Where<Comment>(this IEnumerable<Comment>, Func<Comment,bool>) (in class Enumerable)
IEnumerable<Comment> Where<Comment>(this IEnumerable<Comment>, Func<Comment,int,bool>) (in class Enumerable)

This sounds like it wants Func, not expression, so I try that:

public static IQueryable<BlogEntryDTO> GetBlogsFunction(MyContext ctx, DateTime dateTime)
{
    Func<Comment, bool> inTime = comment => comment.Created < dateTime;

    return ctx
        .Blogs.Include(blog => blog.Comments.Where(inTime))
        .Where(blog => blog.Comments.Any(inTime))
        .Select(b => new BlogEntryDTO(b));
}

That compiles, but produces a run time error:

Unhandled exception.
ArgumentException: Expression of type 'Func`[Comment,Boolean]' cannot be used for parameter of type 'Expression`[Func`[Comment,Boolean]]' of method 'IQueryable`[Comment] Where[Comment](IQueryable`1[Comment], Expression`1[Func`2[Comment,Boolean]])' (Parameter 'arg1')
    at Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
    ...

Not a surprise, it basically doesn't know how to convert Func to SQL.

And after this I'm stuck.

This is mostly a duplicate of How can I reuse the logic in a Where call while working with Entity Framework Core in .NET?, but I've been asked in the comments to re-post with my own failing query, so here we go.

Full runnable code example:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System.Text.Json;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApp1;

internal class Program
{
    private static void Main(string[] args)
    {
        using var ctx = MyContext.MakeInMemoryContext();

        var blogs = GetBlogsInlined(ctx, DateTime.Today).ToList();
        Console.WriteLine(JsonSerializer.Serialize(blogs));
        var blogs2 = GetBlogsExpression(ctx, DateTime.Today).ToList();
        Console.WriteLine(JsonSerializer.Serialize(blogs2));
        var blogs3 = GetBlogsFunction(ctx, DateTime.Today).ToList();
        Console.WriteLine(JsonSerializer.Serialize(blogs3));
    }

    public static IQueryable<BlogEntryDTO> GetBlogsInlined(MyContext ctx, DateTime dateTime)
    {
        return ctx
            .Blogs.Include(blog => blog.Comments.Where(comment => comment.Created < dateTime))
            .Where(blog => blog.Comments.Any(comment => comment.Created < dateTime))
            .Select(b => new BlogEntryDTO(b));
    }

    // Compile time error:
    // Cannot resolve method 'Where(Expression<Func<Comment,bool>>)', candidates are:
    // IEnumerable<Comment> Where<Comment>(this IEnumerable<Comment>, Func<Comment,bool>) (in class Enumerable)
    // IEnumerable<Comment> Where<Comment>(this IEnumerable<Comment>, Func<Comment,int,bool>) (in class Enumerable)
    public static IQueryable<BlogEntryDTO> GetBlogsExpression(MyContext ctx, DateTime dateTime)
    {
        Expression<Func<Comment, bool>> inTime = comment => comment.Created < dateTime;

        return ctx
            .Blogs.Include(blog => blog.Comments.Where(inTime))
            .Where(blog => blog.Comments.Any(inTime))
            .Select(b => new BlogEntryDTO(b));
    }

    // Runtime error:
    // Unhandled exception.
    // ArgumentException: Expression of type 'Func`[Comment,Boolean]' cannot be used for parameter of type 'Expression`[Func`[Comment,Boolean]]' of method 'IQueryable`[Comment] Where[Comment](IQueryable`1[Comment], Expression`1[Func`2[Comment,Boolean]])' (Parameter 'arg1')
    // at Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)

    public static IQueryable<BlogEntryDTO> GetBlogsFunction(MyContext ctx, DateTime dateTime)
    {
        Func<Comment, bool> inTime = comment => comment.Created < dateTime;

        return ctx
            .Blogs.Include(blog => blog.Comments.Where(inTime))
            .Where(blog => blog.Comments.Any(inTime))
            .Select(b => new BlogEntryDTO(b));
    }

    public class MyContext(DbContextOptions<MyContext> options) : DbContext(options)
    {
        public DbSet<BlogEntry> Blogs { get; set; }
        public DbSet<Comment> Comments { get; set; }

        public static MyContext MakeInMemoryContext()
        {
            var builder = new DbContextOptionsBuilder<MyContext>().UseInMemoryDatabase("context");
            var ctx = new MyContext(builder.Options);
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();
            ctx.SetupBlogs();
            return ctx;
        }

        private void SetupBlogs()
        {
            Blogs.AddRange(
                [
                    new BlogEntry
                    {
                        Name = "1",
                        Created = DateTime.Now.AddDays(-3),
                        Comments =
                        [
                            new Comment { Content = "c1", Created = DateTime.Now.AddDays(-2) },
                            new Comment { Content = "c2", Created = DateTime.Now.AddDays(-1) }
                        ]
                    },
                    new BlogEntry
                    {
                        Name = "2",
                        Created = DateTime.Now.AddDays(-2),
                        Comments = [new Comment { Content = "c3", Created = DateTime.Now.AddDays(-1) }]
                    },
                    new BlogEntry { Name = "2", Created = DateTime.Now.AddDays(-1), Comments = [] }
                ]
            );
            SaveChanges();
        }
    }

    public class BlogEntry
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime Created { get; set; }
        public virtual ICollection<Comment> Comments { get; set; }
    }

    public class Comment
    {
        [Key]
        public int Id { get; set; }
        public string Content { get; set; }
        public DateTime Created { get; set; }
        public int BlogEntryId { get; set; }
        public virtual BlogEntry BlogEntry { get; set; }
    }

    public class BlogEntryDTO(BlogEntry blogEntry)
    {
        public int Id { get; set; } = blogEntry.Id;
        public string Name { get; set; } = blogEntry.Name;
        public string[] Comments { get; set; } = blogEntry.Comments.Select(c => c.Content).ToArray();
    }
}

.csproj:

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net8.0</TargetFramework>
        <LangVersion>latest</LangVersion>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.6" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="8.0.6" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="8.0.6" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="8.0.6">
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
            <PrivateAssets>all</PrivateAssets>
        </PackageReference>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.6">
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
            <PrivateAssets>all</PrivateAssets>
        </PackageReference>
    </ItemGroup>
</Project>

Every LINQ operation accepts returns a new query. You can extract every part you want in a separate method, where actual or inner, and compose the methods you want just like LINQ itself does. The only quirk is that Blogs is a DbSet<Blog>, not an IQueryable<Blog>. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2024 at 8:48
it wants Func, not expression it's the opposite. EF converts LINQ queries to SQL. There's no way to convert a delegate to SQL, only expressions. The first error complains about an ambiguous function call, not about the use of Expression — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2024 at 8:51
@vc74 that's the EF Classic namespace. The question is about EF Core, as the PackageReference shows — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2024 at 9:22
Why are you trying to filter the already filtered comments? This matters, because the error complains about IEnumerable<Comment> Where<Comment>. Comments doesn't implement IQueryable<> and the compiler doesn't know what the contents of inTime are. It only knows about the types, and the Where you use is applied to an ICollection<T>. That's the very problem solved by LinqKit. If you install LinqKit you may be able to use .Blogs.Include(blog => blog.Comments.Where(inTime.Expand()) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 21, 2024 at 9:37

Steve Py · Accepted Answer · 2024-06-24 00:29:26Z

1

Sorry for the late reply, I had earmarked this to have a look at and test with my original test scenario but got distracted.

Should be do-able. The key issue is when inside the Linq expressions it will "see" the .Comments as IEnumerable rather than IQueryable so you need to "guide" the compiler to expect and interpret as IQueryable. For example:

Expression<Func<Comment, bool>> commentExpr = comment => comment.Created < dateTime;
Expression<Func<Blog, bool>> blogExpr = blog => blog.Comments.AsQueryable().Any(commentExpr);

... then:

return ctx
    .Blogs.Include(blog => blog.Comments.Where(commentExpr))
    .Where(blogExpr)
    .Select(b => new BlogEntryDTO(b));

Alternatively you can do away with blogExpr and just inline:

    .Where(blog => blog.Comments.AsQueryable().Any(commentExpr))

The missing bit is to append the AsQueryable() to blog.Comments to keep the compiler happy.

answered Jun 24, 2024 at 0:29

Steve Py

36.3k4 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MonstraG Over a year ago

Revisited this when looking at another piece of code, and just adding AsQuerable() worked for me! I'll mark this as the answer.

dbc · Accepted Answer · 2024-06-23 18:02:51Z

An expression tree represents "code in a tree-like data structure". Unlike with delegates (which represent compiled methods), there are no operators built into the language or runtime to combine, compose, curry or uncurry expression trees, so if you would like to include your inTime expression inside some other expressions you will need to do so manually by creating modified expression trees.

Answers to the question Can I reuse code for selecting a custom DTO object for a child property with EF Core? show several tools that allow for expression tree editing, including the built-in type ExpressionVisitor. So if you want to inject your inTime expression into some more complex expressions, first create the following extension methods (adapted from here):

public static partial class ExpressionExtensions
{
    public static Expression<Func<T1, TResult>> InjectInto<T1, T2, T3, TResult>(this Expression<Func<T2, T3>> inner, Expression<Func<T1, Func<T2, T3>, TResult>> outer) =>
        outer.Inject(inner);

    // Uncurry and compose an Expression<Func<T1, Func<T2, T3>, TResult>> into an Expression<Func<T1, TResult>> by composing with an Expression<Func<T2, T3>>
    public static Expression<Func<T1, TResult>> Inject<T1, T2, T3, TResult>(this Expression<Func<T1, Func<T2, T3>, TResult>> outer, Expression<Func<T2, T3>> inner) =>
        Expression.Lambda<Func<T1, TResult>>(
            new InvokeReplacer((outer.Parameters[1], inner)).Visit(outer.Body), 
            false, outer.Parameters[0]);
}

class InvokeReplacer : ExpressionVisitor
{
    // Replace an Invoke() with the body of a lambda, replacing the formal paramaters of the lambda with the arguments of the invoke.
    // TODO: Handle replacing of functions that are not invoked but just passed as parameters to some external method, e.g.
    //   collection.Select(map) instead of collection.Select(i => map(i))
    readonly Dictionary<Expression, LambdaExpression> funcsToReplace;
    public InvokeReplacer(params (Expression func, LambdaExpression replacement) [] funcsToReplace) =>
        this.funcsToReplace = funcsToReplace.ToDictionary(p => p.func, p => p.replacement);
    protected override Expression VisitInvocation(InvocationExpression invoke) => 
        funcsToReplace.TryGetValue(invoke.Expression, out var lambda)
        ? (invoke.Arguments.Count != lambda.Parameters.Count
            ? throw new InvalidOperationException("Wrong number of arguments")
            : new ParameterReplacer(lambda.Parameters.Zip(invoke.Arguments)).Visit(lambda.Body))
        : base.VisitInvocation(invoke);
}   

class ParameterReplacer : ExpressionVisitor
{
    // Replace formal parameters (e.g. of a lambda body) with some containing expression in scope.
    readonly Dictionary<ParameterExpression, Expression> parametersToReplace;
    public ParameterReplacer(params (ParameterExpression parameter, Expression replacement) [] parametersToReplace) 
        : this(parametersToReplace.AsEnumerable()) { }
    public ParameterReplacer(IEnumerable<(ParameterExpression parameter, Expression replacement)> parametersToReplace) =>
        this.parametersToReplace = parametersToReplace.ToDictionary(p => p.parameter, p => p.replacement);
    protected override Expression VisitParameter(ParameterExpression p) => 
        parametersToReplace.TryGetValue(p, out var e) ? e : base.VisitParameter(p);
}

And now you can rewrite your GetBlogsExpression() method as follows:

public static IQueryable<BlogEntryDTO> GetBlogsExpression(MyContext ctx, DateTime dateTime)
{
    Expression<Func<Comment, bool>> inTime = comment => comment.Created < dateTime;

    return ctx
        .Blogs
        .Include(inTime.InjectInto((BlogEntry b, Func<Comment, bool> f) => b.Comments.Where(c => f(c)))) // Injecting inTime into b.Comments.Where(f) is not implemented so use c => f(c) instead
        .Where(inTime.InjectInto((BlogEntry b, Func<Comment, bool> f) => b.Comments.Any(c => f(c))))
        .Select(b => new BlogEntryDTO(b));
}

Notes:

Your GetBlogsFunction() fails because EF Core does not have the ability to translate arbitrary Invoke() calls inside an expression (specifically the invocation of intime(Comment c)) into queries to the underlying database engine.
Your GetBlogsExpression() fails to compile because, as mentioned previously, there is no built-in ability to compose expressions. When you tried to do so it caused type inferencing to go awry, eventually resulting in confusing compiler messages.

Demo fiddle here.

Collectives™ on Stack Overflow

How can I reuse the logic in a .Include.Where() call while working with Entity Framework Core in .NET?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related