What i want to achieve:
Currently I'm working on a filter system which will based on user input apply different filters on entites. That is an easy task if you just focus on the root entity or on Many-to-one relationships. But as soon as you want to filter on collections it gets a little bit harder to express and also harder to query.
The Problem:
I want to filter on the root entity (Organization) and also want to filter on collections like Organization.Contracts or Contract.Licenses. I have the option to add a selection to the Include and ThenInclude clause (see my example). But i can only add fixed LINQ-Querys but not build dynamic functions to select the right rows.
The Exception:
System.ArgumentException: "Expression of type 'System.Func`2[ExpressionTreeTest.MinimalTest Contract,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[ExpressionTreeTest.MinimalTest Contract,System.Boolean]]' of method 'System.Linq.IQueryable`1[ExpressionTreeTest.MinimalTest Contract] Where[Contract](System.Linq.IQueryable`1[ExpressionTreeTest.MinimalTest Contract], System.Linq.Expressions.Expression`1[System.Func`2[ExpressionTreeTest.MinimalTest Contract,System.Boolean]])' Arg_ParamName_Name"
Based on the exception i saw, that Entity Framework Core wants a System.Linq.Expressions.Expression1[System.Func2[ExpressionTreeTest.MinimalTest Contract,System.Boolean]] but i provide a System.Func`2[ExpressionTreeTest.MinimalTest Contract,System.Boolean]. As soon as i change this to Expression<Func<Contract, bool>> ContractFilterExpression = c => c.EndDate > DateTime.Now.AddMonths(11); intellisense reports an error, that this can not be accepted.
Other awnsers:
I found many different questions about querying entity framework core and building dynamic queries. Based on this I was able to build my dynamic queries for the root entity (Organization). But for the nested ThenInclude list queries i could not find any dynamic example.
My minimal test case for you:
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using System.ComponentModel.DataAnnotations;
using System.Linq.Expressions;
namespace ExpressionTreeTest
{
public class MinimalTest
{
public void RunTest()
{
AppDbContext dbContext = new AppDbContext();
dbContext.Database.EnsureDeleted();
dbContext.Database.EnsureCreated();
// Setup some test data to check the query results
SetupTestData(dbContext);
// Pre build query with entity framework, which is working as expected
// Expectation:
// Get all Organizations with the Name == NameOfCompany and
// Include all Organization.Contracts which are Created > ateTime.Now.AddMonths(12)
// ThenInclude all Contract.Licenses which are l.Articel.Name == "TestArticelName"
IQueryable<Organization> preBuildQuery = dbContext.Organizations.Where(o => o.Name == "NameOfCompany").
Include(c => c.Contracts.Where(c => c.Created > DateTime.Now.AddMonths(12))).
ThenInclude(l => l.Licenses.Where(l => l.Articel.Name == "TestArticelName"));
// This prints 1, which is the desired result
Console.WriteLine("Query result count: " preBuildQuery.ToList().Count());
// This is the dynamic filter funtion for the Include-Part of the query
// This function gets accepted by Visual Studio but throws an error by Entity Framework
Func<Contract, bool> ContractFilterFunction = c => c.EndDate > DateTime.Now.AddMonths(11);
// Build the above query dynamically based on user input
IQueryable<Organization> dynamicQuery = dbContext.Organizations.Where(BuildWhereQuery()).
Include(c => c.Contracts.Where(ContractFilterFunction)).
ThenInclude(l => l.Licenses.Where(l => l.Articel.Name == "TestArticelName"));
// This is the line with the error you will find in the question
// If i remove the ContractFilterFunction and replace it with an inline lambda
// the query gets executed, but i am not able to dynamically set the query parameters.
Console.WriteLine("Query result count: " dynamicQuery.ToList().Count());
}
/// <summary>
/// This method creates based on input a query with different types. In the future there
/// should be some select based on the binaryExpression to use (Equal, Greater, etc.)
/// At the moment this is static for testing purposes
/// </summary>
/// <returns>A Func<T,bool> to parse to a Linq-Entity-Framewor query</returns>
private Expression<Func<Organization, bool>> BuildWhereQuery()
{
ParameterExpression rootEntity = Expression.Parameter(typeof(Organization));
MemberExpression fieldExpression = Expression.PropertyOrField(rootEntity, "Name");
ConstantExpression valueToCompare = Expression.Constant("NameOfCompany");
var binaryExpression = Expression.Equal(fieldExpression, valueToCompare);
return Expression.Lambda<Func<Organization, bool>>(binaryExpression, rootEntity);
}
public class AppDbContext : DbContext
{
public DbSet<Organization> Organizations { get; set; }
public DbSet<Contact> Contacts { get; set; }
public DbSet<Contract> Contracts { get; set; }
public DbSet<License> Licenses { get; set; }
public DbSet<Articel> Articels { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(@"Data Source=mydb.db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
}
public class BasicEntity
{
[Key]
public Guid Id { get; set; }
}
public class Organization : BasicEntity
{
public string Name { get; set; }
public List<Contract> Contracts { get; set; }
public List<Contact> Contacts { get; set; }
}
public class Articel : BasicEntity
{
public string Name { get; set; }
}
public class Contact : BasicEntity
{
public string Name { get; set; }
public Organization Organization { get; set; }
}
public class Contract : BasicEntity
{
public DateTime Created { get; set; }
public DateTime EndDate { get; set; }
public List<License> Licenses { get; set; }
public Organization Organization { get; set; }
}
public class License : BasicEntity
{
public string LicenseNumber { get; set; }
public Articel Articel { get; set; }
public Contract Contract { get; set; }
}
private static void SetupTestData(AppDbContext dbContext)
{
Organization org = new Organization
{
Name = "NameOfCompany",
};
dbContext.Add(org);
dbContext.Add(new Contact
{
Name = "Contact 1",
Organization = org
});
dbContext.Add(new Contact
{
Name = "Contact 2",
Organization = org
});
Articel articel = new Articel
{
Id = Guid.NewGuid(),
Name = "TestArticelName"
};
dbContext.Add(articel);
Contract contract = new Contract
{
Id = Guid.NewGuid(),
Created = DateTime.Now,
EndDate = DateTime.Now.AddMonths(12),
Organization = org
};
dbContext.Add(contract);
License license = new License
{
Id = Guid.NewGuid(),
LicenseNumber = "12345-12345",
Articel = articel,
Contract = contract
};
dbContext.Add(license);
dbContext.SaveChanges();
}
}
}
Note:
If you have any additional tips for me or even a workaround or other solution, I would be very happy. It's not a requirement to do it like this, but it's the only way I found.
CodePudding user response：
Simplest way to solve your issue is to install LINQKit - LinqKit.Microsoft.EntityFrameworkCore
Add WithExpressionExpanding in OnConfiguring
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(@"Data Source=mydb.db");
optionsBuilder.WithExpressionExpanding();
}
Make dynamic filter function as a Expression, EF Core will not translate Func<> to the SQL and use LINQKit extension Invoke:
// This is the dynamic filter function for the Include-Part of the query
Expression<Func<Contract, bool>> ContractFilterFunction = c => c.EndDate > DateTime.Now.AddMonths(11);
IQueryable<Organization> dynamicQuery = dbContext.Organizations.Where(BuildWhereQuery())
.Include(c => c.Contracts.Where(c => ContractFilterFunction.Invoke(c)))
.ThenInclude(l => l.Licenses.Where(l => l.Articel.Name == "TestArticelName"));
LINQKit extension will expand LambdaExpression ContractFilterFunction and inject into final Expression Tree before processing by EF Core's LINQ Translator.