DefaultIfEmpty() doesn't work

Question

Hi i am trying to use DefaultIfEmpty() function on IQueryable and it's throwing an exception "Unsupported overload used for query operator 'DefaultIfEmpty'." this is my code:

 Dinner defaultDinner = db.Dinners.Where(d => d.DinnerID == 5).Single();
 Dinner blah;
 IQueryable<Dinner> bla = db.Dinners.Where(d => d.DinnerID == id)
                            .DefaultIfEmpty(defaultDinner);
 blah = bla.First();
 return blah;

I found a different way to do it without DefaultIfEmpty but i still want to know how to solve this... here is the first part of the exception:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NotSupportedException: Unsupported overload used for query operator 'DefaultIfEmpty'.

Answer 1

It seems pretty self-explanatory to me:

Unsupported overload used for query operator 'DefaultIfEmpty'

Sounds like your LINQ provider (which you haven't specified) doesn't support the overload of DefaultIfEmpty which takes a default value.

The simplest alternative is probably to use:

var ret = db.Dinners.Where(d => d.DinnerID == id)
                    .FirstOrDefault();
return ret ?? db.Dinners.Where(d => d.DinnerID == 5).Single();

Note that this approach avoids fetching the "default" dinner unless it's required, so it's more efficient too.

(If there should only be a single result for any ID, you should probably use SingleOrDefault instead of FirstOrDefault by the way. It's more logical that way.)

Answer 2

Try out

Dinner dinner = db.Dinners.SingleOrDefault(d => d.DinnerID == id);

Answer 3

The DefaultIfEmpty method throws a NotSupportedException when called on an IQueryable<T>. You could perform an explicit cast to an IEnumerable<T> by calling db.Dinners.Where(d => d.DinnerId == id).ToEnumerable().First().

You probably shouldn't use this method, though. It's better to just check whether the call returned any values, and retrieve the default if it didn't.

Edit: Oop. Nope, Jon Skeet is correct. The DefaultIfEmpty extension method relies on an implementation from the query provider, so it definitely depends on what query provider is being used.