LINQ to SQL "Single" optimization

kingkeith

I've noticed that, when using a LINQ to SQL data context, doing a TableName.Single(x => x.Test == 1000) for example, the SQL query that is executed is:

-- Region Parameters
DECLARE @p0 Int = 1000
-- EndRegion
SELECT [t0].[ID], [t0].[Test]
FROM [TableName] AS [t0]
WHERE [t0].[Test] = @p0

which can be very inefficient - I would expect it to do a "TOP 2", as that is enough for it to complain if there is more than one result ("Sequence contains more than one element"), rather than try to retrieve all rows, just to then check if only one was returned...

Or is it expected that the developer writing these queries should think about it and optimize it themselves? i.e. TableName.Where(x => x.Test == 1000).Take(2).Single()

sgmoore

Probably more the developers thought - why spend the time to optimise for a case which should never happen.

If there is a slightest chance of there being more than one result then you shouldn't be using Single.

AFAIK Entity-Framework does actually perform this optimisation.

NetMage

Sounds like a suggestion for the .Net developers.