Compare Linq Queries For Single Items

Four Linq Queries for Returning Single Values


Using Linq queries, there are several methods for querying a collection of records in a database to return a single result; these are:- First() Single() FirstOrDefault(); SingleOrDefault() They will each behave slightly different in cases where there are no results or more than 1 result to return.
Operation==1 result to return>1 result to return==0 results to return
 First(); First result of collection First result of collection InvalidOperationException
 Single() ; First result of collection InvalidOperationException InvalidOperationException
 FirstOrDefault(); First result of collection First result of collection     Null
 SingleOrDefault(); First result of collection InvalidOperationException Null
So if there is a possibility that you could return zero results; you have to either check for an InvalidOperationException or null values. Personally I don't like having to deal with exceptions for results; I would rather have to deal with null values. So using the chart I have a choice of FirstOrDefault or SingleOrDefault. Again I don't want to have to deal with exceptions if the results are greater that 1; so that leaves me with FirstOrDefault.

What if your query will only return the 1 result, which do you choose?

Looking at the TSQL that is generated; there is only 1 difference. First() and FirstOrDefault() will do:- SELECT TOP(1) Whereas Single() and SingleOrDefault() will do:- SELECT TOP(2)

Why is this you ask?

It goes back to throwing exceptions. Doing a TOP(2) is the only way to determine whether there is more than 1 result; if there is it will throw the exception.

If you are returning zero results

Operation==0 results to return
FirstOrDefault(); Null
As a side note it would be slightly more expensive to check for zero results by doing a count instead of handling a null value as the database will be doing a sub-query as the Count() operator generates the following tsql:-returns 0 So it might be better to use FirstOrDefault() and check for nulls. Happy coding

Comments

HTTP Endpoint Monitoring for both Windows and Android
WebUp App