What is LINQ to SQL? An Introduction

Our IT group has been using Visual Studio 2005 for over 3 years now. After some time of deliberation and in-depth analysis, our team came to conlusion that it is time to adapt Visual Studio 2008. One reason that excite me for the upgrade is that, our Team will now be able to use the "LINQ to SQL" feature of VS 2008 codename "Orcas". So for the whole week, I will blog about LINQ tips that you readers (speacially beginners) can use on your application. For today's blog, I will provide you a brief introduction to LINQ to SQL.


So what is LINQ?
Language Integrated Query (LINQ, pronounced "link") is a set of extension to the Microsoft .NET Framework component that adds native data querying capabilities to .NET languages. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities. Using LINQ, you can virtually query through any enumerable types of objet such as arrays, control collections and data source.

Example of LINQ
This sample LINQ retrieve all customernames with a length of 5.

C# version

string[] customernames = { "Fryan", "Auric", "Foxtrot",
"Rizza", "Ferdinand", "Tyrone",
"James", "Randy" };

IEnumerable query = from s in customernames
where s.Length == 5
orderby s
select s.ToUpper();

foreach (string item in query)
Console.WriteLine(item);

VB.NET version

Dim query As IEnumerable(Of String) = From s in names _
Where s.Length = 5 _
Order By s _
Select s.ToUpper()

What is LINQ To SQL?
LINQ to SQL is an object relational mapping feature that allows developers to model a relational database using .NET classes. You can then query, insert, update and delete the database using LINQ. LINQ to SQL fully supports transactions, views, and stored procedure.

How to create your LINQ to SQL object model (data context)?
Before we can design our LINQ to SQL object model we must create a new connection in Server Explorer which points to our sample database (in this case i'm using Northwind database).



Next step is to generate our LINQ to SQL object model using LINQ To SQL designer. To do this, right click your solution explorer then click "Add a new item" as shown below.



LINQ to SQL designer will appear. Now drag and drop the tables you want to create a data context from.



The arrows between tables or entities above are associations/relationships between the different entities. These are typically modeled using primary-key/foreign-key relationships in the database.

LINQ to SQL Code Examples
Using the above LINQ to SQL object models, let's try some coding:

Query Product From the Database

using (NorthwindDataContext datacontext = new NorthwindDataContext())
{
//LINQ to SQL query to retrieve all products that are beverages
var products =
from p in datacontext.Products
where p.Category.CategoryName == "Beverages"
select p;
//Bind the LINQ to SQL result to data bound control such as a grid
gridViewProducts.DataSource = products;
gridViewProducts.DataBind();
}

Update a Product in the Database

NorthwindDataContext datacontext = new NorthwindDataContext();

Product product = datacontext.Products.Single(p => p.ProductID == 1);
product.UnitsInStock = 10;

// Submit the changes to the database.
try
{
datacontext.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
// Provide for exceptions.
}

Insert a Product in the Database

NorthwindDataContext datacontext = new NorthwindDataContext();
Product p = new Product();
p.ProductName = "New Product";

// Add the new object to the product collection.
datacontext.Products.InsertOnSubmit(p);

// Submit the change to the database.
try
{
datacontext.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
}

Delete Products from the Database

NorthwindDataContext datacontext = new NorthwindDataContext();
var query = from p in datacontext.Products
where p.ProductName.Contains("New")
select p;

foreach (var prod in query)
{
datacontext.Products.DeleteOnSubmit(prod);
}

try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
// Provide for exceptions.
}

There you have it. An introduction to LINQ to SQL. On my next article, I will discuss How to Call a Stored Procedure using "LINQ to SQL".

For more LINQ coding tips, subscribe now