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

Question

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?

Colbycolbye

Answer 1

Whenever you use SingleOrDefault, you clearly state that the query should result in at most a single result. On the other hand, when FirstOrDefault is used, the query can return any amount of results but you state that you only want the first one.

I personally find the semantics very different and using the appropriate one, depending on the expected results, improves readability.

Syntax

A very important difference is that if you use SingleOrDefault on a sequence with more than one element, it throws an exception. – Brott

@kami if it didn't throw an exception it would be exactly like FirstOrDefault. The exception is what makes it SingleOrDefault. Good point bringing it up, and puts a nail on the coffin of differences. – Forbes

I must say that from performance wise, the FirstOrDefault is working about 10 times faster than SingleOrDefault , using a List<MyClass> of 9,000,000 elements, class contains 2 integers and the Func contains a search of these two integers. Searching 200 times in a loop took 22 seconds on var v = list.SingleOrDefault(x => x.Id1 == i && x.Id2 == i); and var v = list.FirstOrDefault(x => x.Id1 == i && x.Id2 == i); about 3 seconds – Staysail

@BitsandBytesHandyman If SignleOrDefault didn't throw an exception when the sequence contained more than one items it would not behave exactly like FirstOrDefault. FirstOrDefault returns the first item, or null if the sequence is empty. SingleOrDefault should return the only item, or null if the sequence is empty OR if it contains more than one items, without throwing an exception at all. – Aquarius

@ThanasisIoannidis According to the MSDN documentation, SingleOrDefault "throws an exception if there is more than one element in the sequence" – Maynord

@Maynord Yes, I am aware of that. Reading carefully my comment, I was saying what SingleOrDefault should do, not what it does. But of course, what it should do, is very subjective. For me, the "SomethingOrDefault" pattern means: Get the value of "Something". If "Something" fails to return a value, return the default value. Which means that the default value should be returned even in the case where "Something" would throw an exception. So, where Single would throw an exception, SingleOrDefault should return the default value, in my perspective. – Aquarius

@Maynord continuing my previous comment: But in the case of more than one items in the sequence, SingleOrDefault behaves exactly like Single. They both throw an exception. In my perspective, that looks a bit odd, but I understand the rationale behind the documented behavior. Maybe, the behavior I expected from SignleOrDefault should be implemented in a method called OnlyOrDefault, or something like that, where it should return the only item of the sequence, or, in any other case (zero, or more than one items) it should return the default value. – Aquarius

@ThanasisIoannidis thanks...I see what you were saying now – Maynord

@BryanMenard I think the wording of SingleOrDefault is confusing if you just take it as what it implies w/o digging into the API document. It sounds like give me just one or whatever, or give me a "top 1" (like SQL). I disagree "you clearly state that the query should result in at most a single result." Very often we can not predict there will be at most a single result unless searching "United States" from countries. And Default is misleading, many who has SQL experience knows when you don't specify "Order By" along w/ Top 1, you'll get the one by clustered index... – Mathematics

after looking in the msdn ,I confirm that singleOrDefault raise InvalidOperationException when The input sequence contains more than one element. – Merril

SingleOrDefault can be useful for a unit test to fail if more than 1 records are returned. I just went thru a major refactor where SingleOrDefaults were used all over the place because the previous devs didn’t anticipate it could become multiple one day. Well it did. If FirstOrDefaults were used would’ve saved a ton of work. – Immunoreaction

In the interest of Database integrity, you should use Single/SingleOrDefault when there should be only one item. If it throws an error, then you know something is wrong with the data, and for that reason it is a good tool to have. – Easiness

I would argue that if you are expecting to only ever have 1 item then you should be using SingleOrDefault. I'm not saying FirstOrDefault is inherently bad, however if there is no preceding OrderBy or OrderByDescending how can you guarantee you are getting the correct record? If there were multiple results and you call FirstOrDefault without an OrderBy the code will carry on as normal. – Introversion

Answer 2

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

Francium

I would suggest that it's rare to actually want the exception so most of the time FirstOrDefault would be preferred. I know cases would exist just not that often imo. – Kore

FirstOrDefault is returned first record means new record(last) / old record(first) ?can u clarify me? – Finalize

@Duk, it depends on how you sort the records. You can use OrderBy() or OrderByDescending() etc before calling FirstOrDefault. See the OP's code example. – Stocktaking

I like this answer too. Especially given that there are some occasions where you actually want the exception thrown because you intend to handle that rare case properly elsewhere as opposed to just pretend it doesn't happen. When you want the exception you are saying this clearly, and also forcing others to handle just encase making the overall system more robust. – Grady

