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>
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.