Part 14 - How to directly execute sql queries using Linq to SQL

Suggested Videos
Part 11 - Single table inheritance in linq to sql
Part 12 - Single table inheritance - saving to database
Part 13 - Compiled queries in Linq to SQL



So far in this video series, we have not written any sql queries to retrieve data using linq to sql. We write a linq query, and the underlying LINQ to SQL provider dynamically generates the T-SQL required. If we want to have complete over the SQL that is executed against the database, then we can use ExecuteQuery() or ExecuteCommand() methods of the DataContext class. 



In this video we will discuss how to use ExecuteQuery() and ExecuteCommand() methods to directly execute SQL queries. Let us understand this with an example.

Step 1 : Create Students table 

Create Table Students

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50)

)

GO

Insert into Students values ('Mark', 'Hastings', 'Male')

Insert into Students values ('Steve', 'Pound', 'Male')

Insert into Students values ('Ben', 'Hoskins', 'Male')

Insert into Students values ('Philip', 'Hastings', 'Male')

Insert into Students values ('Mary', 'Lambeth', 'Female')

GO

Step 2 : Create a new Console application. Name it Demo.

Step 3 : Add a new LINQ to SQL class to the console project. Drag and drop Students table from Server Explorer onto LINQ to SQL class designer file.

Step 4 : 
The following query retrieves all Male students from Students table

Select * from Students where Gender='Male'

Use the following code to execute the above query using DataContext object's ExecuteQuery() method

using (SampleDataContext dbContext = new SampleDataContext())

{

    IEnumerable<Student> students = dbContext.ExecuteQuery<Student>(

        "Select * from Students where Gender='Male'");

    foreach (Student student in students)

    {

        Console.WriteLine(student.FirstName + " " + student.LastName);

    }

}

In the above example, we have hard-coded Gender. If you want to parameterize the query, then use the following syntax.

using (SampleDataContext dbContext = new SampleDataContext())

{

    IEnumerable<Student> students = dbContext.ExecuteQuery<Student>(

        "Select * from Students where Gender={0}", "Male");

    foreach (Student student in students)

    {

        Console.WriteLine(student.FirstName + " " + student.LastName);

    }

}

If you want to perform an Insert, Update or Delete then use ExecuteCommand() method. This method returns the number of rows affected by the query. The following code updates all 4 male student's gender to Female.

using (SampleDataContext dbContext = new SampleDataContext())

{

    int count = dbContext.ExecuteCommand(

        "Update Students set Gender='Female' where Gender='Male'");

    Console.WriteLine("Rows Updated = {0}", count);

}

Is it a good practice to use ExecuteQuery() or ExecuteCommand() methods to directly execute SQL queries?
No, use these methods only if absolutely necessary, that is when LINQ to SQL is not able to generate optimal SQL queries that you are expecting. In most of the cases LINQ to SQL does a pretty decent job in generating optimal sql queries. When we use ExecuteQuery() or ExecuteCommand() methods we loose the expressive power of LINQ and the advantage of having strongly-typed variables in queries.

What is the difference between ExecuteQuery and ExecuteCommand methods in linq
ExecuteQuery is used to perform a Select, while ExecuteCommand is used to perform Insert, Update, Delete or for calling a stored procedure.