Note: To ensure single result the SingleOrDefault is always doing 2 data base calls: select top(2) [id], [column2]...from [table] where [column2]=@filter then select top(2) [id], [column2]...from [table] where [id] = @id – Ducan

If you want to lookup something that is supposed to be unique, like a primary key, then you definitely DON'T want to silently pick a random one out of several matches. (Yes, many SQL databases default to effectively random ordering if nothing is specified) – Advancement

Answer 3

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. In theory, it could stop iterating when it finds a second element and throw an exception. But in most cases, there is no second element, so it doesn't help much.

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.

Castroprauxel

+1 "or when you can't afford checking uniqueness (e.g. in a very large collection).". I was looking for this. I would also add enforce uniqueness when inserting, or/and by design instead of at the time of making query! – Sternson

I can imagine SingleOrDefault iterates over a lot of objects when using Linq to Objects, but doesn't SingleOrDefault have to iterate over at most 2 items if Linq is talking to a database for example? Just wondering.. – Absorbance

@memetolsen Consider the code spit for the two with LINQ to SQL - FirstOrDefault uses Top 1. SingleOrDefault uses Top 2. – Kunz

@JimWooley I guess I misunderstood the word 'enumerable'. I thought Stefan meant the C# Enumerable. – Absorbance

@memetolsen correct in terms of the original answer, your comment was referring to the database, so I was offering what happens from the provider. While the .Net code only iterates over 2 values, the database visits as many records as it needs to until it hits the second one meeting the criteria. – Kunz

The problem is that you "usually" expect only one (otherwise you'll get an exception anyway). So "usually" it iterates all ... – Castroprauxel

About performance considerations of SingleOrDefault: it does not has to iterate over the entire collection. It just look if the returned results have more than one element. So, I think that process time is despicable. – Jessabell

@Aikanáro: And how does it determine if it has more than one element, given that it is not a list nor an array? Say it would be the return value of .Select or .Where. A "real" IEnumerable. – Castroprauxel

"SingleOrDefault needs to check if there is only one element and therefore always iterates the whole enumerable" This is not true; it only needs to know if the enumerable contains "more than one" element, so simply retrieving the first two elements is sufficient. You can see in the source at github.com/microsoft/referencesource/blob/master/System.Core/… that there is no loop, just two calls to enumerator.MoveNext() – Rebate

@Richiban: Sure, but note this: "To be precise, it iterates until it finds a second element...". and "But in most cases, there is no second element.". Combined: in many cases it iterates the whole enumerable. Because often you have where and select clauses involved, it would have a performance impact. There is even an overloaded SingleOrDefault with predicate. – Castroprauxel

@StefanSteinegger The case you mention here is to be considered when designing our code but saying that in itself, SingleOrDefault is a performance hit is misguiding for people coming to get the information. You should really reformulate this part to be clearer in the intent. If it is an error to have more than 1, I think it is always best to use Single. In most cases, I think Single should be used on lists you expect to contain only a single element anyway. But anyhow, I agree that combined with other LINQ "operators" it may cause impact. – Orientalism

Looks like you should always use FirstOrDefault when querying on the primary key – Workbook

@Savage: From performance point of view: yes. On the other hand, when you want to check that the primary key is really unique, you should use SingleOrDefault. If your data was corrupt and primary keys aren't unique, you would get weird results when your code wouldn't recognize it. – Castroprauxel

Unless I'm misreading the decompiled code for SingleOrDefault in Linq to Objectswith Predicate, the whole collection is ALWAYS iterating the whole collection even if the collection contains 1000 items matching the predicate. There is no fast exit path. github.com/microsoft/referencesource/blob/… – Orientalism

@Orientalism This is supposably because in most cases, there is none or a single item. To check this, all items must be evaluated. More than one item is an error case. Optimizing this would only optimize the error case. Oh I see, you mean because I stated something else. I'll fix that. – Castroprauxel

Moving and therefore always iterates the whole enumerable to the end of that paragraph will prevent a lot of discussion. – Endurant

I'm quite sure there would have been a benefit since they did it in .NET Core but I don't think this kind of optimization will see the light of day in the .NET Framework. github.com/dotnet/runtime/blob/… – Orientalism

Answer 4

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.

Brownout

When I ran a SingleOrDefault through LinqPad and VS, I never got SELECT TOP 2, with FirstOrDefault I was able to get SELECT TOP 1, but as far as I can tell you do not get SELECT TOP 2. – Mauricemauricio

Hey i have been tried in linqpad too and sql query made me afraid because it completely fetch all rows. I am not sure how can this be happen? – Jenijenica

This depends entirely on the LINQ provider used. For example, LINQ to SQL and LINQ to Entities could translate to SQL in different ways. I just tried LINQPad with the IQ MySql provider, and FirstOrDefault() adds LIMIT 0,1 while SingleOrDefault() adds nothing. – Pilate

EF Core 2.1 translates FirstOrDefault to SELECT TOP(1), SingleOrDefault to SELECT TOP(2) – Circumfuse

If you specify SingleOrDefault, the database does a SELECT TOP(2) so that .NET can Exception if there is more than one result. – Lymphadenitis

Answer 5

For LINQ -> SQL:

SingleOrDefault

• will generate query like "select TOP 2 * 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

Discrown

i think you should remove "Select all matching rows" from SingleOrDefault – Lenny

Answer 6

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.

Supernatant

Often I find that SingleOrDefault() highlights cases where I have not applied the correct filtering on the result set, or where there is an issue with duplications in the underlying data. More often than not I find myself using Single() and SingleOrDefault() over the First() methods. – Doodle

There are performance implications for Single() and SingleOrDefault() on LINQ to Objects if you have a large enumerable but when talking to a database (e.g. SQL Server) it will do a top 2 call and if you're indexes are setup correctly that call shouldn't be expensive and I'd rather fail fast and find the data issue instead of possibly introducing other data issues by taking the wrong duplicate when calling First() or FirstOrDefault(). – Euterpe

Answer 7

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.

Knuckleduster

I disagree. If your database ID is a primary key, then the database is already enforcing uniqueness. Wasting CPU cycle to check if the database is doing its job on every query is just silly. – Careycarfare

Answer 8

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. :)

