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()
[edit: this article is about reference types, value types behave slightly different as is noted by Matt T Heffron in the comments.]
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 one 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:
SELECT[GroupBy1].[A1] AS [C1]
FROM ( SELECTCOUNT(1) AS [A1]
FROM [dbo].[MyTable] AS [Extent1]
WHERE 100 = [Extent1].[Id]) AS [GroupBy1]
returns 0
So it might be better to use FirstOrDefault() and check for nulls.
Happy coding!
The post Compare LINQ Queries For Single Items appeared first on Don't Believe The Type.
