本地序列不能在LINQ to SQL实现中使用 [英] Local sequence cannot be used in LINQ to SQL implementation

问题描述

当我尝试生成类MappedItem的列表时遇到错误，请参见下文.简而言之，下面的代码示例尝试按类别，日期范围和SKU查找产品.我的要求是用户应该能够输入以逗号分隔的SKU列表，并且搜索是要查找其SKU以用户输入的SKU之一开头的任何产品.运行代码时，我得到了.

I'm getting an error, see below, when I try to generate a list of the class MappedItem. In short the code example below tries to find products by category, date range and SKU. The requirement I have is that the user should be able to enter a comma separated list of SKUs and the search is to find any product whos SKU starts with one of the SKUs entered by the user. When I run the code, I get.

除Contains()运算符外，不能在LINQ to SQL实现中使用本地序列.

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

缩写顺序为:

将逗号分隔的SKU字符串转换为字符串列表.

Convert the comma separated string of SKUs into a list of strings.

string sku = TextSKU.Text;
List<string> skuList = sku.Split(new char[] { ',' }).ToList();

在代码的其他位置定义将接受搜索结果的类.

Define elsewhere in the code the class that will accept the search results.

public class MappedItem
{
    public string ItemDescription { get; set; }
    public int ItemCount { get; set; }

    public MappedItem()
    {

    }

    public MappedItem(string itemDescription, int itemCount)
    {
        ItemDescription = itemDescription;
        ItemCount = itemCount;
    }
}

这是我从

List<MappedItem> widgetItems = (from c1 in db.CCRCodes
                                join pac in db.widgetAssignedCodes on c1.code_id equals pac.code_id
                                join ph in db.widgetHistories on pac.history_id equals ph.history_id
                                where ph.contact_dt.Value.Date >= startDate && ph.contact_dt.Value.Date <= endDate &&
                                    (string.IsNullOrEmpty(baanCatFam) || ph.baan_cat_family_code == baanCatFam) &&
                                    (string.IsNullOrEmpty(baanCat) || ph.baan_cat_code == baanCat) &&
                                    (string.IsNullOrEmpty(baanSubCat) || (ph.baan_sub_cat_code == baanSubCat)) &&
                                    (string.IsNullOrEmpty(sku) || skuList.All(sl => ph.product_mod.StartsWith(sl)))
                                group c1 by c1.code_desc into ct
                                select new MappedItem
                                {
                                    ItemDescription = ct.Key.ToUpper(),
                                    ItemCount = ct.Count()
                                }).OrderByDescending(m => m.ItemCount)
                                .ToList();

我相信罪魁祸首是我提取并显示在下面的代码行.

I believe that the culprit is the line of code that I've extracted and displayed below.

skuList.All(sl => ph.product_mod.StartsWith(sl))

这标识了所有以skuList中的元素开头的skus，这些元素是由用户输入的逗号分隔的skus列表派生的.我的问题是，是什么导致此错误，并给出了代码示例，我该如何解决这些问题.

This identifies all skus that start with an element from the skuList which is derived from a comma delimited lists of skus entered by the user. My question is, what causes this error, and given the code examples, what do I do to get around them.

推荐答案

首先-从逻辑上讲，您需要任何，而不是全部.

First - logically you want Any, not All.

第二，这是构建查询过滤器的糟糕方法.所有这些操作都发送到数据库中，而确定应该应用哪些过滤器的信息已经在本地.显式联接也很糟糕(可以使用关联属性代替).

Second, this is a poor way to build up a query filter. All of those operations are sent into the database, while the information to determine which filters should be applied is already local. The explicit joins are also bad (association properties could be used instead).

IQueryable<WidgetHistory> query =  db.widgetHistories
  .Where(ph => ph.contact_dt.Value.Date >= startDate
    && ph.contact_dt.Value.Date <= endDate);

if (!string.IsNullOrEmpty(baanCatFam))
{
  query = query.Where(ph => ph.baan_cat_family_code == baanCatFam);
}
if (!string.IsNullOrEmpty(baanCat))
{
  query = query.Where(ph => ph.baan_cat_code == baanCat);
}
if (!string.IsNullOrEmpty(baanSubCat))
{
  query = query.Where(ph => ph.baan_sub_cat_code == baanSubCat);
}

//TODO sku filtering here.

List<MappedItem> widgetItems =
  from ph in query
  let c1 = ph.widgetAssignedCode.CCRCode
  group c1 by c1.code_desc into g
  select new MappedItem
  {
    ItemDescription = g.Key.ToUpper(),
    ItemCount = g.Count()
  }).OrderByDescending(m => m.ItemCount)
  .ToList();

第三:您问题的答案.

导致此错误的原因

what causes this error

LinqToSql的查询提供程序无法将本地集合转换为sql.只能在有限的情况下进行转换... .Where(ph => idList.Contains(ph.Id))转换为idList中每个int带有1个参数的IN子句.

LinqToSql's query provider cannot translate your local collection into sql. There's only a limitted set of scenarios where it can translate... .Where(ph => idList.Contains(ph.Id)) is translated into an IN clause with 1 parameter per int in idList.

要解决此限制，您需要将本地集合转换为表达式.首先将集合中的每个项目转换为过滤表达式:

To get around this limitation, you need to convert the local collection into an expression. Start by tranforming each item in the collection into a filtering expression:

List<Expression<Func<WidgetHistory, bool>>> skuFilters =
  skuList.Select<string, Expression<Func<WidgetHistory, bool>>>(skuItem =>
    ph => ph.ProductMod.StartsWith(skuItem)
  ).ToList();

接下来，一个辅助方法:

Next, a helper method:

public static Expression<Func<T, bool>> OrTheseFiltersTogether<T>(
  this IEnumerable<Expression<Func<T, bool>>> filters)
{
  Expression<Func<T, bool>> firstFilter = filters.FirstOrDefault();
  if (firstFilter == null)
  {
    Expression<Func<T, bool>> alwaysTrue = x => true;
    return alwaysTrue;
  }
  var body = firstFilter.Body;
  var param = firstFilter.Parameters.ToArray();
  foreach (var nextFilter in filters.Skip(1))
  {
    var nextBody = Expression.Invoke(nextFilter, param);
    body = Expression.OrElse(body, nextBody);
  }
  Expression<Func<T, bool>> result = Expression.Lambda<Func<T, bool>>(body, param);
  return result;
}

现在将它们放在一起:

if (skuFilters.Any())  //this part goes into where it says "TODO"
{
  Expression<Func<WidgetHistory, bool>> theSkuFilter = skuFilters.OrTheseFiltersTogether()
  query = query.Where(theSkuFilter);
}

这篇关于本地序列不能在LINQ to SQL实现中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！