There is more than one way to Select a Cat (in Entity Framework)

You may have noticed that there are multiple different way to select a single entity in Entity Framework. In this post, we will take a look at 5 different methods of selecting a single entity based on a unique entity id.

Let’s say we have a context that stores cats:

public class Cat
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Breed { get; set; }
    public bool IsGrumpy { get; set; }
    public DateTime Birtdate { get; set; }
    public int Age { get; set; }
    public int Weight { get; set; }
}

public class CatContext : DbContext
{
public DbSet Cats { get; set; }
}

Given a unique Id for a cat, we can use any 1 of the following 5 methods to select that cat: Single, SingleOrDefault, First, FirstOrDefault, and Find.

Cat cat;
cat = context.Cats.Single(p => p.Id == catId);
cat = context.Cats.SingleOrDefault(p => p.Id == catId);
cat = context.Cats.First(p => p.Id == catId);
cat = context.Cats.FirstOrDefault(p => p.Id == catId);
cat = context.Cats.Find(catId);

 

What’s the difference?

Assuming the cat exists in the database, there really is no major difference between each of these. The differences come in when no cat matches that id. There is also a difference in the performance of these methods.

### Method	### Id Not Found	### Timing (ms / 1,000 selects)
Single	InvalidOperationException	703
SingleOrDefault	returns null	701
First	InvalidOperationException	730
FirstOrDefault	returns null	1,250
Find	returns null	9,625

 

Note, the timings were determined by individually selecting each of the 5,000 cats in the database using each method. This process was repeated 25 times and the average timing is reported above. Your mileage may vary.

The First methods are not really intended to be used when selecting based on a unique key. The intended use of First is when you are matching based on a predicate that might return multiple results and you are only interested in the first match. When selecting based on a unique key, prefer using the Single methods over the First methods as it makes your intention more clear.

In general, I prefer using the Find method as it reduces the need for a lambda expression. It is by far the simplest and clearest option. It is, however, a lot slower than Single / SingleOrDefault. If you are repeating the query in a loop, you may prefer to use Single for performance reasons.

It worth noting that Find will return a cat that have been added to a DbSet but not yet saved to the database, while the other methods will not find those new cats. This is likely part of the reason for the performance difference.

 

Some people try to get ‘fancy’ with doing their selects and craft complex LINQ queries like this:

Cat cat = (from c in context.Cats
            where c.Id == id
            select c).FirstOrDefault();

I would recommend strongly against doing something like this. Not only is this a needlessly complicated solution to a simple problem, but the overhead of parsing that query can actually affect performance. I remember seeing this done in a piece of code that was executed thousands of times in a loop. I cut out several minutes from the runtime of the application by simply changing the code to use the Single() method.