LINQ To SQL

Introduction

LINQ is one of the most important features in .NET Framework 3.5 (Visual Studio 2008). It's the new way to mapping database tables to classes, and as we know, we call this O/R Mapping. An article on how to write LINQ code quickly is always welcome for beginners, and I think that reading samples is the best way to learn a new technique.

These are samples created while I was learning and using LINQ, and I want to share them now. Hope they will be helpful. I will use Northwind database as a sample, which you can download from the link at the top of this article.

I recommend that you read 101 LINQ Samples if you would like to learn more.


// Basic
// Select * From Products
var query1 = from p in db.Products select p;
// Select ProductID, ProductName, UnitPrice From Products
var query2 = from p in db.Products select new
{
p.ProductID, p.ProductName, p.UnitPrice
};

Note: query2 will create a new class which contains three properties that map the ProductId, ProductName, and UnitPrice.

// Where // Select * From Products Where ProductID = 1
var query3 = from p in db.Products where p.ProductID == 1 select p;

// Select * From Products Where SupplierId =5 and UnitPrice > 20
var query4 = from p in db.Products where p.SupplierID == 5 && p.UnitPrice > 20 select p;

// Select * From Products Where SupplierId =5 Or SupplierId=6
var query5 = from p in db.Products where p.SupplierID == 5 || p.SupplierID == 6 select p;

Note: The condition in the where block is a logical express, a boolean value is returned just like in if().

// Order By
// Select * From Products Order By ProductId
var query6 = from p in db.Products
orderby p.ProductID
select p;

// Select * From Products Order By ProductId Desc
var query7 = from p in db.Products
orderby p.ProductID descending
select p;

// Select * From Products Order By CategoryId, UnitPrice Desc
var query8 = from p in db.Products
orderby p.CategoryID, p.UnitPrice descending
select p;

Note: The default order is ascending, the order by p.ProductID is same as order by p.ProductID ascending, just like in T-SQL.


// Top
// Select Top 10 * From Products
var query9 = (from p in db.Products
select p).Take(10);

// Select Top 1 * From Products
var query10 = (from p in db.Products
select p).Take(1);
// or
var query11 = (from p in db.Products
select p).First();

Note: If it just returns one record, I recommend using First instead of Take(1).

// Top with Order By
// Select Top 10 * From Products Order By ProductId
var query12 = (from p in db.Products
orderby p.ProductID
select p).Take(10);

// Distinct
// Select Distinct CategoryId From Products
var query13 = (from p in db.Products
select p.CategoryID).Distinct();

// Group By
// Select CategoryId, Count(CategoryID) As NewField
// From Products Group By CategoryId
var query14 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Count()
};

// Select CategoryId, Avg(UnitPrice) As NewField From Products Group By CategoryId
var query15 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Average(k => k.UnitPrice)
};

// Select CategoryId, Sum(UnitPrice) As NewField From Products Group By CategoryId
var query16 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Sum(k => k.UnitPrice )
};

// Union
// Select * From Products Where CategoryId =1 union Select *
// From Products Where CategoryId = 2
var query17 = (from p in db.Products
where p.CategoryID == 1
select p).Union(
from m in db.Products
where m.CategoryID == 2
select m
);

// Two tables
// Select A.ProductId, A.ProductName, B.CategoryId, B.CategoryName
// From Products A, Categories B
// Where A.CategoryID = B.CategoryID and A.SupplierId =1
var query18 = from p in db.Products
from m in db.Categories
where p.CategoryID == m.CategoryID && p.SupplierID == 1
select new {
p.ProductID,
p.ProductName,
m.CategoryID,
m.CategoryName
};


Language Integrated Query (LINQ) to SQL

Introducing LINQ to Relational Data

LINQ introduced the concept of a unified data access technique to eliminate challenges of accessing data from a disparity of sources. LINQ has been extended to include the ability to access relational data through LINQ to SQL and LINQ to Entities. LINQ to SQL offers a direct mapping to the Microsoft SQL Server family of databases and allows you to query and manipulate objects associated with database tables. LINQ to Entities offers a more flexible mapping to relational data stored in other databases beyond just SQL Server. You build against a conceptual schema that is mapped to an actual schema. The remainder of this article will concentrate on LINQ to SQL.