Impenetrable

Actually having no results is perfectly acceptable use of FirstOrDefault. More correctly: FirstOrDefault` = Any number of results but I only care about the first one, there may also be no results. SingleOrDefault = There are 1 or 0 results, if there are more that means there's an error somewhere. First = There is at least one result, and I want it. Single = There is exactly 1 result, no more, no less, and I want that one. – Celadon

Answer 9

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();

Denys

"there is a minor difference between them" - That's major! – Peplum

Answer 10

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...

Aggression

That's correct. If you get 0 result, you also get an exception. – Lindeman

Answer 11

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);

Dionne

Answer 12

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.

Scapula

"FirstOrDefault() is a code smell" is way over the top. When SQL is involved, and you know you're querying a unique field, FirstOrDefault is even marginally better (TOP(1) vs TOP(2)). Also, ordering by some property and taking FirstOrDefault() is a very normal and healthy pattern to get an object by max/min value. The fact that the statement can be used to mask bad data content doesn't make it a code smell. That would make foreach a code smell, or anything. – Endurant

Answer 13

Besides the two semantic differences — expecting zero-or-one item vs expecting zero-or-many items and returning the only item vs returning the first item — there is another difference:

FirstOrDefault, ideally, should go with an OrderBy clause; otherwise there is no concrete definition of "first" and you might see warnings such as:

The query uses the 'First'/'FirstOrDefault' operator without 'OrderBy' and filter operators. This may lead to unpredictable results.

An example where order-by is a must-have:

SELECT TOP 1 salary FROM employee ORDER BY salary DESC

SingleOrDefault should be used when exactly one item is expected and order-by does not make sense. An example:

SELECT MAX(salary) FROM employee

Fellers

Answer 14

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.

Unkennel

Answer 15

In essence this gives you some sort of validation to clean up your data, if you choose one over the other it will both give the data but SingleOrDefault will make you realize that when the data you're expecting should only have 1 result and spits out more 1 then you need to take a look at why your stored procedure or query resulted in such, duplicated items are never good in queries anyways.

Demission

Answer 16

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 |

Tonneson

interesting answer, but hope it didn't imply to choose by popularity – Maryellen

This answer is meaningless for development decisions. – Endurant

Answer 17

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.

Unpeg

Which namespace is Find() in? – Colbycolbye

Could you tell us please? Still waiting for an answer. – Card

Could be this: #14033209 – Intuit

The word "IEnumerable" is in the very first line of the question body. If you only read the title and not the actual question, and posted an incorrect answer as a result, that's your mistake and a perfectly legitimate reason to downvote IMO. – Expressionism