c# - Linq to Entities - where 语句抛出 System.NotSupported 异常

Question

   var entity =
   from document in db.Context.DocumentEntity
   join product in db.Context.ProductEntity on document.ProductId equals product.Id
   join partner in db.Context.PartnerEntity on product.PartnerId equals partner.Id
   select new
   {
       document,
       product,
       partner
   } into t1
   where request.PartnerFilter.Contains(t1.partner.Name)
   group t1 by t1.document.Date into rp
   select new
   {
       PartnerName = rp.FirstOrDefault().partner.Name,
       Date = rp.FirstOrDefault().document.Date,
       Income = rp.Sum(x => x.document.Income),
       Click= rp.Sum(x => x.document.Click)
   };

result = ToDataTable(entity.OrderByDescending(d=>d.Date).ToList());


public static DataTable ToDataTable<T>(List<T> items)
{
  DataTable dataTable = new DataTable(typeof(T).Name);

  PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
  foreach (PropertyInfo prop in Props)
  {
    var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
    dataTable.Columns.Add(prop.Name, type);
  }
  foreach (T item in items)
  {
    var values = new object[Props.Length];
    for (int i = 0; i < Props.Length; i++)
    {
        values[i] = Props[i].GetValue(item, null);
    }
    dataTable.Rows.Add(values);
  }
 return dataTable;

问题出在 where 子句上。 request.PartnerFilter 是一个字符串数组，可能为空。我需要检查 partner.Name 是否包含在其中。一种 Sql Where-In。最后 entity.ToList() 抛出 System.NotSupported Exception。我怎样才能完成过滤？

Answer 1

如果要在 EF 查询表达式树中使用 Contains，则需要确保该变量不为 null。您需要在查询之外执行此操作(以及需要应用的条件)。

例如:

var partnerFilter = request.PartnerFilter ?? Enumerable.Empty<string>();
bool applyPartnerFilter = partnerFilter.Any();

var entity =
    ...
    where (!applyPartnerFilter || partnerFilter.Contains(t1.partner.Name))
    ...

但在我看来，在查询之外应用可选过滤器会更好，例如:

var partners = db.Context.PartnerEntity.AsQueryable();
if (request.PartnerFilter != null && request.PartnerFilter.Any())
    partners = partners.Where(partner => request.PartnerFilter.Contains(partner.Name));

var entity =
...
join partner in partners on product.PartnerId equals partner.Id
...

(没有哪里)