In this article let us look at the Single, SingleOrDefault, First, FirstOrDefault 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.
Table of Contents
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.
1 2 3 4 5 6 7 | using (AdventureWorks db = new AdventureWorks()) { var p = db.Products.Find(1); Console.WriteLine("{0}", p.Name); } |
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.
1 2 3 4 5 6 7 8 | using (AdventureWorks db = new AdventureWorks()) { var prdInv = db.ProductInventories.Find(1, 1); Console.WriteLine("{0} {1}", prdInv.ProductID, prdInv.LocationID); } |
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 2 3 4 | var p = db.Products.Find(1) //Query is sent to the database and entity is added to the context var q = db.Products.Find(1) //Here the query is not sent to the database. It returns the entity from the context |
- 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.
- The EF sends the query to the database to retrieve the entity.
- 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.Single(p => p.Name == "Blade"); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Example 2
Single method querying for the product Football, which does not exist in the database. Throws an exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.Single(p => p.Name == "Football"); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Example 3
Single method querying for the product which starts with “A”. Multiple records exist for this query in the database. Throws an exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.Single(p => p.Name.StartsWith("A")); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
SingleOrDefault Method
Example 1
SingleOrDefault method querying for the product Blade, which exists in the database. Does not throw a exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.SingleOrDefault(p => p.Name == "Blade"); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.SingleOrDefault<Product>(p => p.Name == "Football"); if (prd == null) { Console.WriteLine("Product not found"); } } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Example
SingleOrDefault method querying for the product which starts with A. Multiple records exist for this query in the database. Hence throws an exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.SingleOrDefault(p => p.Name.StartsWith("A")); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.First(p => p.Name == "Blade"); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Example
Single method querying for the product Football, which does not exist in the database. It will throw an exception
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.First(p => p.Name == "Football"); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.First(p => p.Name.StartsWith("A")); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
FirstOrDefault Method
Example
FirstOrDefault method querying for the product Blade, which exists in the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.FirstOrDefault(p => p.Name == "Blade"); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Example
FirstOrDefault method querying for the product Football, which does not exist in the database. The method returns the default value, which is NULL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.FirstOrDefault(p => p.Name == "Football"); if (prd == null) { Console.WriteLine("Product not found"); } } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Example
FirstOrDefault method querying for the product which starts with A. Multiple records exist for this query in the database. Returns the first record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (AdventureWorks db = new AdventureWorks()) { try { Product prd = db.Products.FirstOrDefault(p => p.Name.StartsWith("A")); Console.WriteLine("{0} {1}", prd.Name, prd.Color); } catch (Exception e) { Console.WriteLine("Exception thrown {0}", e.Message); } } |
Difference between Single Vs SingleOrDefault Vs First Vs FirstOrDefault
The following tables show the difference between Single, SingleOrDefault, First & FirstOrDefault
| Condition | Single | SingleOrDefault | First | FirstOrDefault |
|---|---|---|---|---|
| No Matching Rows found | Exception | Default Value | Exception | Default Value |
| 1 Matching row found | Returns The Row | Returns The Row | Returns Row | Returns Row |
| More than 1 matching row found | Exception | Exception | Returns 1st Row | Returns 1st Row |
