Tip 21 – How to use the Single() operator – EF 4.0 only

This is 21st post in my ongoing series of Entity Framework Tips, and the first that is specific to EF 4.0.

Entity Framework 4.0 Beta 1

As you’ve probably heard VS 2010 Beta 1 is now available to subscribers which means some of you can get your hands on EF 4.0 Beta 1 too. If you have any questions our new pre-release forums are here.

Support for Single() and SingleOrDefault()

One thing we’ve added is support for LINQ’s Single() operator.

The expected semantics of this method is that if there is not exactly one match it will raise an exception. For example this:

var person = (from p in ctx.People
where p.Firstname == “Alex”
select p).Single();

Will throw an exception both if no matches are found or if more than one match is found.

We’ve also added support for SingleOrDefault() too. Which is slightly different, it means there should be at most one match, meaning it is okay for there not to be a match.

How it is implemented

Someone in the forums had already noticed that we had added support for Single() and noticed that the T-SQL issued is a TOP(2) query, i.e. something like this:

SELECT TOP (2) …
FROM [dbo].[People] AS [Extent1]
WHERE N'Alex' = [Extent1].[Firstname]

As Matthieu notes in his answer in the forums this makes perfect sense if you think about it. We have to get the first two results (and no more) to know whether we should throw an exception, if the resulting DataReader has 2 rows rather than just 1, it is exception time!