Click here to Skip to main content
Click here to Skip to main content

Compare LINQ Queries for Single Items

, 19 Sep 2013
Rate this:
Please Sign up or sign in to vote.
Four LINQ queries for returning single values using LINQ queries.
Compare Linq Queries For Single Items

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Garry Pilkington
Web Developer Asterope Systems Ltd
United Kingdom United Kingdom
Software developer for over 10 years mainly creating enterprise web applications using the Microsoft .Net stack.
 
Owner of Asterope Systems Ltd specializing in consulting/contracting.

Comments and Discussions

 
QuestionAnother Method PinmemberGeorge Swan20-Sep-13 0:02 
SuggestionPretty good ... PinprofessionalMatt T Heffron18-Sep-13 8:59 
GeneralRe: Pretty good ... PinmemberGarry Pilkington19-Sep-13 3:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 19 Sep 2013
Article Copyright 2013 by Garry Pilkington
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid