LINQ-to-SQL Part 1: "Let me teach you English!"

When using LINQ to SQL you need to create a DBML diagram!

The first step to start with, is clicking "Add New Item".

The item type is now called "LINQ to SQL Classes" in Beta 2 of VS 2008:

Within the diagram designer you can drag tables from the server explorer onto the surface to generate LINQ class for them. If relations exist on the server, they will be automatically created as well.

I did this for my Meeting-Tool. What I got, was this strange class:

MeetingSery ?!? What the hack is this! My table is named MeetingSeries! So I tried it with a sample table named Cars --> renamed to Car.

The LINQ-to-SQL Designer automatically renames plural names to singular names! Unfortunately this doesn't work well for all names ;-)

But - Good news: you can adjust all settings with the property window:

If you save the diagram, a corresponding C# file is generated below the DBML file - called MyFile.designer.cs.

This file includes several classes:

*) The DataContext

This class derives from System.Data.Linq.DataContext and is responsible for managing the database connection. Each table of the database is accessable via a public property of type Table.

The System.Data.Linq.Mapping.DatabaseAttribute specifies the name of the database, the datacontext is responsible for.

*) The table classes

For each table a seperate class is generated, with properties for each table field. The table and the fields are decorated with attributes as well - TableAttribute and ColumnAttribute.

As these classes are partial, it is possible to extend them via partial classes. There are methods defined and called in the auto-generated part, which can be implemented in the custom part to provide custom behavior on special events.

The following statement fetches data from the database:

 SqlConnection connection = new SqlConnection(Settings.Default.NorthwindConnectionString);
using (NorthwindDataContext dc = new NorthwindDataContext(connection))
{
   var orderList = (from order in dc.Orders
           where order.ShipCountry == "Austria" && order.ShipCity == "Vienna"
           select order);

   foreach (Order o in orderList.ToList())
   {
        Console.WriteLine("Order {0} to {1}", o.OrderID, o.ShipName);
   }
}

The new LINQ syntax (var, from where, select) is powered by C# 3.0, a new language compiler as well as the System.Linq.dll of .NET 3.5.

See my previous post on C# 3.0 extensions to learn about the basics!

Wait for part 2 of the LINQ series to learn more details ;-)