Which one is faster SingleOrDefault or FirstOrDefault

Generally, I don't see too much performance problem whether you use Single Or First. However if you have a table with lots of columns like 300 you might notice slight improvement if you use FirstOrDefault. Before we go deeper into the performance difference let’s understand how they are semantically different.

When you use FirstOrDefault, you will get the first item that matches a given criteria. When you use SingleOrDefault, you are saying that there can be only one value that matches the criteria. If there are more then 1 value that matches the criteria, throw an exception.

To see clear difference let’s query against Categories table with FirstOrDefault and SingleOrDefault. Code below shows the query along with its sql translation.

var beverage1 = db.Categories.SingleOrDefault(c => c.CategoryName == "Beverage");
 

Query using FirstOrDefault

var beverage1 = db.Categories.FirstOrDefault(c => c.CategoryName == "Beverage");                    

I think when you look at the sql translation it would make more sense why FirstOrDefault performs better.. In the SingleOrDefault, EF actually sends top(2) query which retrieve top 2 records that match the criteria specified for SingleOrDefault. This means that two rows have to be retrieved from the database and materialized. On the .net side, EF checks to see if the record count is greater then 1, then throw an exception. What’s worse is, it actually materializes two records into entities and then throws an exception. 

In the code below, I am retrieving products with categoryid equal 4 using SingleOrDefault. Since this code will throw exception, i wrap it inside of try catch block. Inside the exception, i query the object context to see how many entities got tracked and sure enough the result returned was 2. I think the future version should improve on this behavior where it really does not need to materialize two rows to entities before throwing an exception.

In the case of FirstOrDefault, only one row is retrieved from the database so it performs slightly better then SingleOrDefault. Normally such a small difference is hardly noticeable but on the current application i am working on, there are several tables which have more then 200 columns(don’t ask me why), you can actually see the difference between SingleOrDefault and FirstOrDefault.

Just watch out for this gotcha!