Linq Intersect(...).Any() inside of a Where clause throws NotSupportedException

Question

Whenever the tags argument is not empty I get a NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

[WebMethod]
public static object GetAnswersForSurvey(string surveyName, int? surveyYear, IEnumerable<string> tags, IEnumerable<string> benchmarks)
{
    IQueryable<DAL.Answer> results = new DataClassesDataContext().Answers
                                            .OrderBy(a => a.Question.Variable);

    if (!String.IsNullOrEmpty(surveyName)) results = results.Where(a => a.Survey.Name == surveyName);
    if (surveyYear.HasValue) results = results.Where(a => a.Survey.Year == surveyYear.Value);
    if (tags.Any()) results = results.Where(answer => answer.Question.Tags.Select(t => t.Label).Intersect(tags).Any());
    if (benchmarks.Any()) results = results.Where(answer => benchmarks.Contains(answer.Question.BenchmarkCode));

    return results.Select(a => new {
        a.Question.Wording,
        a.Demographic,
        Benchmark = a.Question.BenchmarkCode,
        a.Question.Scale,
        a.Mean,
        a.MEPMean,
        a.NSSEMean
    });
}

I understand it may not be possible to do it the way I'm trying. If it is impossible, can anyone offer any alternatives?

Answer 1

A number of the general purpose Linq to Object methods are not support within Linq to SQL.

http://msdn.microsoft.com/en-us/library/bb399342.aspx

An alternative might be to complete several sub-queries against sql and then perform your intersection operations as Linq to Objects

Answer 2

I think the reason is that the implementation of System.Data.Linq.Table.Intersect returns an IEnumerable, which in turn does not implement a parameterless version of Any().