Stack overflow in LINQ to SQL and the Contains keyword

Question

I have an Extension method which is supposed to filter a Queryable object (IQueryable) based upon a collection of Ids....

Note that IQueryable is sourced from my database via a LinqToSql request

 public static IQueryable<NewsItemSummary> WithID(this IQueryable<NewsItemSummary> qry, IQueryable<Guid> Ids)
    {
        return from newsItemSummary in qry
               where Ids.Contains(newsItemSummary.ID)
               select newsItemSummary;
    }

If Ids are created from an array or list and passed in as a queryable list, it DOESNT work

For example...

 GetNewsItemSummary().WithID(ids.AsQueryable<Guid>())

If Ids is composed form a LinqToSql request, it DOES work!!

This is known issue: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=355026

My Ids collection cannot come from a LinqToSql request...

Note, if I change the function so that it consumes and IList rather than an IQueryable....

 public static IQueryable<NewsItemSummary> WithID(this IQueryable<NewsItemSummary> qry, IList<Guid> Ids)
    {
        return from newsItemSummary in qry
               where Ids.Contains(newsItemSummary.ID)
               select newsItemSummary;
    }

I now get the following exception:

Method 'Boolean Contains(System.Guid)' has no supported translation to SQL.

So... all I want to do is filter my collection of news based upon a list or array of Guids.... Ideas???

Answer 1

This will translate.

public static IQueryable<NewsItemSummary> WithID(
    this IQueryable<NewsItemSummary> qry,
    List<Guid> Ids
)
    {
        return from newsItemSummary in qry
               where Ids.Contains(newsItemSummary.ID)
               select newsItemSummary;
    }
)

Translation of the Contains method against local collections was one of the last features added in the development of linq to sql for .net 3.5, so there are some cases that you would expect work that don't - such as translation of IList<T>.

Also, be aware that while LinqToSql will happily translate lists containing a vast number of items (I've seen it do over 50,000 elements), SQL Server will only accept 2,100 parameters for a single query.