Learning LINQ to SQL

I've decided to spend a little time these next few evenings coming up to speed on LINQ (Language-Integrated Query) and writing about my experience with it. Let's see what sorts of problems I run into. I'm using as reference the 119 page LINQ to SQL: .NET Language-Integrated Query for Relational Data in order to jump start my learning. I hope that my SQL and C# experience will give me a head start and keep me moving. But, I have done nothing yet with the Northwind sample database (the database used in the white paper) in any of my projects or tests. I am not even sure where to get it yet. Ha, ha, Noobie on the loose, let me at it...

Let's Create some Entity Classes. The white paper starts out with creating entity classes. Granted, I'm starting in the "Quick Tour" section of the white paper, so I expect to find some detail ommisions. I'll take my chances. So, I jump right into the declaration of the Customer class.

    [Table(Name="Customers")]
    public class Customer
    {
        public string CustomerID;
        public string City;
    }

Well, by looking at it, I can see I am probably missing a few things already. I compile and sure enough, the errors tell me I need to reference something. My hunch is I need something like "using System.Data.Linq;", but since intellisense doesn't show it, I look under References instead and find "System.Data.Linq". I add it and now the intellisense is happy. I attempt to compile again.

The next stumbling block is the error that System.Data.Linq.Table<TEntity> requires a type argument. Hmm, let's hit MSDN and search for TableAttribute. Why not "Table<TEntity>" you ask? That's because the only "Table" reference in my program is [Table...] and I know this syntax is shorthand for [TableAttribute...]. Now, I could go looking for "Table<TEntity>", but the real problem is TableAttribute is not being found in one of my references, so the compiler is finding something else. I'll figure out the something else later. Right now, I'm on a mission. The search brings up "TableAttribute Class (System.Data.Linq.Mapping)". As I thought, I was missing another reference. I add the "using System.Data.Linq.Mapping;" and I'm back on track.

After adding column attributes and checking out my compile again, I next come to "The DataContext". This part of the white paper calls for "northwnd.mdf". Well, I've never used the Northwinds example database, so I have no clue where to find the file. I start searching the hard drive, and of course, I hit the search engines again searching for "northwnd.mdf sample file". Look's like someone else was having trouble finding it, too. The search rendered "Can't find Northwnd.mdf example database", so I read up. But my search of the hard drive also turns up a copy in the LinqSamples/Data directory. So, I use that (actually, I extract it from the CSharpSamples.zip file in c:\<install-location>\Samples\1033 ).

With database file in place and all code adjusted for the correct path, I compile and run. The next error I hit is an exception when I interate through my query results. The code up to this point looks like this...

        private void button1_Click(object sender, EventArgs e)
        {
            // DataContext takes a connection string 
            DataContext db = new DataContext(@"c:\LinqSamples\data\northwnd.mdf");
            // Get a typed table to run queries
            Table Customers = db.GetTable();
            // Query for customers from London
            var q =
               from c in Customers
               where c.City == "London"
               select c;
            foreach (var cust in q)
                Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);
        }
    ...

    [Table(Name="Customers")]
    public class Customer
    {
        [Column(IsPrimaryKey=true)]
        public string CustomerID;
        [Column]
        public string City;
    }

The exception is occuring when I step through the code and land on "q" in...

foreach(var cust in q)

... That clues me in that database connections are not made until later than I expect, and some of this new LINQ syntax does not run immediately. Interesting. The exception says effectively that I don't have a SqlServer running, and upon checking my configuration, I find my SqlServer Express edition is stopped and set to start up manually. So, I turn it on and run my first attempt at a LINQ program. My output is ...

id = AROUT, City = London
id = BSBEV, City = London
id = CONSH, City = London
id = EASTC, City = London
id = NORTS, City = London
id = SEVES, City = London

Wow, that wasn't too painful. I have a working LINQ program. I recognize the white paper was not intended to be a tutorial, so I'm pleased with how few are the problems I've run into thus far. Maybe I should have started with a tutorial, but the level of detail here is perfect for someone like myself who is after a really rapid ramp-up, and who has enough experience to solve some of the basic problems on his own.

The DataContext is a little too loose for the author of the article, so I follow along by deriving the Northwind class from the DataContext and updating my code.

    public partial class Northwind : DataContext
    {
        public Table Customers;
        public Table Orders;
        public Northwind(string connection)
            : base(connection) {}
    }

    ...

        private void button1_Click(object sender, EventArgs e)
        {
            Northwind db = new Northwind(@"c:\LinqSamples\data\northwnd.mdf");
            // Query for customers from London
            var q =
               from c in db.Customers
               where c.City == "London"
               select c;
            foreach (var cust in q)
                Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);
        }
    ...

    [Table(Name="Customers")]
    public class Customer
    {
        [Column(IsPrimaryKey=true)]
        public string CustomerID;
        [Column]
        public string City;
    }
    [Table(Name = "Orders")]
    public class Order
    {
    }

So, with this declaration (and having to define the Order class on my own), I can now do without the DataContext and the GetTable<Customer>() call. I compile again and run it successfully. So, I proceed to "Defining Relationships".

Here the example code has an error. It applies the ColumnAttribute with an invalid parameter "Id". I believe the correct parameter to be "IsPrimaryKey", so I use that instead. I also find that here is where the Order class is defined, so I use this definition and fix the ColumnAttribute on OrderID as well (change "Id" to "IsPrimaryKey"). It all compiles, but I am concerned that neither "_Orders" nor "_Customer" are ever instantiated. Well, I don't know how "Storage=" works, so I look at it once more cautiously before moving on.

(to be continued...)