我试图创建一个基于筛选器返回目录的服务。
在网络上看到了一些结果,但并没有解决我的问题。希望你能帮助我。
问题是,这个查询生成器无法转换为存储表达式:
'LINQ to Entities不识别'System.Linq.IQueryable`1[App.Data.Models.Subgroup] HasProductsWithState [Subgroup](System.Linq.IQueryable`1[App.Data.Models.Subgroup], System.Nullable`1[System.Boolean])'方法,此方法无法转换为存储表达式。'
如何使查询能够转换为存储表达式,请不要建议使用.ToList()
,因为我不想在内存中运行它。
所以我现在有的是:
bool? isActive = null;
string search = null;
DbSet<Maingroup> query = context.Set<Maingroup>();
var result = query.AsQueryable()
.HasProductsWithState(isActive)
.HasChildrenWithName(search)
.OrderBy(x => x.SortOrder)
.Select(x => new CatalogViewModel.MaingroupViewModel()
{
Maingroup = x,
Subgroups = x.Subgroups.AsQueryable()
.HasProductsWithState(isActive)
.HasChildrenWithName(search)
.OrderBy(y => y.SortOrder)
.Select(y => new CatalogViewModel.SubgroupViewModel()
{
Subgroup = y,
Products = y.Products.AsQueryable()
.HasProductsWithState(isActive)
.HasChildrenWithName(search)
.OrderBy(z => z.SortOrder)
.Select(z => new CatalogViewModel.ProductViewModel()
{
Product = z
})
})
});
return new CatalogViewModel() { Maingroups = await result.ToListAsync() };
在下面的代码中,你可以看到我递归调用扩展程序来尝试堆叠表达式。但当我在运行时遍历我的代码时,它没有再次进入函数。
return maingroups.Where(x => x.Subgroups.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>;
被称为。
public static class ProductServiceExtensions
{
public static IQueryable<TEntity> HasProductsWithState<TEntity>(this IQueryable<TEntity> source, bool? state)
{
if (source is IQueryable<Maingroup> maingroups)
{
return maingroups.Where(x => x.Subgroups.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>;
}
else if (source is IQueryable<Subgroup> subgroups)
{
return subgroups.Where(x => x.Products.AsQueryable().HasProductsWithState(state).Any()) as IQueryable<TEntity>;
}
else if (source is IQueryable<Product> products)
{
return products.Where(x => x.IsActive == state) as IQueryable<TEntity>;
}
return source;
}
public static IQueryable<TEntity> HasChildrenWithName<TEntity>(this IQueryable<TEntity> source, string search)
{
if (source is IQueryable<Maingroup> maingroups)
{
return maingroups.Where(x => search == null || x.Name.ToLower().Contains(search) || x.Subgroups.AsQueryable().HasChildrenWithName(search).Any()) as IQueryable<TEntity>;
}
else if (source is IQueryable<Subgroup> subgroups)
{
return subgroups.Where(x => search == null || x.Name.ToLower().Contains(search) || x.Products.AsQueryable().HasChildrenWithName(search).Any()) as IQueryable<TEntity>;
}
else if (source is IQueryable<Product> products)
{
return products.Where(x => search == null || x.Name.ToLower().Contains(search)) as IQueryable<TEntity>;
}
return source;
}
}
更新
缺失的类:
public class Maingroup
{
public long Id { get; set; }
public string Name { get; set; }
...
public virtual ICollection<Subgroup> Subgroups { get; set; }
}
public class Subgroup
{
public long Id { get; set; }
public string Name { get; set; }
public long MaingroupId { get; set; }
public virtual Maingroup Maingroup { get; set; }
...
public virtual ICollection<Product> Products { get; set; }
}
public class Product
{
public long Id { get; set; }
public string Name { get; set; }
public long SubgroupId { get; set; }
public virtual Subgroup Subgroup { get; set; }
...
public bool IsActive { get; set; }
}