Csharp 基于C#方法的EF核心过滤查询(缓冲与流)

Scenario个

我有这个代码(简化):

var query = _context.Products
                    .Where(x => x.IsActive)
                    .Select(x => new ProductDto 
                                 {
                                     Id = x.Id,
                                     Name = x.Name,
                                     Description = GetDescription(x)
                                 });

// Building predicates (using LinqKit)
var predicate = PredicateBuilder.New<T>();

query = query.Where(predicate);

var count = await query.CountAsync();
var list = await query.ToListAsync();

当我按ID和姓名进行过滤时，它会起作用.当我想按Description进行筛选时，问题就出现了，例如:

query = query.Where(x => x.Description.Contains("winter"));

这里抛出了一个异常--Linq无法将其转换为可理解的SQL.

Question个

你觉得我能处理好吗？这就是我如何构建我的过滤/排序等，我想保持这种状态如何根据使用C#方法但无法转换为SQL的属性进行筛选/排序？

What I tried个

在读完这Efficient Querying EF Core本书后，我有了一个 idea ，也许这是一条可行的道路.

我试着这样做:

var predicate = PredicateBuilder.New<T>();

var enumerable = query.AsEnumerable();

enumerable = enumerable.Where(predicate);

// I convert it back to Queryable to use things such as 
// Take() and Skip() hoping it will be translated into SQL
query = enumerable.AsQueryable();

// Here I lost the ability to use "Async"
var count = query.Count();
var list = query.ToList();

看上go 挺管用的.我失go 了在SQL级别上进行过滤的能力(但我可能能够区分在哪个阶段应用哪些过滤器)，但我不确定它是如何工作的.

我想我理解缓冲和流之间的区别--在这里我不会将所有结果加载到内存中--我希望发生流.在判断数据库调用时--只进行一次调用(.Count()和.ToList())，这听起来不错.

这是解决这个问题的有效方法吗？在.AsEnumerable()岁之后，过滤到底是如何工作的？如果Linq既不将GetDescription(x)加载到内存中，也不向数据库发送请求，它如何知道如何处理GetDescription(x)？

// Edit for more context:
I might have simplified too much, sorry about that. My GetDescription method looks like this:

public static string GetDescription(int productType)
{
    switch (productType)
    {
        case 0:
            return ProductTypeEnum.Value1.GetDescription();
        case 1:
            return ProductTypeEnum.Value2.GetDescription();
    }

    return string.Empty;
}

我在查询中这样使用它:

Description  = x.ProductType != null ? GetDescription(x.ProductType) : string.Empty

Post-thoughts after resolving the issue
I misunderstood how queries are translated. The fact misled me, that without filtering by problematic property - everything worked fine. I thought it meant that it is evaluated on the server side and translated into SQL. What was happening was that this property was marked to be evaluated on the client side, once the records were fetched.

如果你的查询不能被转换成SQL -首先要确保查询写得很好，因为它可能不是.