我想要達到的目標：
目前我正在開發一個過濾系統，它將根據用戶輸入對物體應用不同的過濾器。如果您只關注根物體或多對一關系，那將是一件容易的事。但是一旦你想過濾集合，它就會變得有點難以表達，也更難查詢。
問題：
我想過濾根物體（組織），還想過濾諸如 Organization.Contracts 或 Contract.Licenses 之類的集合。我可以選擇向Include和ThenInclude子句添加選擇（參見我的示例）。但我只能添加固定的 LINQ 查詢，但不能構建動態函式來選擇正確的行。
例外：
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"
根據我看到的例外，Entity Framework Core 需要System.Linq.Expressions.Expression 1[System.Func2[ExpressionTreeTest.MinimalTest Contract,System.Boolean]]但我提供了System.Func`2[ExpressionTreeTest.MinimalTest Contract,System .布爾]。一旦我將其更改為Expression<Func<Contract, bool>> ContractFilterExpression = c => c.EndDate > DateTime.Now.AddMonths(11); intellisense 報錯，表示無法接受。
其他遮陽篷：
我發現了許多關于查詢物體框架核心和構建動態查詢的不同問題。基于此，我能夠為根物體（組織）構建動態查詢。但是對于嵌套的 ThenInclude 串列查詢，我找不到任何動態示例。
我給你的最小測驗用例：
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();
}
}
}
筆記：
如果您對我有任何其他提示，甚至是解決方法或其他解決方案，我將非常高興。這樣做不是必需的，但這是我找到的唯一方法。
uj5u.com熱心網友回復：
解決問題的最簡單方法是安裝LINQKit -LinqKit.Microsoft.EntityFrameworkCore
加入WithExpressionExpanding_OnConfiguring
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite(@"Data Source=mydb.db");
optionsBuilder.WithExpressionExpanding();
}
將動態過濾功能設為一個Expression，EF Core 不會轉換Func<>為 SQL 并使用 LINQKit 擴展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擴展將LambdaExpression ContractFilterFunction在 EF Core 的 LINQ 轉換器處理之前擴展并注入最終的運算式樹。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/532868.html
標籤：C＃林克实体框架核心
上一篇：只轉發一次有狀態IEnumerable<T>到IEnumerable<IEnumerable<T>>沒有臨時存盤？