Local Sequence Cannot Be Used in Linq to SQL Implementation of Query Operators Except the Contains() Operator

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

You can't use a Join between a SQL source and a local source. You'll need to bring the SQL data into memory before you can join them. In this case, you're not really doing a join since you only take the elements from the first collection, what you want is a select...where...selectid in query, which you can get using the Contains method.

 var SE = Shop.Sections.Where( s => obj.SectionObjects
                                       .Select( so => so.SectionId )
                                       .Contains( s.SectionId ))
                       .ToList();

Translates to

select * from Sections where sectionId in (...)

where the values for the in clause come from the list of ids in the collection of local objects.

Local sequence cannot be used in LINQ to SQL

The query provider doesn't know how to translate w.ops.Intersect(selecteditems) into a SQL query.

If selecteditems was another query from the same query provider then it may be able to translate them, or if the entire operation were being done in Linq-to-Objects, rather than Linq-to-SQL, then it would be fine.

As per the error message, the only operation it knows how to perform on such an object is Contains. You can re-work your query to use that instead:

.Where(w => selecteditems.Count == 0 || 
    w.ops.Any(op => selecteditems.Contains(op)))

That [should] work.

LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator

Replace the usages of Any with Contains in your query. eg:

searchTerms.Contains(c.Email)

This should get the result you're looking for. It looks backwards, but it's correct- it'll generate an IN operator for each field inside a Contains with all the elements in searchTerms.

The AddressLine1 part won't work this way- you'll have to loop-generate the comparisons yourself with

c.addressLine1.Contains(...)

Something like PredicateBuilder can be helpful for this.

Local sequence cannot be used in LINQ to SQL implementation

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();

Third: the answer to your question.

what causes this error

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;
}

And now putting it all together:

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

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

I basically reversed the join and it worked

var urunMD = from urunStokbilgileri in urunStok
             join urun in db.TBLP1URUNs
                       on urunStokbilgileri.UrunNo equals urun.ID
             join kategori in db.TBLP1URUNKATEGORIs
                       on urun.KATEGORIID equals kategori.ID
             ......

LINQ to SQL exception: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

The error you are getting is trying to direct you to use the .Contains method passing in a simple array. By default it translates that array into an In clause in the format:

Where foo In ("b1", "B2", "B3")

Notice here that you can't do a multi-dimentional array in the In clause (as you would need to do). Since you can't join server side to a local array, your options become limited as long as you have a composite key relationship.

If you don't need to fetch the rows in order to delete them, it will probably be faster anyway to just use Context's ExecuteCommand to issue your deletes. Just make sure to parameterize your query (see http://www.thinqlinq.com/Post.aspx/Title/Does-LINQ-to-SQL-eliminate-the-possibility-of-SQL-Injection)

string deleteQuery = "DELETE FROM Bookings b WHERE b.ReservationCode = {0} AND b.Pren = {1}";
foreach (var bookingType in bookings)
{
    db.ExecuteCommand(deleteQuery, bookingType.ReservationCode, bookingType.Preen);
}

LINQ - Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

SOLVED

Thanks sgmoore for your input - it helped arrive at this solution:

Assgning the IQueryable list to an IEnumerable list, running a ToList on it and THEN using my filters on the list worked great.

IEnumerable<tblItems> temp = items.ToList();

temp = temp.Where(p => p.heading.ToLower().Contains(keywords) || 
                     p.description.ToLower().Contains(keywords));

items = temp.AsQueryable();

Using Intersect I'm getting a Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

Ok, so after more searching more techniques, and trying user1010609's technique, I managed to get it working after an almost complete rewrite. The following code first provides a flat record query with all of the information I am searching, then a new list is formed with the filtered information compared against the search terms (counting the hits of each search term for ordering by relevance). I was careful not to return a list of the flat file so there would be some efficiency in the final database retrieval (during the formation of the filtered List<>). I am positive this is not even close to being an efficient method, but it works. I am eager to see more and unique techniques to solving this type of problem. Thanks!

searchText = searchText.ToUpper();
List<string> searchTerms = searchText.Split(' ').ToList();

var allIssues =
    from iss in DatabaseConnection.CustomerIssues
    join stoi in DatabaseConnection.SolutionToIssues on iss.IssueID equals stoi.IssueID into stoiToiss
    from stTois in stoiToiss.DefaultIfEmpty()
    join solJoin in DatabaseConnection.Solutions on stTois.SolutionID equals solJoin.SolutionID into solutionJoin
    from solution in solutionJoin.DefaultIfEmpty()
    select new IssuesAndSolutions
    {
        IssueID = iss.IssueID,
        IssueDesc = iss.Description,
        SolutionDesc = (solution.Description == null) ? "No Solutions" : solution.Description,
        SolutionID = (solution.SolutionID == null) ? 0 : solution.SolutionID,
        SolutionToIssueID = (stTois.SolutionToIssueID == null) ? 0 : stTois.SolutionToIssueID,
        Successful = (stTois.Successful == null) ? false : stTois.Successful
    };                

    List<IssuesAndSolutions> filteredIssues = new List<IssuesAndSolutions>();

    foreach (var issue in allIssues)
    {
        int hits = 0;
        foreach (var term in searchTerms)
        {
            if (issue.IssueDesc.ToUpper().Contains(term.Trim())) hits++;                        
        }
        if (hits > 0)
        {
             IssuesAndSolutions matchedIssue = new IssuesAndSolutions();
             matchedIssue.IssueID = issue.IssueID;
             matchedIssue.IssueDesc = issue.IssueDesc;
             matchedIssue.SearchHits = hits;
             matchedIssue.CustomerID = issue.CustomerID;
             matchedIssue.AssemblyID = issue.AssemblyID;
             matchedIssue.DateOfIssue = issue.DateOfIssue;
             matchedIssue.DateOfResolution = issue.DateOfResolution;
             matchedIssue.CostOFIssue = issue.CostOFIssue;
             matchedIssue.ProductID = issue.ProductID;
             filteredIssues.Add(matchedIssue);
         }                    
      }

paginatedlist throwing an error Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

You can't use the result of GetUsersInRole() in a join to a database table via LINQ to SQL. You can try this instead:

  Dim usrs = System.Web.Security.Roles.GetUsersInRole(TeamRole)

  Return From task In db.Tasks                      _
         Where usrs.Contains(task.TaskAssignedToID) _
         Order By task.InsertDateTime               _
         Select task

---

Related Topics