27

I have been using the Find(id) extension method with collections in Entity Framework 5. However, many of the examples I see use Where(s => s.Id == 1), to which I added FirstOrDefault() to get the object instead of a collection. Is this a style difference, or is there a functional reason for the apparent .Where() preference?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Thomas McNamee
  • 692
  • 1
  • 6
  • 14
  • Im pretty sure they are equivalent and have always treated them as such (they definitively return the same result) but I've always been curious if there are subtle differences. On a side note I think you should use .SingleOrDefault not .FirstOrDefault as its kinda a big deal if there actually are multiple results – undefined Feb 06 '13 at 03:53
  • 1
    btw you can use `FirstOrDefault(s => s.Id == 1)` or better `SingleOrDefault(...)` – abatishchev May 04 '13 at 03:54

2 Answers2

51

Find() has a fundamental difference to Where(), Single(), First() etc. in that it will first search for the object in memory and only hit the database if the object has not already been loaded. Thus try to use Find() where possible as it offers possible speed benefits of loading from memory. Find() only works with the primary key and doesn't support lambdas so it is not very flexible.

Where() is typically used to get a listing of objects. To retrieve a single object I normally use either Single(), SingleorDefault(), First(), FirstorDefault().

Single() and SingleOrDefault() differ from First() and FirstOrDefault() as they ensure that a maximum of one object can satisfy the criteria which helps ensure the integrity of the data in the database. They 'Single' clauses do this by selecting 'TOP 2' in the SQL query and then throwing an exception if two entities are returned.

Note that you should not chain these to the end of the Where() clause.

So instead of

.Where(s => s.Id == 1).FirstOrDefault();

use:

.FirstOrDefault(s => s.Id == 1);

I wrote a blog post to fully explore this issue : http://judeokelly.com/primer-on-selecting-data-using-entity-framework/

Judo
  • 5,167
  • 3
  • 24
  • 34
  • 5
    Why should you not use `.Where(s => s.Id == 1).FirstOrDefault()` instead of `.FirstOrDefault(s => s.Id == 1)`? In your blog you state that there is no advantage in using the first statement, but you don't mention any disadvantage. If there is indeed no disadvantage, then it is a matter of preference and you *could* use the second statement instead of *should*. – user247702 Jun 04 '13 at 13:06
  • 4
    The point is that both are equivalent, in which case go for the shortest and easiest to read. – Judo Jun 09 '13 at 05:44
  • 7
    Well most guidance on writing terse code is subjective. Whats the point of adding the Where() clause? does it make it clearer? If so use it. If not just omit it and the code is shorter. – Judo Jun 10 '13 at 06:32
  • So if I know the object I'm after is not loaded in memory but could exists in the database, I should use `FirstOrDefault`? – Rudey Nov 19 '15 at 08:13
  • 1
    Yes, there's no advantage to using Find() if you are sure the object is not in memory. – Judo Nov 24 '15 at 14:26
  • 2
    By using .FirstOrDefault(condition) instead of .Where(condition).FirstOrDefault(), you can SLOW DOWN your code performance. – scegg May 04 '16 at 01:58
  • @AllenSt.Clair Why do you say that? Regarding that we're talking about Linq-to-Entities, `.FirstOrDefault(condition)` is the correct approach. If instead the question was on Linq-to-Objects, than maybe the other option might be more performant. – Nelson Reis Jan 04 '17 at 17:39
  • @NelsonReis By testing: query a not existed record in a table contains 500 records, using EF6. There is no significant differences in performance test between Where and Where.FirstOrDefault. The simple thing to remember is use Where.FirstOfDefault instead of FirstOfDefault directly, leading to a at least not worse result. – scegg Jan 06 '17 at 01:48
  • @NelsonReis Case TPT with EF6 and inherited relationship Derived1-> Base, Derived2-> Base: Where().FirstOrDefault() alters a query slightly: the WHERE-clause itself is pushed down into the Subselect against Special1/Special2 before the Join against Base. Actually the result is better SQL, which is important, as not all DB(MS) can optimize queries that good. Case SQlite: Index-Structures on the Derived-Tables aren't used if the JOIN & WHERE is outside (see limits on query flattening). Not using the index has a huge impact. – Leonidas Mar 27 '19 at 12:43
0

If performance matters, as per http://wp.secretnest.info/archives/2991 , .Where(...).FirstOrDefault() is much faster than FirstOrDefault(...). Of course a List Find() might again be much faster than the previous.

So contrary to the accepted answer, you should not avoid Where(...).FirstOrDefault()!

loxx
  • 119
  • 4
  • 2
    That would only be valid outside the context of the Entity framework. The question has been tagged with entity-framework therefore the answer is not valid. Would you do this on an already fetched `IEnumerable` of entities you'd be correct. – Feanaro Dec 13 '15 at 18:38