Click here to Skip to main content
11,710,943 members (85,362 online)
Click here to Skip to main content

SingleOrDefault and FirstOrDefault Methods in LINQ to SQL

, 14 Jul 2014 CPOL 34.6K 17
Rate this:
Please Sign up or sign in to vote.
This article explains the differences between the SingleOrDefault and FirstOrDefault Methods in LINQ to SQL.

Introduction

The SingleOrDefault() method returns a single specific element of a sequence or default value if that element is not found in the sequence. Whenever you use SingleOrDefault, you clearly state that the query should result in at most a single result.

The FirstOrDefault() method returns a first specific element of a sequence or default value if that element is not found in the sequence. Whenever FirstOrDefault is used, the query can return any amount of results, but you state that you only want the first one.

I create a console application to explain the differences between these methods.

Adding a LINQ to SQL Class

Entity classes are created and stored in LINQ to SQL Classes files (.dbml files). The O/R Designer opens when you open a .dbml file. It is a DataContext class that contains methods and properties for connecting to a database and manipulating the data in the database. The DataContext name corresponds to the name that you provided for the .dbml file

  • Step 1: Right-click on the project in the Solution Explorer, then go to "Add" and click on "Class.."
  • Step 2: Choose "LINQ to SQL Classes" from the list and give the name "User" for the dbml name. After that, click on "Add".
  • Step 3: Drag the User table from the database in the Server Explorer and drop onto the O/R Designer surface of the "User.dbml" file.

    Table in User.dbml file

    Figure 1.1: Table in User.dbml file

Using the Code

We will look at various scenarios for FirstOrDefault and SingleOrDefault methods. So let's see each one by one.

If Sequence Returns 0 Elements

If a sequence returns 0 elements, then the FirstOrDefault method sets the default value for the type. Suppose we are retrieving a specific record from the database table and that specific record does not exist in the database table, then the sequence contains 0 elements and the FirstOrDefault method sets a null value for that object.

In the following code snippet, the sequence does not contain an element, then the FirstOrDefault method sets a null value for the object type and the catch block executes. We define NullReferenceException as a parameter in the catch block that represents that this catch block executes when a null value is set for an object and data is being retrieved from that object.

using System;
using System.Linq;

namespace FirstOrSingle
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UserDataContext dataContext = new UserDataContext())
            {
                var users = from user in dataContext.Users
                            where user.Id==5
                           select user;

                var firstUser = users.FirstOrDefault();
                try
                {
                    Console.WriteLine("Name : {0} Age : {1}", firstUser.Name, firstUser.Age);             
                }
                catch(NullReferenceException ex)
                {              
                  Console.WriteLine("No Record Found");
                  Console.Write(ex.Message);            
                }
                Console.ReadKey();
            }
        }
    }
}

After running the application, we get the result that represents that the catch block executes due to retrieving a value from the object that has a null value.

Output of program when sequence contains zero element

Figure 1.2: Output of program when sequence contains zero element

We can implement the SingleOrDefault method on the same sequence that does not contain any elements; it then also sets a null value to the object.

using System;
using System.Linq;

namespace FirstOrSingle
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UserDataContext dataContext = new UserDataContext())
            {
                var users = from user in dataContext.Users
                            where user.Id==5
                           select user;

                var singleUser = users.SingleOrDefault();          

                try
                {
                    Console.WriteLine("Name : {0} Age : {1}", singleUser.Name, singleUser.Age);
                }
                catch(NullReferenceException ex)
                {                  

                    Console.WriteLine("No Record Found");
                    Console.Write(ex.Message);  
                }
                Console.ReadKey();
            }
        }
    }
}

We get the same result as in Figure 1.2 that represents that there is not a difference between the FirstOrDefault and SingleOrDefault methods when the sequence does not contain any elements.

If Sequence Contains 1 Element

If the sequence contains only 1 element, then there is no difference between the FirstOrDefault and SingleOrDefault methods, both will return the same one element. For example:

using System;
using System.Linq; 

namespace FirstOrSingle
{
    class Program
    {
        static void Main(string[] args)
        {
            using (UserDataContext dataContext = new UserDataContext())
            {
                var users = from user in dataContext.Users
                            where user.Id==4
                           select user;

                var firstUser = users.FirstOrDefault();
                var singleUser = users.SingleOrDefault();
                try
                {

                    Console.WriteLine("Result By FirstOrDefault Method");
                    Console.WriteLine("Name : {0} Age : {1}", firstUser.Name, firstUser.Age);
                    Console.WriteLine("======================================================");
                    Console.WriteLine("Result By SingleOrDefault Method");
                    Console.WriteLine("Name : {0} Age : {1}", singleUser.Name, singleUser.Age); 
                }
                catch(NullReferenceException ex)
                {    
                    Console.Write(ex.Message);  
                }
                Console.ReadKey();
            }
        }
    }
}

After running the application, we get the result that represents the same result for both methods.

Output of program when sequence contains one element

Figure 1.3: Output of program when sequence contains one element

If the Sequence Contains Many Elements

If the sequence contains many elements, then the FirstOrDefault method returns only one element that is on top in the sequence. The following code snippet shows that the top one element is gotten from this sequence using the FirstOrDefault method.

using System;
using System.Linq;

