|
Introduction
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.
Background
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.
What is LINQ to SQL
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.
How to Use LINQ to SQL
Create a New C# Project
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;
//..............
}
Understanding DataContext
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.
Understanding the var Keyword
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();
Querying the Database
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' ");
Insert, Update and Delete Rows from Database
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).
Order of Operations
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();
Enhanced Features
Other LINQ to SQL Options
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.
Manage Conflicts
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).
Points of Interest
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
|
