Contents
This article demonstrates what LINQ to SQL is and how to use its basic functionality. I found this new feature amazing because it really simplifies a developer's debugging work and offers many new ways of coding applications.
For this article, I have used the Northwind database sample from Microsoft. The database is included in the ZIP file or can be found on the Microsoft website.
The LINQ Project is a codename for a set of extensions to the .NET Framework that encompasses language-integrated query, set and transform operations. It extends C# and Visual Basic with native language syntax for queries. It also provides class libraries to take advantage of these capabilities. For more general details, refer to the Microsoft LINQ Project page.
LINQ to SQL is a new system which allows you to easily map tables, views and stored procedures from your SQL server. Moreover, LINQ to SQL helps developers in mapping and requesting a database with its simple and SQL-like language. It is not the ADO.NET replacement, but more an extension that provides new features.
In this section I'll show you how to use LINQ to SQL from the start, at project creation.
- Create a new Windows form application with Visual C# 2008.
- Make a new "Data Connection" with the SQL Server 2005 northwind.mdf file.
- Add a new item to your project and choose "LINQ to SQL Classes." Name it NorthwindDataClasses.dbml. This new DBML file will contain the mapping of SQL Server tables into C# classes.
The Object Relational Designer is a design surface that maps a database table into a C# class. To do that, just drag and drop tables from the database explorer into the designer. The designer automatically displays the tables in a UML way and represents the relationship between them. For example, I have dragged and dropped the four tables Customers, Order, Order_Detail and Product, as shown below:
In NorthwindDataClasses.designer.cs (under NorthwindDataClasses.dbml from the project explorer), you will find definitions for all classes corresponding to tables like this:
| SQL | LINQ to SQL O/R Designer |
| Table name | Class name |
| Columns | Attributes |
| Relations | EntitySet and EntityRef |
| Stored procedures | Methods |
[Table(Name="dbo.Customers")]
public partial class Customer : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs =
new PropertyChangingEventArgs(String.Empty);
private string _CustomerID;
private string _CompanyName;
private string _ContactName;
private string _ContactTitle;
private string _Address;
private string _City;
private string _Region;
private string _PostalCode;
private string _Country;
private string _Phone;
private string _Fax;
private EntitySet<Order> _Orders;
}
dataContext is a class that gives direct access to C# classes, database connections, etc. This class is generated when the designer is saved. For a file named NorthwindDataClasses.dbml, the class NorthwindDataClassesDataContext is automatically generated. It contains the definition of tables and stored procedures.
This keyword is used when you do not know the type of the variable. Visual Studio 2008 automatically chooses the appropriate type of data and so does IntelliSense!
Examples:
var anInteger = 5;
var aString = "a string";
var unknown_type = new MyClass();
Once the database is modeled through the designer, it can easily be used to make queries.
Query the Customers from Database
NorthwindDataClassesDataContext dc = new NorthwindDataClassesDataContext();
var customers =
from c in dc.Customers
select c;
dataGridResult.DataSource = customers.ToList();
Query the Customers with a Simple Statement
var customers_from_uk =
from c in dc.Customers
where c.Country == "UK"
select c;
Query Specified Columns Only, Returning a Collection of a Specified Class
Use a very simple class definition.
public class CMyClassTwoStrings
{
public CMyClassTwoStrings(string name, string country)
{
m_name = name;
m_country = country;
}
public string m_name;
public string m_country;
}
For example:
var customers_name_starts_a_2col_in_specific_class =
from c in dc.Customers
where c.ContactName.StartsWith("A")
select new CMyClassTwoStrings (c.ContactName, c.Country );
foreach (CMyClassTwoStrings a in customers_name_starts_a_2col_in_specific_class)
Console.WriteLine(a.m_name + " " + a.m_country);
Query Specified Columns Only, Returning a Collection of an Undefined Class
var customers_name_starts_a_2col_in_anonymous_class =
from c in dc.Customers
where c.ContactName.StartsWith("A")
select new {
Name = c.ContactName,
Country = c.Country
};
foreach (var a in customers_name_starts_a_2col_in_anonymous_class)
Console.WriteLine(a.Name + " " + a.Country);
This example demonstrates how to use an anonymous class that is (in this example) composed of two strings. The aim of this feature is to create a new class for temporary storage that the developer does not want (or need) to declare. It may be useful in some cases where the declaration of class is used only for storage.
For example, in the sample 4a, the class CMyClassTwoStrings is used only to create the interface between the query engine and the output in the console. It is not used anywhere else and is a loss of time. This new way of writing enables the developer to create temporary classes with an unlimited number of attributes of any type. Every attribute is named, either by specifying the name with Name = c.ContactName or by leaving the attribute without, i.e. Name =. IntelliSense also works with anonymous classes!
Query Multiple Tables
var customers_and_product =
from c in dc.Customers
from p in dc.Products
where c.ContactName.StartsWith("A") && p.ProductName.StartsWith("P")
select new { Name = c.ContactName, Product = p.ProductName };
The resulting collection is the cross product between all contact names starting with "A" and all products starting with "P."
Query with Tables Joined
var customers_and_orders =
from c in dc.Customers
from p in dc.Orders
where c.CustomerID == p.CustomerID
select new { c.ContactName, p.OrderID};
This example demonstrates how to specify the relation between tables' joins on an attribute.
Query with Tables Joined through entityref
var customers_and_orders_entityref =
from or in dc.Orders
select new {
Name = or.Customer.ContactName,
OrderId = or.OrderID,
OrderDate = or.OrderDate
};
In this example, the entityref property is used. The class orders have an attribute named Customer that refers to the customer who realizes the order. It is just a pointer to one instance of the class Customer. This attribute gives us direct access to customer properties. The advantage of this feature is that the developer does not need to know exactly how tables are joined and access to attached data is immediate.
Query in the Old Way: with SQL as String
As you may want to execute SQL that is not yet supported by LINQ to SQL, a way to execute SQL queries in the old way is available.
dc.ExecuteCommand("UPDATE Customers SET PostalCode='05024' where CustomerId='ALFKI' ");
LINQ to SQL provides a new way of managing data into database. The three SQL statements INSERT, DELETE and UPDATE are implemented, but using them is not visible.
Update Statement
var customers_in_paris =
from c in dc.Customers
where c.City.StartsWith("Paris")
select c;
foreach (var cust in customers_in_paris)
cust.City = "PARIS";
dc.SubmitChanges();
To make modifications to a database, just modify any relevant object properties and call the method SubmitChanges().
Insert Statement
To insert a new entry into the database, you just have to create an instance of a C# class and Attach it to the associated table.
Product newProduct = new Product();
newProduct.ProductName = "RC helicopter";
dc.Products.InsertOnSubmit(newProduct);
dc.SubmitChanges();
Delete Statement
Deleting data is quite easy. When requesting your database, give a collection of data. Then just call DeleteOnSubmit (or DeleteAllOnSubmit) to delete the specified items.
var products_to_delete =
from p in dc.Products
where p.ProductName.Contains("helicopter")
select p;
dc.Products.DeleteAllOnSubmit(products_to_delete);
dc.SubmitChanges();
IntelliSense works into the query definition and can increase developer productivity. It's very interesting because it pops up on DataContext, tables and attributes. In this first example, IntelliSense shows the list of tables mapped from the database, the connection instance and a lot of other properties.
For a table, the list contains all of its columns:
For an attribute, it will display methods and properties depending on the type (string, integer, etc).
To use LINQ to SQL, a developer must know exactly when a query is executed. Indeed, LINQ to SQL is very powerful because the query is executed when it's required, but not at definition! In the first sample, we have this code:
var customers =
from c in dc.Customers
select c;
The query is not yet executed; it is just compiled and analysed. In fact, the query is run when the code makes an access to the customer variable, like here:
dataGridResult.DataSource = customers.ToList();
LINQ to SQL supports deferred loading options. This functionality allows a user to modify query engine behaviour when retrieving data. One of them is the deferred loading that will load all the data of a query. As an example, a query on the Order table gives you entry to the customer properties by entityref. If Datacontext.DeferredLoadingEnabled is set at true (default) then the Customer attribute will be loaded when an access to the Order entry is made. Otherwise (when at false), it is not loaded. This option helps a developer when optimizing requests, data size and time for querying. There is a good example about that here.
When the function SubmitChanges() is executed, it starts by verifying if there is no conflict that occurs by an external modification. For a server/client application, the application must take conflicts into account in case multiple clients access the database at the same time. To implement conflict resolution, SubmitChanges() generates a System.Data.LINQ.ChangeConflictException exception. The DataContext instance gives details about conflicts to know why exactly they throw. I wrote a basic conflict resolution, but I will not give the full details of all other possibilities because I think it should be an entire article.
try{
var customers_in_paris_conflict =
from c in dc.Customers
where c.City.StartsWith("Paris")
select c;
foreach (var cust in customers_in_paris_conflict)
cust.City = "PARIS";
dc.SubmitChanges();
}
catch (System.Data.LINQ.ChangeConflictException)
{
foreach (ObjectChangeConflict prob in dc.ChangeConflicts)
{
prob.Resolve(RefreshMode.KeepChanges);
}
}
If you want more details about conflict resolution, I suggest you to refer to this page (in VB).
As a conclusion to this article, I summarize the important points of LINQ to SQL:
- LINQ to SQL is a query language
- Query syntax is verified at build (not at runtime like old SQL queries)
- IntelliSense works with all objects of LINQ to SQL
- Making queries is quite easy (select, insert, update and delete)
- Manage tables' PK/FK relationships
- Databases are automatically mapped to C# classes and queries return a collection of C# class instances
- Conflict detection and resolution
