In this article let us look at the Single, SingleOrDefault, First, FirstOrDefault methods in EF Core. 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.
Database:
The Database for this tutorial is taken from the chinook database.Source Code:
The source code of this project available in GitHub. It also contains the script of the database
Table of Contents
Single or SingleOrDefault
We use Single or SingleOrDefault in EF Core, when we expect only a single row to exist in the table. If the query returns 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 i.e null.
Single
The following example covers all three scenarios for Single Method.
Single Record Exists
Single method querying for the Track Bohemian Rhapsody which exists in the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | public void singleRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Bohemian Rhapsody").Single(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown "+ ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
No Record Exists
Single method querying for the track Love Kills, which does not exist in the database. The code throws an Exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void singleRecordNotExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Love Kills").Single(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
Multiple Record Exists
Single method querying for the tracks by the composer Mercury, Freddie. Since the query returns multiple records, It results in an exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void singleMultipeRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Composer=="Mercury, Freddie").Single(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
SingleOrDefault
The SingleOrDefault returns the null record if there are no matching records in the database. In all other cases it behaves exactly same as Single Method.
Single Record Exists
Returns the record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | public void singleOrDefaultRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Bohemian Rhapsody").SingleOrDefault(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
No Record Exists
Returns null. Does not throw exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void SingleOrDefaultRecordNotExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Love Kills").SingleOrDefault(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
Multiple Record Exists
This results in an exception.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void SingleOrDefaultMultipeRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Composer == "Mercury, Freddie").SingleOrDefault(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
First or FirstOrDefault
Use First or FirstOrDefault 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 which is null.
First
Single Record Exists
Returns the record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void firstRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Bohemian Rhapsody").First(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
No Record Exists
No Record exists. Throws an exception
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void firstRecordNotExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Love Kills").First(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
Multiple Record Exists
Multiple Record exists. Returns the first record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void firstMultipeRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Composer == "Mercury, Freddie").First(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
FirstOrDefault
The FirstOrDefault returns the null record if there are no matching records in the database. In all other cases it behaves exactly same as First Method.
Single Record Exists
Returns the record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void firstOrDefaultRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Bohemian Rhapsody").FirstOrDefault(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
No Record Exists
Returns null. Does not throw exception
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void firstOrDefaultRecordNotExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Name == "Love Kills").FirstOrDefault(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } |
Multiple Record Exists
Returns the first record.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | public void firstOrDefaultMultipeRecordExists() { using (ChinookContext db = new ChinookContext()) { try { var track = db.Track.Where(f => f.Composer == "Mercury, Freddie").FirstOrDefault(); if (track != null) { Console.WriteLine("{0} {1} ", track.Name, track.Composer); } else { Console.WriteLine("Track not found"); } } catch (Exception ex) { Console.WriteLine("Track not found but exception thrown " + ex.Message); } } Console.WriteLine("Press any key to continue"); Console.ReadKey(); } } |
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 |
References
Read More
- Querying in Entity Framework Core
- First, FirstOrDefault, Single, SingleOrDefault in EF Core
- EF Core Find Method
- EF Core Projection Queries
- EF Core Join Query
- Eager Loading using Include & ThenInclude in EF Core
- SelectMany in EF Core
- Explicit Loading in EF Core
- Lazy Loading in EF Core
Summary
The Single, SingleOrDefault, First, FirstOrDefault in EF Core returns a single record. The methods starting with Single expects the database to contain only a one record else throws an exception. The Methods starring with First always returns the first row if it encounters more than one record. The methods ending with Default returns null if no records are found while others throws an exception.