0

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.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
OmniOwl
  • 5,477
  • 17
  • 67
  • 116
  • Show the full exception text and show the query that builds `surveyIds` – Tim Schmelter Feb 23 '17 at 12:10
  • @TimSchmelter Done :) – OmniOwl Feb 23 '17 at 12:12
  • 3
    Sounds like query provider limitation. What is the type of `context`? – Ivan Stoev Feb 23 '17 at 12:16
  • @IvanStoev It is a Sharepoint DataContext – OmniOwl Feb 23 '17 at 12:16
  • Well, I can't test, but the message is clear - I guess the unsupported expression is `surveyId.Contains()`. You could try manually building `||` predicate for each id in the list. – Ivan Stoev Feb 23 '17 at 12:28
  • 1
    @IvanStoev Yeah I ended up doing something else. I went and made an approximate search instead using Dates (as we want reports within certain weeks at a time). This still leads to a few more reports than what I want but it should never hit the 1000 item limit so it's easier to sort through. Sharepoint really is Sharepain. – OmniOwl Feb 23 '17 at 12:32

1 Answers1

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();
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343