[.net] LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria

Consider the IEnumerable extension methods SingleOrDefault() and FirstOrDefault()

MSDN documents that SingleOrDefault:

Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.

whereas FirstOrDefault from MSDN (presumably when using an OrderBy() or OrderByDescending() or none at all),

Returns the first element of a sequence

Consider a handful of example queries, it's not always clear when to use these two methods:

var someCust = db.Customers
.SingleOrDefault(c=>c.ID == 5); //unlikely(?) to be more than one, but technically COULD BE

var bobbyCust = db.Customers
.FirstOrDefault(c=>c.FirstName == "Bobby"); //clearly could be one or many, so use First?

var latestCust = db.Customers
.OrderByDescending(x=> x.CreatedOn)
.FirstOrDefault();//Single or First, or does it matter?

Question

What conventions do you follow or suggest when deciding to use SingleOrDefault() and FirstOrDefault() in your LINQ queries?

In my opinion FirstOrDefault is being overused a lot. In the majority of the cases when you’re filtering data you would either expect to get back a collection of elements matching the logical condition or a single unique element by its unique identifier – such as a user, book, post etc... That’s why we can even get as far as saying that FirstOrDefault() is a code smell not because there is something wrong with it but because it’s being used way too often. This blog post explores the topic in details. IMO most of the times SingleOrDefault() is a much better alternative so watch out for this mistake and make sure you use the most appropriate method that clearly represents your contract and expectations.

I queried Google for the usage of the different methods on GitHub. This is done by running a Google search query for each method and limiting the query to the github.com domain and the .cs file extension by using the query "site:github.com file:cs ..."

It seems that the First* methods are more commonly used than the Single* methods.

| Method               | Results |
|----------------------|---------|
| FirstAsync           |     315 |
| SingleAsync          |     166 |
| FirstOrDefaultAsync  |     357 |
| SingleOrDefaultAsync |     237 |
| FirstOrDefault       |   17400 |
| SingleOrDefault      |    2950 |

SingleOrDefault: You're saying that "At most" there is one item matching the query or default FirstOrDefault: You're saying that there is "At least" one item matching the query or default

Say that out loud next time you need to choose and you shall likely choose wisely. :)

For LINQ -> SQL:

SingleOrDefault

• will generate query like "select * from users where userid = 1"
• Select matching record, Throws exception if more than one records found
• Use if you are fetching data based on primary/unique key column

FirstOrDefault

• will generate query like "select top 1 * from users where userid = 1"
• Select first matching rows
• Use if you are fetching data based on non primary/unique key column

Both are the element operators and they are used to select a single element from a sequence. But there is a minor difference between them. SingleOrDefault() operator would throw an exception if more than one elements are satisfied the condition where as FirstOrDefault() will not throw any exception for the same. Here is the example.

List<int> items = new List<int>() {9,10,9};
//Returns the first element of a sequence after satisfied the condition more than one elements
int result1 = items.Where(item => item == 9).FirstOrDefault();
//Throw the exception after satisfied the condition more than one elements
int result3 = items.Where(item => item == 9).SingleOrDefault();

If your result set returns 0 records:

• SingleOrDefault returns the default value for the type (e.g. default for int is 0)
• FirstOrDefault returns the default value for the type

If you result set returns 1 record:

• SingleOrDefault returns that record
• FirstOrDefault returns that record

If your result set returns many records:

• SingleOrDefault throws an exception
• FirstOrDefault returns the first record

Conclusion:

If you want an exception to be thrown if the result set contains many records, use SingleOrDefault.

If you always want 1 record no matter what the result set contains, use FirstOrDefault

One thing that is missed in the responses....

If there are multiple results, FirstOrDefault without an order by can bring back different results based on which ever index strategy happened to be used by the server.

Personally I cannot stand seeing FirstOrDefault in code because to me it says the developer didn't care about the results. With an order by though it can be useful as a way of enforcing the latest/earliest. I've had to correct a lot of issues caused by careless developers using FirstOrDefault.

So as I understand now, SingleOrDefault will be good if you are querying for data that is guaranteed to be unique i.e. enforced by DB constraints like primary key.

Or is there a better way of querying for the primary key.

Assuming my TableAcc has

AccountNumber - Primary Key, integer
AccountName
AccountOpenedDate
AccountIsActive
etc.

and I want to query for an AccountNumber 987654, I use

var data = datacontext.TableAcc.FirstOrDefault(obj => obj.AccountNumber == 987654);

In your last example:

var latestCust = db.Customers
.OrderByDescending(x=> x.CreatedOn)
.FirstOrDefault();//Single or First, or doesn't matter?

Yes it does. If you try to use SingleOrDefault() and the query results in more than record you would get and exception. The only time you can safely use SingleOrDefault() is when you are expecting only 1 and only 1 result...

There is

• a semantical difference
• a performance difference

between the two.

Semantical Difference:

• FirstOrDefault returns a first item of potentially multiple (or default if none exists).
• SingleOrDefault assumes that there is a single item and returns it (or default if none exists). Multiple items are a violation of contract, an exception is thrown.

Performance Difference

• FirstOrDefault is usually faster, it iterates until it finds the element and only has to iterate the whole enumerable when it doesn't find it. In many cases, there is a high probability to find an item.

• SingleOrDefault needs to check if there is only one element and therefore always iterates the whole enumerable. To be precise, it iterates until it finds a second element and throws an exception. But in most cases, there is no second element.

Conclusion

• Use FirstOrDefault if you don't care how many items there are or when you can't afford checking uniqueness (e.g. in a very large collection). When you check uniqueness on adding the items to the collection, it might be too expensive to check it again when searching for those items.

• Use SingleOrDefault if you don't have to care about performance too much and want to make sure that the assumption of a single item is clear to the reader and checked at runtime.

In practice, you use First / FirstOrDefault often even in cases when you assume a single item, to improve performance. You should still remember that Single / SingleOrDefault can improve readability (because it states the assumption of a single item) and stability (because it checks it) and use it appropriately.

I use SingleOrDefault in situations where my logic dictates that the will be either zero or one results. If there are more, it's an error situation, which is helpful.

I don't understand why you're using FirstOrDefault(x=> x.ID == key) when this could retrieve results much faster if you use Find(key). If you are querying with the Primary key of the table, the rule of thumb is to always use Find(key). FirstOrDefault should be used for predicate stuff like (x=> x.Username == username) etc.

this did not deserve a downvote as the heading of the question was not specific to linq on DB or Linq to List/IEnumerable etc.

Nobody has mentioned that FirstOrDefault translated in SQL does TOP 1 record, and SingleOrDefault does TOP 2, because it needs to know is there more than 1 record.

In your cases, I would use the following:

select by ID==5: it's OK to use SingleOrDefault here, because you expect one [or none] entity, if you got more than one entity with ID 5, there's something wrong and definitely exception worthy.

when searching for people whose first name equals "Bobby", there can be more than one (quite possibly I would think), so you should neither use Single nor First, just select with the Where-operation (if "Bobby" returns too many entities, the user has to refine his search or pick one of the returned results)

the order by creation date should also be performed with a Where-operation (unlikely to have only one entity, sorting wouldn't be of much use ;) this however implies you want ALL entities sorted - if you want just ONE, use FirstOrDefault, Single would throw every time if you got more than one entity.