Method 'SingleOrDefault' not supported by Linq to Entities

Question

I read other posts on similar problem on using SingleOfDefault on Linq-To-Entity, some suggested using "First()" and some others suggested using "Extension" method to implement the Single().

This code throws exception:

Movie movie  = (from a in movies
                where a.MovieID == '12345'
                  select a).SingleOrDefault();

If I convert the object query to a List using .ToList(), "SingleOrDefault()" actually works perfectly without throwing any error.

My question is: Is it not good to convert to List? Is it going to be performance issue for more complicated queries? What does it get translated in SQL?

Movie  movie  = (from a in movies.ToList()
                where a.MovieID == '12345'
                  select a).SingleOrDefault();

Answer 1

SingleOrDefault is supported in LinqToEntities (4).

My assumption is that you are using EF 3.5 ?

There is no easy way to do a 1 or null.

I would write an exention that uses count, which would scale well.

  public static TElement SingleOrDefault<TElement>
  (this IQueryable<TElement> query)
 {
    if (query.Count() > 1)
   {
        throw new Exception();
   }
   return query.FirstOrDefault();
 }

Answer 2

In general it is not good to call .ToList() or .AsEnumerable() because it forces the query to be evaluated and all the data fetched from SQL.

In your example the .ToList() is in a particularly bad position as it will fetch ALL movies. Doing the where first and then the ToList would be better.

Shortest form of what you want in EF prior to EF4 would be:-

var movie = movies.FirstOrDefault(a => a.MovieID = 12345);

Enforcing single at the database by making MovieID a primary key would seem to be a better way to ensure there's only ever one movie with any given ID.

Answer 3

It's not supported on provider level as far as the the linq 2 entity team is concerned but there are ways todo it check here.

But as far as I know its now supported in .NET 4.