Introduction
LINQ to SQL offers six primary approaches to selecting a single item:
- First
- FirstOrDefault
- Single
- SingleOrDefault
- Last
- LastOrDefault
This article describes the differences between these alternatives and when each should be applied. Each option provides a different approach to recovering a single item from the database and so long as the query yields the expected result there are no problems. When the query returns nothing or if it returns multiple values then each option exhibits different behaviors. Deciding which option to use depends on the potential for returning either nothing or multiple values as is described here.
The option First. If you execute a query using First to select a single item, LINQ will return the first item of the sequence regardless of whether or not one item or a hundred items are returned from the query. Where multiple items are expected, you can order the items to position the item desired into the first position if the table contains columns that will support it. For example if you want the newest item from a list and the table targeted contains a date and time stamp, you can order the items by that date and time value to move the desired entry into the first position. If the query returns nothing, it will throw an error and you will have to handle the results using a try catch block. For that reason, it is best to use First only when you are certain that the query will return in at least one value.
The option FirstOrDefault. This option works in a manner identical to the use of First with one exception: If the sequence returned contains no elements, instead of throwing an error it will return a default value. In code you can evaluate whatever is returned to see if it has a value and thus can handle that situation without resorting to handling it in a try catch block. For that reason, you should use FirstOrDefault instead of First when you are uncertain as to whether or not the query will return anything at all.
The option Single. This option will only successfully return a single result. If the query returns multiple values, it will throw an exception and if it returns no values at all it will also throw an exception. It would be best to limit use of this option to situations in which you are certain that the query will return only one value and if you are absolutely certain the value exists. For example, you might use this option to select a row based upon a query against the identity column (primary key) of the table when you are certain that the value exists.
The option SingleOrDefault. This option will also only successfully return a single result. If multiple values are returned it will throw an exception. If the query returns nothing it will instead return a default value which may be handled in code without resorting to reliance upon the try catch block. This option could be used when you are certain that the query will return no more than one value (e.g. querying against a primary key value) and when the query may return nothing.
The option Last. This exists as an option but it is not supported, if you attempt to use it, it will throw an error.
The option LastOrDefault. This option is also not supported and attempts to use it will also throw an error. If you want to get the last item of a sequence, order the results appropriately and use FirstOrDefault.
Attached Project
The attached project contains examples of each option in use against the Northwind database. If you want to experiment with each of the options you may download the project and update the connection string to point to a local instance of Northwind. Each of the examples provided is set up specifically to fail so that you can see whether or not the failure may be handled normally within the code or if you would have to handle the failure within the catch part of a try catch block.