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  gt c ID    5     unlikely    to be more than one  but technically COULD BE  var bobbyCust   db Customers  FirstOrDefault c  gt c FirstName     Bobby      clearly could be one or many  so use First   var latestCust   db Customers  OrderByDescending x  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I don t understand why you re using FirstOrDefault x  gt  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  gt  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

User · Answer

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

User · Answer

In your last example    var latestCust   db Customers  OrderByDescending x  gt  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

User · Answer

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   gt  obj AccountNumber    987654

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt int gt  items   new List lt int gt     9 10 9     Returns the first element of a sequence after satisfied the condition more than one elements int result1   items Where item   gt  item    9  FirstOrDefault      Throw the exception after satisfied the condition more than one elements int result3   items Where item   gt  item    9  SingleOrDefault

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

Examples related to .net

Examples related to linq

Examples related to linq-to-sql