LINQ to SQL Object Model

LINQ to SQL allows you to query and manipulate objects associated with database tables. Creating an object model involves creating a typed object that represents the database connection along with classes that map to database entities. The following list of objects are involved:

There are a few different ways to create an object model. The two most straight forward approaches are to use the provided designer within Visual Studio 2008 to auto-generate the objects and mappings or adding attributes to existing objects. For this example I downloaded the Northwind sample database and loaded it in my SQL Server so that I could retrieve data from it for testing. I created a new solution and added a console application. I then added a LINQ to SQL data class to the console application. I used the Server Explorer to setup a new connection to the Northwind database my local SQL Server, and then dragged all of the tables from the Server Explorer on to the design surface for the data class. Finally I right clicked on the design surface and chose "Layout Diagram" to auto arrange all of the newly added tables and relationships. Refer to Figure 1 for an example of the completed design surface


Test Driving LINQ to SQL through Examples

Now that we've covered the background let's use a couple of examples of LINQ to SQL. Our examples will demonstrate the use of the DataContext object along with an example of querying the Northwind sample database using expressions.

Querying Data

The following example code uses LINQ syntax to query data from the database and then loop through it to print it to the console. You'll notice how the query expression syntax is the same as what you would find whether it be LINQ to XML or another form of LINQ. Also notice how the NorthwindDataClassesDataContext object is wrapped within a using construct. The NorthwindDataClassesDataContext is an object that extends the DataContext class and represents a strongly typed connection to our database. The NorthwindDataClassesDataContext is a disposable type, so wrapping it in a using block ensures it is properly disposed after use.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
var results = from customers in context.Customers where customers.City == "London" orderby customers.CompanyName select customers;
foreach (var customer in results)
{
Console.WriteLine("Company is {0} and contact is {1}", customer.CompanyName, customer.ContactName
);
} // Pause to see the output Console.ReadLine(); }

Modifying Data Using the Created Object Model

The previous example focused on the retrieval of data. We'll now examine the basic data manipulation, create, update, and delete operations and how LINQ to SQL makes them simple. The object model used to manipulate the data is the model generated in one of the earlier sections. After each operation we'll use the SubmitChanges() method to save the changes to the database. We'll execute some simple query expressions with the Count() method to verify the modifications were made as desired. As the example will show, you can use instance of objects, make modifications to them, and the changes that you make are tracked in a change set and pushed back to the database.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
// Add a new record and verify it exists through Count
var customer = new Customer()
{
CompanyName = "Drama Cafe", CustomerID = "DRACA", ContactName = "Tom Smith", City = "Beverly Hills", Address = "123 Melrose Place", PostalCode = "90210"
};
context.Customers.InsertOnSubmit(customer);
context.SubmitChanges();
Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count()); // Modify the record and verify it is changed through Count customer.ContactName = "Joe Smith";
context.SubmitChanges();
Console.WriteLine("Number of Joe Smith records: {0}", context.Customers.Where(c => c.ContactName == "Joe Smith").Count()); // Delete a record and verify it is removed through Count context.Customers.DeleteOnSubmit(customer);
context.SubmitChanges();
Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count());
// Pause to see the output Console.ReadLine();

Calling Stored Procedures

The previous retrieve, create, update, and delete operations involved dynamically generated SQL statements. It is also possible to call stored procedures in place of dynamically generated SQL. The sample code below demonstrates how to call stored procedures. The Northwind database has a stored procedure called "Ten Most Expensive Products" we will use for this example. Once again using the Server Explorer navigate to the Stored Procedures in the tree view and drag the stored procedure to the design surface. You'll want to right click on the design surface and select "Show Methods Pane" if it isn't already available. You should now see a method, Ten_Most_Expensive_Products(), that has been created and we'll use in our next code example to get the list of top products and display the prices to the console.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
// Use the ten most expensive products stored procedure
var results = from products in context.Ten_Most_Expensive_Products() select products;
foreach
(var product in results)
{
Console.WriteLine("Product price is {0}", product.UnitPrice);
} // Pause to see the output Console.ReadLine();