namespace FirstOrSingle
{
    class Program
    {
        static void Main(string[] args)
        {

            using (UserDataContext dataContext = new UserDataContext())
            {
                var users = from user in dataContext.Users 
                           select user;
                try
                {
                    var firstUser = users.FirstOrDefault();
                    Console.WriteLine("Name : {0} Age : {1}", firstUser.Name, firstUser.Age); 
                }
                catch(InvalidOperationException ex)
                {   
                    Console.Write(ex.Message); 
                }
                Console.ReadKey();
            }
        }
    }
} 

After running the application, we get the top first element from the sequence.

one element from the sequence when the sequence contains more than one

Figure 1.4: one element from the sequence when the sequence contains more than one

When the sequence contains many elements and uses the SingleOrDefault method to get an element, then we get an exception that represents that sequence containing more than one element. See the following code snippet:

using System;
using System.Linq; 

namespace FirstOrSingle
{
    class Program
    {

        static void Main(string[] args)
        {
            using (UserDataContext dataContext = new UserDataContext())
            {
                var users = from user in dataContext.Users 
                           select user;              

                try
                {
                    var singleUser = users.SingleOrDefault();
                    Console.WriteLine("Name : {0} Age : {1}", singleUser.Name, singleUser.Age);
                }

                catch(InvalidOperationException ex)
                {
                    Console.Write(ex.Message);  
                }
                Console.ReadKey();
            }
        }
    }
} 

After running the application, we get the result that shows an exception which was created by the SingleOrDefault method due to the sequence that contains more than one element.

 Exception when sequence contains more than one element

Figure 1.5: Exception when sequence contains more than one element

Conclusion

If you want an exception to be thrown if the sequence contains more than one element, then use SingleOrDefault. If you always want one record no matter what the sequence contains, use FirstOrDefault.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sandeep Singh Shekhawat
Software Developer
India India
Sandeep Singh Shekhawat is a Software Developer. He is awarded for C# Corner MVP(2013,2014 and 2015) and CodeProject MVP (2015). His three articles at CodeProject are prize wining in article competition. His more than 10 articles has been selected Article of the Day at Microsoft ASP.NET (http://www.asp.net/) community.

You may also be interested in...

Comments and Discussions

 
QuestionWhat is the LINQ query for getting all the data Pin
Mateen Kadwaikar28-Jan-15 2:09
memberMateen Kadwaikar28-Jan-15 2:09 
QuestionWell done and very good explanation Pin
Salam Y. ELIAS2-Nov-14 1:23
memberSalam Y. ELIAS2-Nov-14 1:23 
AnswerRe: Well done and very good explanation Pin
Sandeep Singh Shekhawat2-Nov-14 14:20
professionalSandeep Singh Shekhawat2-Nov-14 14:20 
QuestionSandeep Pin
Member 800608921-Jul-14 5:24
memberMember 800608921-Jul-14 5:24 
AnswerRe: Sandeep Pin
Sandeep Singh Shekhawat21-Jul-14 5:59
professionalSandeep Singh Shekhawat21-Jul-14 5:59 
GeneralMy vote of 1 Pin
Samer Abu Rabie15-Jul-14 23:53
professionalSamer Abu Rabie15-Jul-14 23:53 
GeneralRe: My vote of 1 Pin
Sandeep Singh Shekhawat16-Jul-14 0:43
professionalSandeep Singh Shekhawat16-Jul-14 0:43 
GeneralMy vote of 5 Pin
Akhil Mittal14-Jul-14 20:56
mvpAkhil Mittal14-Jul-14 20:56 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat30-Aug-14 19:16
professionalSandeep Singh Shekhawat30-Aug-14 19:16 
QuestionMy vote of 5 Pin
Yogesh Kumar Tyagi14-Jul-14 18:36
professionalYogesh Kumar Tyagi14-Jul-14 18:36 
AnswerRe: My vote of 5 Pin
Sandeep Singh Shekhawat30-Aug-14 19:16
professionalSandeep Singh Shekhawat30-Aug-14 19:16 
GeneralRe: My vote of 5 Pin
Yogesh Kumar Tyagi31-Aug-14 18:12
professionalYogesh Kumar Tyagi31-Aug-14 18:12 
GeneralMy vote of 5 Pin
kayesharun14-Jul-14 17:14
memberkayesharun14-Jul-14 17:14 
GeneralRe: My vote of 5 Pin
Sandeep Singh Shekhawat14-Jul-14 17:25
professionalSandeep Singh Shekhawat14-Jul-14 17:25 
QuestionNo no no. Pin
Pete O'Hanlon20-Feb-14 9:00
protectorPete O'Hanlon20-Feb-14 9:00 
AnswerRe: No no no. Pin
Sandeep Singh Shekhawat20-Feb-14 16:54
professionalSandeep Singh Shekhawat20-Feb-14 16:54 
QuestionIsn't it easier to say... Pin
Paulo Zemek20-Feb-14 9:00
professionalPaulo Zemek20-Feb-14 9:00 
AnswerRe: Isn't it easier to say... Pin
Sandeep Singh Shekhawat14-Jul-14 6:37
professionalSandeep Singh Shekhawat14-Jul-14 6:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150819.1 | Last Updated 14 Jul 2014
Article Copyright 2014 by Sandeep Singh Shekhawat
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid