Learning LINQ to SQL (Part 2)

In my last post, I had worked through a few section of the white paper "LINQ to SQL: .NET Language-Integrated Query for Relational Data" and had gotten a working LINQ program going.

Everything was going just fine... until now. At the end of the Defining Relationships section, the article leaves me kind'a hanging. It says "there is a tool (described later) that can be used to generate all the necessary definitions..." So, I've got to go back to the TOC to see what this "special tool" is for generating the Entities.

I jump to "The Entity Class Generator Tool" section and start reading. My first order of business is to find the program called SqlMetal. I launch a COMMAND window and type "sqlmetal" and receive the expected "'sqlmetal' is not recognized as an internal or external command." error. Hey, it was worth a try! So, I next use the old stand-by "cd \" then "dir /s sqlmetal.*". Yup, there it is under the Microsoft SDKs (C:\Program Files\Microsoft SDKs\WIndows\v6.0A\bin).

So, I add the path of the tool to my User PATH variable. I change directories to my project directory and run the command...

SqlMetal /server:.\SQLExpress /database:Northwind /pluralize /namespace:nwind /code:Northwind.cs

SqlMetal reports an error...

Error : Cannot open database "Northwind" requested by the login. The login failed. Login failed for user XALNIX-DEVBOX\Les

To solve this problem, I launch SQL Server Management Studio Express and see why the database won't log me in given my program is working and accessing the database. Once in Management Studio, I note that there is no Northwind database, of course. My program accesses the database by filename. So I attach the Northwind database file and change the database name from the filename to Northwind. Now the SqlMetal runs and produces a file called Northwind.cs.

By the way, if you need the SQL Server Management Studio Express, just follow the link to Microsoft's download page.

Now I can get back to the Learning some LINQ. Before I made the excursion into SqlMetal domain, I was about to enter the section, Querying Across Relationships. Getting back on track, I load the new Northwind.cs into my project. Next, I lift the code snippet from the article and plug it into a new Button2 click handler. Since the snippet from the article does not contain the DataContext, I add a line to create one. And since, the snippet does not have an iteration to look at the results, I use the earlier iterator (with a few tweaks). Now my code looks something like this...

        private void button2_Click(object sender, EventArgs e)
        {
            Northwind db = new Northwind(@"c:\LinqSamples\data\northwnd.mdf");
            var q =
               from c in db.Customers
               from o in c.Orders
               where c.City == "London"
               select new { c, o };
            foreach (var cust in q)
                Console.WriteLine("Customer id = {0}, City = {1}, OrderID {2}", cust.c.CustomerID, cust.c.City, cust.o.OrderID);
        }
    ...

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

        private EntitySet _Orders;
        [Association(Storage = "_Orders", OtherKey = "CustomerID")]
        public EntitySet Orders
        {
            get { return this._Orders; }
            set { this._Orders.Assign(value); }
        }
    }
    [Table(Name = "Orders")]
    public class Order
    {
        [Column(IsPrimaryKey = true)]
        public int OrderID;
        [Column]
        public string CustomerID;
        private EntityRef _Customer;
        [Association(Storage = "_Customer", ThisKey = "CustomerID")]
        public Customer Customer
        {
            get { return this._Customer.Entity; }
            set { this._Customer.Entity = value; }
        }
    }
    public partial class Northwind : DataContext
    {
        public Table Customers;
        public Table Orders;
        public Northwind(string connection)
            : base(connection)
        {
        }
    }

The code would not run though, because SQL Server could not log me into the database. I had attached the Northwnd.mdf in order to get SqlMetal to run. If I were to take the time to figure out the new connection string, I could just change it in my code. But, I cheat and just detach the database. But WATCH OUT! If you create a SQL login for yourself and set the default database to Northwind, and then detach Northwind, you have locked yourself out of the database (if I sound like I have experience with this, [blush] I do).

With the code back to running, I experiment a little. The new query above lets me now get at the members of the Orders with the member "cust.o". The "cust" variable is an anonymous object of type { Customer c, Order o }. So, "intellisense" works great showing the members of the Order class as I type. But, I've just spent a lot of time figuring out SqlMetal, I should be using the Northwind.cs definition it created.

To switch to the auto-generated Entities, I open the Northwind.cs file and change the namespace from "nwind" to LingPOC (to be compatible with my project). I use the handy refactor tool to change all occurances of "nwind". I really don't expect there to be any other occurances, but the tool is very useful in that it reports errors about my existing definitions for Customer and Order not being marked partial. That's okay because I just comment out my three classes (Northwind, Customer, Order). After all that work, gone in a moment.

I run again, everything is still working, and the intellisense now has more information to report about Order and Customer.

The final section of the Quick Tour gets into updating the database. In Modifying and Saving Entities, I note a few points. First, there is a nice little call on the Customers object called "Single()" which gets a single Customer object. The previous examples get a list of objects. So, even if the select statement narrows the results to one row, I would have to use a "foreach" statement to get at the row. The "Single()" statement reduces the code and complexity. Also, I note that creating a new Order appears very database independent, nice. Last, I note that the update to the database is performed by the "SubmitChanges()" member of the DataContext. Hmm, if I do a query for a customer and a query for an order on the same context, it appears separate changes to each cannot be separately updated to the database. I'll have to check this out.

Yes, it seems so. A single DataContext will update multiple separate selections. But, if I use separate DataContexts, I can independantly control the updates. There is a note that this behavior can be overridden. Anyway, that's enough for now. In a future post, I will embark upon the next section of the article, Queries in Depth. Here's my updated code. You'll need to generate your own Northwind.cs. And if you use this code, be sure to add the buttons to your form and link up the button click handlers. Enjoy...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LinqPOC
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        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);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Northwind db = new Northwind(@"c:\LinqSamples\data\northwnd.mdf");
            var q =
               from c in db.Customers
               from o in c.Orders
               where c.City == "London"
               select new { c, o };
            foreach (var cust in q)
                Console.WriteLine("Customer id = {0}, City = {1}, OrderID {2}", cust.c.CustomerID, cust.c.City, cust.o.OrderID);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Northwind db = new Northwind(@"c:\LinqSamples\data\northwnd.mdf");
            var cust = db.Customers.Single( c => c.CustomerID == "ALFKI");
            var ord = db.Orders.Single( o => o.OrderID ==  10269);// customer WHITC

            ord.OrderDate = DateTime.Now;

            Order order = new Order();
            order.OrderDate = DateTime.Now;
            cust.Orders.Add(order);

            db.SubmitChanges();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Northwind db = new Northwind(@"c:\LinqSamples\data\northwnd.mdf");
            Northwind db2 = new Northwind(@"c:\LinqSamples\data\northwnd.mdf");
            var cust = db.Customers.Single(c => c.CustomerID == "ALFKI");
            var ord = db2.Orders.Single(o => o.OrderID == 10269);// customer WHITC

            ord.OrderDate = DateTime.Now;

            Order order = new Order();
            order.OrderDate = DateTime.Now;
            cust.Orders.Add(order);

            db.SubmitChanges();
            db2.SubmitChanges();
        }
    }
}

(To be continued...)