Compile Queries And Execute SQL Queries Using LINQ To SQL

In this article, we will see

• Compiled queries in LINQ to SQL and
• How to execute SQL queries, using LINQ to SQL.

We will see how to use compiling queries to improve the performance.

Let’s start,

First, let’s understand what happens when a LINQ query is issued.

using(SampleDataContext dbContext = new SampleDataContext()) {
    Student student = (from s in dbContext.Students where s.ID == 1 select s).Single();
    Console.WriteLine(student.FirstName + " " + student.LastName);
}

The query given above is what we had, which is going to retrieve a single student whose ID is 1. When this LINQ query is issued, LINQ must pass this expression tree and dynamically generate the required T-SQL statements, which will be executed under the underlying database. This parsing happens every time when a LINQ query is issued. This overhead can be removed by compiling a LINQ query.

To compile a LINQ query, we use compiled query class that is present in system.data.linq namespace.

Let's look at an example. First, let's create a table and insert some data into the table, as shown below.

Create Table Students(ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(50))
GO
Insert into Students values('Akshay', 'Phadke', 'Male')
Insert into Students values('Milind', 'Daware', 'Male')
Insert into Students values('Raghvan', 'Nadar', 'Male')
Insert into Students values('Gopal', 'nair', 'Male')
GO

Now, create a blank console application, drag and drop the tables to the designer class, as shown below.

Now, paste the code given above in the main method.

In this class, we have got a LINQ query, which is going to retrieve the student whose ID is 1 and finally, we are printing the student's firstname and lastname. Let’s run the program to make sure that we get the desired result.

Now, this LINQ query is not compiled, which means every time this LINQ query is executed, this must be passed and then T-SQL statements must be dynamically generated on the fly and this parsing takes time.

Thus, this parsing overhead can be removed by compiling this LINQ query. For compiling a LINQ query, we are using CompileLinq query class.

var CompiledStudentQuery = CompiledQuery.Compile()

Thus, we had used compiledquery class.

static void Main(string[] args) {
    var CompiledStudentQuery = CompiledQuery.Compile(
        (SampleDataContext datacontext, int studentID) => (from s in datacontext.Students where s.ID == studentID select s).Single());
    using(SampleDataContext dbContext = new SampleDataContext()) {
        Student sudents = CompiledStudentQuery(dbContext, 1);
        Console.WriteLine(sudents.FirstName + " " + sudents.LastName);
        Console.WriteLine("Press enter to close...");
        Console.ReadLine();
    }
}

In compiled query class, we had used a function of sample data context and pass two arguments as datacontext and student Id. We are passing those functions in student, which receives two parameters, where first is the datacontext and next is ID.

Now, let’s run the app and see the output.

This was all about how to compile the queries in LINQ to SQL. In the next part, we will see how to directly execute SQL queries, using LINQ to SQL.

In this LINQ series, we had not written any SQL queries to retrieve the data, using LINQ to SQL.We write a LINQ query and the underlying LINQ to SQL provider dynamically generates T-SQL required.

If we want to have the complete control over SQL, which is executed against the database, we use ExecuteQuery() or ExecuteCommand() methods of the DataContext class .

Here is an example, where we will create a console Application. Add a LINQ to SQL class file. Drag and drop the respective table.

Now, copy this code in the main method.

static void Main(string[] args) {
    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);
        }
    }
}

We are creating an instance of sample datacontext class and then we are using this ExecuteQuery method to execute the SQL query.

So, this SQL query is going to return all students whose gender is male. Once we have the query result back; LINQ is going to take us to results construct student object which populates those properties and returns those objects back. Notice, the return type of this method is IEnumerable student object.

Hence, we are using for each loop through each student, and printing their firstname and lastname.

Now, let’s quickly run the app and see the output.

Now, the gender value is hard coded. Here, if we want to turn this value into a parameterized query, we can use placeholders, as shown below.

We will pass the second parameter, which is Male value. We can supply as many parameters as we want like firstname and lastname.
Now, test the application

We get the same output: male students.

Thus, we had seen the selected output. Now, if we want to perform insert, update or delete, use ExecuteCommand() method. This method returns the number of the rows affected by the query .

static void Main(string[] args) {
    using(SampleDataContext dbContext = new SampleDataContext()) {
        int count = dbContext.ExecuteCommand("Update Students set Gender='Female' where Gender='Male'");
        Console.WriteLine("Rows Updated = {0}", count);
    }
}

The code given below updates all 15 male students' gender to Female.

Now, when we run this that we should get the rows updated as 15.

Is it a good practice to use ExecuteQuery() or ExecuteCommand() methods to directly execute SQL queries?

No, use these methods only if absolutely necessary, i.e. 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 lose the expressive power of LINQ and the advantage of having strongly-typed variables in the queries.

What is the difference between ExecuteQuery and ExecuteCommand methods in LINQ?

ExecuteQuery is used to perform a Select command, while ExecuteCommand is used to perform Insert, Update, Delete or for calling a stored procedure. 

Recommended Reads for LINQ to SQL,

• http://www.c-sharpcorner.com/article/introduction-to-linq/
• http://www.c-sharpcorner.com/article/how-to-use-linq-to-sql-for-retrieving-data-from-sql-server-database/
• http://www.c-sharpcorner.com/article/crud-operations-using-linq-to-sql/
• http://www.c-sharpcorner.com/article/how-to-view-linq-to-sql-generated-sql-queries-and-using-stored-procedures-with-l/
• http://www.c-sharpcorner.com/article/stored-procedure-with-output-parameters-in-linq-to-sql-and-what-is-sqlmetal/
• http://www.c-sharpcorner.com/article/lazy-loading-and-eager-loading-in-linq-to-sql/
• http://www.c-sharpcorner.com/article/single-table-inheritance-in-linq-to-sql/