Local sequence cannot be used in LINQ to SQL implementation

Question

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

The local sequence cannot be used in the implementation of LINQ to SQL query operators, except for the Contains () operator.

Shortened sequence:

Convert a comma-separated string to a list of strings.

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

Define a class elsewhere in the code 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; } } 

Here is the query that I generate from the results

 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 the culprit is a line of code that I extracted and displayed below.

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

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

Answer 1

First, logically you want Any, not All.

Secondly, this is a bad way to create a query filter. All these operations are sent to the database, and the information to determine which filters should be applied is already local. Explicit connections are also bad (you can use association properties 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(); 

Third: the answer to your question.

what causes this error

The LinqToSql query provider cannot translate your local collection into sql. There is only a limited set of scripts where it can translate ... .Where(ph => idList.Contains(ph.Id)) converted to an IN clause with 1 parameter for int in idList.

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

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

Next, the 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; } 

And now all together:

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