Avoiding NotSupportedException with IQueryable

Most remote LINQ providers can handle simple projections. For example, given a Movie class with lots of properties, and a MovieSummary class with a subset of those Movie properties, you can write a LINQ query like the following:

var summaries = db.Movies.Select(m => new MovieSummary {   
       Title = m.Title,
       Length = m.Length 
});

But it all falls apart if you try to offload some of the work to a MovieSummary constructor.

var db = new MovieDataStore();
var summaries = db.Movies.Select(m => new MovieSummary(m));

If you give the above query to the the Entity Framework, for example, it would throw a NotSupportedException.

Unhandled Exception: System.NotSupportedException: Only parameterless constructors and initializers are supported in LINQ to Entities.

A LINQ provider will not know what code is inside the MovieSummary constructor, because the constructor code isn't captured in the expression tree generated by the query. The Entity Framework tries to translate everything in the LINQ query into T-SQL, but since it can't tell exactly what is happening inside the constructor call it has to stop and throw an exception.

One solution is to move the entire projection out of the expression tree by switching from IQueryable to IEnumerable (using the AsEnumerable LINQ operator).

var summaries = db.Movies.AsEnumerable()
                  .Select(m => new MovieSummary(m));

With this query, however, a LINQ provider won't know you only need two properties from every movie. In the case of EF it will now bring back every column from the Movie table. If you need better performance and readability, it might be better to hide the projection in an extension method instead, and make sure the extension method extends IQueryable to keep the projection in an expression tree.

public static IQueryable<MovieSummary> ToMovieSummary(
    this IQueryable<Movie> source) 
{
    return source.Select(m => 
        new MovieSummary
            {
                Title = m.Title,
                Length = m.Length
            }                    
        );
}

// and in the query ...

var summaries = db.Movies.ToMovieSummary();

With EF, the above code will only select two columns from the database to create the movie summaries.