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)
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!
And it generates TOP 2 syntax for extra efficiency with SQL Sever 🙂
[)amien
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?
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.
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.
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.
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.