Method "SingleOrDefault" is not supported by Linq for objects
I read other posts on a similar issue about using SingleOfDefault for Linq-To-Entity, some of which suggested using "First ()" and some others suggested using the Extend method to implement Single ().
This code throws an 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 ()" works fine without throwing any errors.
My question is, is it inconvenient to convert to a list? Will there be a performance issue for more complex queries? What does it translate to SQL?
Movie movie = (from a in movies.ToList()
where a.MovieID == '12345'
select a).SingleOrDefault();
a source to share
In general, it is not a good idea to call .ToList () or .AsEnumerable () because it forces the query to be evaluated and all data is fetched from SQL.
In your example .ToList () is in a particularly bad position as it will fetch ALL movies. Doing the first and then the ToList would be better.
The shortest form of what you want in EF before EF4 would be: -
var movie = movies.FirstOrDefault(a => a.MovieID = 12345);
Ensuring consistency in the database by making MovieID the primary key would seem to be the best way to ensure there is only one movie with any ID.
a source to share
SingleOrDefault is supported in LinqToEntities (4).
My guess is that you are using EF 3.5?
There is no easy way to do 1 or null.
I would write a header that uses count that scales well.
public static TElement SingleOrDefault<TElement>
(this IQueryable<TElement> query)
{
if (query.Count() > 1)
{
throw new Exception();
}
return query.FirstOrDefault();
}
a source to share