Single, SingleOrDefault, First, FirstOrDefault in Entity Framework

In this article let us look at the SingleSingleOrDefaultFirstFirstOrDefault methods in Entity Framework. These methods return a single record from the database based on a Where condition. We use these methods when the Where condition is not part of the Primary Key, But we want only one record in the query result. If the Where condition is part of the primary key then use the find method.

In our last tutorial Querying with LINQ To Entities in Entity Framework, we looked at how to query the database and get the desired results. We also looked at how to filter & sort the queried data. All these queries return the collection of entities.

Find Method

Finding the row with the Primary Key is one of the common tasks that we perform on a table. The DbContext gives us the Find Method to do just that. The DbSet exposes the Find method, which uses the Primary Key to return the entity matching the primary key.

Find Method with Composite Key

Finding Entities with Composite Primary Keys is equally easy. Composite Key consists of more than one field (or property) of the table. In the AdventureWorks table, ProductInventory has a composite primary key. ProductID & LocationID.

This table can be queried using the Find method in method syntax.

The find method uses the Primary Key to construct the SQL query and sends it to the database to retrieve the data. The order in which you specify the parameter for the Find Method must match the order of the primary key defined in the database.

The Find Method always looks for the Entity in the context first. If it does not find the entity in the context, then the Context sends the query to the database. If the record is not found in Context or in the database, it will return Null

For Example

  1. Find searches within the context to locate the entity with the given Primary/Composite Key. It will also look for the entities which are added in the context but not saved in the database. If found, then find will return it.
  2. The EF sends the query to the database to retrieve the entity.
  3. If it fails to find the entity, then it will return NULL

Finding the single entry without Primary key

There are many situations, where you would like to get a single result, but the where clause is not part of the Primary key. This can be achieved by using Single, SingleOrDefault, First & FirstOrDefault.

Single or SingleOrDefault

Single or SingleOrDefault is used, when you expect only a single row to exist in the table. If it finds more than one record, then the system will throw an exception. If no records found in the database then Single will throw an exception, while SingleOrDefault returns the default value

Single Method

Example 1

Single method querying for the product Blade, which exists in the database. Does not throw any exception.

Example 2

Single method querying for the product Football, which does not exist in the database. Throws an exception.

Example 3

Single method querying for the product which starts with “A”. Multiple records exist for this query in the database. Throws an exception.

SingleOrDefault Method

Example 1

SingleOrDefault method querying for the product Blade, which exists in the database. Does not throw a exception.

Example 2

SingleOrDefault method querying for the product Football, which does not exist in the database. It does not throw any exception. The method returns the default value, which is NULL.

Example

SingleOrDefault method querying for the product which starts with A. Multiple records exist for this query in the database. Hence throws an exception.

First or FirstOrDefault

First or FirstOrDefault is used when you expect more than one record in the database. This method returns the first matching row from the database. If no records found in the database then First will throw an exception, while FirstOrDefault returns the default value

First Method

Example 1

First method querying for the product Blade, which exist in the database, hence returns it.

Example

Single method querying for the product Football, which does not exist in the database. It will throw an exception

Example

Single method querying for the product which starts with A. Multiple records exist for this query in the database. First Record is returned by the Query

FirstOrDefault Method

Example

FirstOrDefault method querying for the product Blade, which exists in the database.

Example

FirstOrDefault method querying for the product Football, which does not exist in the database. The method returns the default value, which is NULL.

Example

FirstOrDefault method querying for the product which starts with A. Multiple records exist for this query in the database. Returns the first record.

Difference between Single Vs SingleOrDefault Vs First Vs FirstOrDefault

The following tables show the difference between Single, SingleOrDefault, First & FirstOrDefault

ConditionSingleSingleOrDefaultFirstFirstOrDefault
No Matching Rows foundExceptionDefault ValueExceptionDefault Value
1 Matching row foundReturns The RowReturns The RowReturns RowReturns Row
More than 1 matching row foundExceptionExceptionReturns 1st RowReturns 1st Row

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top