LINQ - Executing Custom SQL Expressions

Hi,

This blog post summarizes the overview of LINQ.

Below are the new features been introduced in Orcas

§ Automatic Properties

§ Object Initializers

§ Collection Initializers

§ Extesnion Methods

§ Lambda Expressions - p => expressions

§ Query Syntax

§ Anonymous Types

§ Var Keyword

These language features help in making query against relational data. This overall querying programming model is called as "LINQ" - which stands for .NET Language Integrated Query.

Executing Custom SQL Expressions

When we use the LINQ code like below to Insert/Update/Delete - The LINQ to SQL ORM automatically generates and executes dynamic SQL to perform queries, updates, inserts and deletes against a database.

db.Products.Add(product);

db.SubmitChanges(); // Insert

NorthwindDataContext db = new NorthwindDataContext();

MyProduct product = db.MyProducts.Single(p => p.ProductName == "Chai");

product.UnitPrice = 99;

db.SubmitChanges(); // Update

db.Products.Remove(product);

db.SubmitChanges(); // Delete

However, for advanced scenarios, or cases where you want total control over the SQL query/command executed, you also have the two abilities as below.

· Customize the ORM to use either SPROCs, or

· Your own custom SQL Expressions, instead.

This provides you with a great deal of flexibility when building and extending your data access layer.

Why Custom SQL Expressions

· If you do not want to use SP and need total control over the SQL queries then best is to use the Custom SQL Expressions used by LINQ to SQL.

· We can use custom SQL expressions to populate the LINQ to SQL data model classes, as well as perform insert, updates, and deletes.

Custom SQL Expressions for Retreival Query:

The LINQ code to retrieve the products is as below:

NorthwindDataContext db = new NorthwindDataContext();

var products = from p in db.MyProducts

where p.Category.CategoryName == "Beverages"

select p;

To achieve the above with cutsom sql expression, you’ll be write the code in a separate partial DataContext class, as below:

public partial class NorthwindDataContext : System.Data.Linq.DataContext

{

public IEnumerable<MyProduct> GetProductByCategory(int CategoryId)

{

return ExecuteQuery<MyProduct>(@"select * from Products where Products.CategoryID = {0}", CategoryId);

}

}

This can be used as :

NorthwindDataContext db = new NorthwindDataContext();

var products = db.GetProductByCategory(1);

Using the ExecuteQuery Method

· Using it we can execute any raw SQL we want against the database (including custom JOINs across multiple tables).

· The ExecuteQuery method takes a SQL query expression as an argument, along with a set of parameter values that we can use to optionally substitute values into the query.

· What makes the ExecuteQuery method really useful is that it allows you to specify how you want the return values of your SQL expression to be typed. You can do this either by passing a Type-Object as a parameter to the method, or by using a Generic-Based Version of the method.

Custom SQL Expressions for Inserts/Updates/Deletes

· In addition to using custom SQL expressions for queries, we can also execute them to perform custom Insert/Update/Delete logic.

· This can accomplish this by creating the appropriate partial Insert/Update/Delete method for the entity we want to change in a partial class on our DataContext.

public partial class NorthwindDataContext : System.Data.Linq.DataContext

{

partial void DeleteProduct(int ProductId)

{

ExecuteCommand(@"Delete from Products where ProductID = {0}", ProductId);

}

}

· Now if we write the below code to remove a specific Product instance from our database, LINQ to SQL will call the above DeleteProduct method - which will cause our custom SQL to execute IN PLACE OF THE DEFAULT DYNAMIC SQL that LINQ to SQL would otherwise use.

NorthwindDataContext db = new NorthwindDataContext();

MyProduct product = db.MyProducts.Single(p => p.ProductName == "Chai");

db.MyProducts.Remove(product);

db.SubmitChanges();

Custom SQL Expressions and Object Tracking for Updates

· By default when you retrieve a data model object using LINQ to SQL, it will track all changes and updates you make to it. If you call the "SubmitChanges()" method on the DataContext class, it will then transactionally persist all of the updates back to the database. I cover this in more depth in Part 4 of this LINQ to SQL series.

· One of the cool features of the ExecuteQuery() method is that it can fully participate in this object tracking and update model. i.e the below code will work fine.

NorthwindDataContext db = new NorthwindDataContext();

var products = db.GetProductByCategory(1);

foreach (MyProduct product in products)

{

product.UnitPrice = 6;

}

db.SubmitChanges();

Custom SQL Expressions with Custom Classes

Follow reference link.

Reference: http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx

Hope this helps.

Thanks & Regards,

Arun Manglick || Tech Lead