Avoiding NotSupportedException with IQueryable

Tuesday, March 20, 2012

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.


Comments
gravatar Damien Guard Tuesday, March 20, 2012
The problem with this approach is you have to repeat the initialization pattern everywhere you need it.

An alternative is to do this:

Expression<Func<Movie, MovieSummary>> createMovieSummary = (m) => new MovieSummary { Title = m.Title, Length = m.Length };

Which now means you can do this:

return source.Select(c => createMovieSummary(c));

In multiple places... and you can also use this as a regular non-IQueryable, e.g.

var movie = Movies.First();
var summary = createMovieSummary(m);

[)amien
gravatar scott Tuesday, March 20, 2012
@[)amien - That's awesome - thank you.
gravatar tobi Tuesday, March 20, 2012
EF's LINQ support is really poor, and this case is further proof for it. L2S at least allows the ctor call to happen.
gravatar Galilyou Wednesday, March 21, 2012
@tobi in L2S when the constructor call happens, will it return all the columns from sql, or will it be able to figure out (some how) what fields are needed in the constructor?
gravatar Shashi Wednesday, March 21, 2012
@scott, @[)amien, Awesome - I was just about to look up how to do this, thanks!
gravatar tobi Wednesday, March 21, 2012
Galilyou, it will return the entire entity. The ctor could do anything with it. It could even publish the object as a property.
gravatar Ian Patrick Hughes Thursday, March 22, 2012
Wow. That is a pretty elegant approach, Damien. Very nice.
gravatar Rex Thursday, March 22, 2012
I'm feeling a bit dumb; I can't quite figure out the context of Damien's solution. I can declare the expression somewhere that is accessible to the querying code, but I can't see how to invoke it without Invoke:

return source.Select(c => createMovieSummary.Invoke(c));

I'm obviously missing something... obvious.
gravatar scott Friday, March 23, 2012
@Rex: No, you are not missing anything. I didn't get it to work with Damien's exact syntax, instead using: http://codepaste.net/8zo2yn

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

return source.Select(createMovieSummary);
}
gravatar Rex Friday, March 23, 2012
Thanks Scott, and thanks for the elegant solution to a recurring annoyance. While I'm at it, thanks for all the great content--the blog, Pluralsight, etc. It's consistently useful, insightful and well presented.
gravatar scott Friday, March 23, 2012
@Rex - thank you!
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!