NotSupportedException when using Generic Operation in LINQ

Question

At work I have to make a console application which can contact a WCF service at Sharepoint, pull out some data from a list and compare it to some data from our Microsoft SQL Server Database.

Now, I almost got it down but I have run into some issues when trying to pull out some values that I need. I need two different values that are put in the same object representation. To do this I have to make the same query twice but on two different Title field criteria:

context.Values.Where(i => i.Title.Equals("Flight number:") && surveyIds.Contains(i.Survey.Id) == true).ToList();
context.Values.Where(i => i.Title.Equals("Date") && surveyIds.Contains(i.Survey.Id) == true).ToList();

The problem is that I can't call this portion of the code without getting the NotSupportedException

surveyIds.Contains(i.Survey.Id)

The expression (([10007].Title == "Flight number:") And (value(System.Collections.Generic.List`1[System.Int32]).Contains([10007].Survey.Id) == True)) is not supported.

Further up in the code I've made another list called surveyIds which is full of integers and to limit the list search that I do, I wanted to compare the Survey attached to the Value's ID (since it's a lookup) with the ones in my surveyIds list.

List<FlightSurveysDataContext.SurveysItem> reports = context.Surveys.Where(i => i.Title.Equals("Quality report - Cleaning") && i.UploadComplete == true).ToList();
List<int> surveyIds = new List<int>();
foreach (SurveysItem item in reports) { surveyIds.Add(item.Id); }

Can I do this in some other fashion? The reason I wanna do it in one go is that if I don't limit the search, the Collection will only get the first 1,000 values it finds that matches the title and the Sharepoint list have a little over 200,000 items currently so I am sure to get items I don't want.

Answer 1

As I mentioned in the comments, looks like the SharePoint LINQ query provider does not support constant Contains expression.

You can try replacing it with the equivalent || based condition build with the following helper:

public static partial class QueryableExtensions
{
    public static IQueryable<T> WhereIn<T, V>(this IQueryable<T> source, Expression<Func<T, V>> valueSelector, IEnumerable<V> values)
    {
        var condition = values
            .Select(value => Expression.Equal(valueSelector.Body, Expression.Constant(value)))
            .DefaultIfEmpty()
            .Aggregate(Expression.OrElse);
        if (condition == null) return source;
        var predicate = Expression.Lambda<Func<T, bool>>(condition, valueSelector.Parameters);
        return source.Where(predicate);
    }
}

The usage would be something like:

var result = context.Values
    .Where(i => i.Title.Equals("Flight number:"))
    .WhereIn(i => i.Survey.Id, surveyIds)
    .ToList();