A message from your sponsor: Single and SingleOrDefault are supported in EF4

Thursday, December 10 2009 - Data Access

Just a friendly reminder that I’m passing on from one of the tech reviewers of my book who (thankfully) noticed that I had left a note in there about Single & SingleOrDefault not being supported.

They were not in .NET 3.5 SP1 Entity Framework, but they are now supported in EF4.

Single is the way to go when you only expect one item to be in your result set.

As noted by many, the benefit of Single is highlighted in testing. Single expects only one item to be in the result set. If there are more, then an exception is thrown and this can help you discover logic errors in your application.

For a list of supported & unsupported LINQ methods used (or not used) in LINQ to ENtities, see the MSDN Topic: Supported and Unsupported LINQ Methods (LINQ to Entities)

Tagged as: ,

#1 Damien Guard on 12.10.2009 at 6:27 PM

And it generates TOP 2 syntax for extra efficiency with SQL Sever :)

[)amien

#2 Julie on 12.11.2009 at 5:07 PM

Thanks Damien. I didn't even see that before. Can you help me grok why "select top(2) blah where id=3" is more efficient than "select blah where id=3". I guess I'm presuming that id is a primary key. But using single we're already presuming there is only one result anyway. But why top 2 not top 1?

#3 Dan on 12.12.2009 at 9:38 AM

You said, "If there are more, then an exception is thrown...". TOP 1 eliminates the possibility of "more". TOP 2 allows for the smallest possible set of "more" being handled by the database and client.

#4 Dmitry on 12.13.2009 at 2:40 AM

SELECT TOP 2 is more efficient than just unbounded SELECT if do not use a where condition [ex: context.Companies.Single()]. LINQ-to-SQL uses the later approach.

First() is more efficient than Single() because it uses TOP 1 instead of TOP 2. As mentioned above, Single() uses TOP 2 to see if there is more than 1 result matching the criteria.

I never understood why EF 1.0 did not support Single() and SingleOrDefault(). The implementation seems to be a matter of using Skip(0).Take(2) and then checking the result count.

#5 Diego on 12.13.2009 at 8:17 PM

Just to clarify, we added support Single and SingleOrDefault at the top level query. The reason we did not support this before has to do with the usual time and resources limitations.

#6 ghd on 2.20.2010 at 1:11 AM

You said, "If there are more, then an exception is thrown...". TOP 1 eliminates the possibility of "more". TOP 2 allows for the smallest possible set of "more" being handled by the database and client.