LinqToSql: Momma Said Knock You Out

Why don't you rip with me, the maniac psycho Cuz when I pull out my jammy, get ready cuz it might go Blauh!

In my last post on LinqToSql, I covered the DataContext and likened it to the Great Gazoo and Phoebe Cates - possibly the worst analogies in any form of tech writing, ever. But that's what you've come to expect so I won't disappoint. This aint no Peanut Butter Jelly Time or Hamster Dance ya know... In this post I'm going to take it a step further and show you how you can polish the chrome on LinqToSql so that you can cleanly separate your What from your How. I'm also going to go a little bit into how SubSonic fits into the whole "LINQ thang". Don't call it a comeback For the last week I've been beating on LinqToSql because, quite frankly, I want to know just where SubSonic fits in. If the ORM tool that ships with .NET 3.x does what we do, there's no reason to duplicate. SubSonic's all about improving YOUR experience, and we can just as easily wrap our loving arms around L2S just as easily as any other data access system. The challenge here isn't the technology - it's how it's supposed to be used. In other words - I have a problem with LINQ and how easy it is to stick data access code where it shouldn't go. It's twice as tempting with LinqToSql. When first created, your model practically BEGS you to let it run loose in your code behind, batting it's eyes innocently at you as it sticks the fake ID in its back pocket, ready to crawl out of your application come midnight. We've tried to make SubSonic as malleable as we can so that you, as the architect, can use its goodness wherever you please. LinqToSql sort of does this, but it's not straightforward from the get-go. I been here for years, Rockin' my peers ORM's been around a long time, and it bears mentioning right up front that

This is ORM, the Microsoft Way

I'll explain more as I go along - but keep that thought in your mind. The best explanations involve lots of code, so let's jump right in. The first thing you want to do is create a separate project. This will make sure that the protection patters we use below can be enforced. So right-click, add a class library project, remove Class1.cs (why do they put that in there?), and then right-click your project, selecting "Add Linq to SQL classes". It's a good idea to name it something relevant - like NorthwindSchema or something. When finished, you get a nice Class Diagram that lets you get your ORM on: The mapper let's you do all kinds of things - including set inheritance, relationships, naming, and so on. When you first "map stuff out", you're ready right away to do some coding. By default your DataContext class is the same name as the L2S file you created - I named mine NorthwindContext. To grab some data, all you need to do is create a context to work in, and then ask for the data:

NorthwindContext db=new NorthwindContext();
Gridview1.DataSource=db.Products;
Gridview1.DataBind();

Man, that's easy! It's actually too easy - and will tempt you into some bad habits; namely writing the code above. Puttin' suckers in fear Every project is "small, quick and simple" at some point - even for the prototype. That's not a license to be sloppy, and often the compromises made to run out a quick prototype never get refactored out like they should (guilty as charged). While I completely agree with the Premature Optimization theory, it's still important to make sure that your skeleton approach is sound so you don't build crap on a pile of crap, wrapped crappily in crap coating. Things to pay attention to up front:

• Abstract the Database and how you call your DB routines
• Put your business logic in a central place
• Keep the DB away from your UI

I'll keep the focus of this post on the Database and suggest that if you want to keep things on the simple side, go with something like a data-broker pattern, where all of your calls for data involve a static class returning data object (aka "models" or "entities" in the ORM world). I'll be using this model for the rest of the post. Makin' the tears rain down like a monsoon, Listen to the bass go boom Now that we have our model, we need to crank things down a bit. So the first thing I'm going to do is to change the namespaces of the generated code, and also reset the inheritance model. To do this, right-click in the diagram space and select "Properties" to bring up the Property Sheet, then get to settin': Things to notice here:

• I renamed the Context to something meaningful and I like the pattern [DBName]Context
• I set the Context namespace to "Northwind" and the entities to "Northwind.Data" - this will help a lot when coding and keep stuff "tidy".
• I locked down the Inheritance to "sealed" so no data objects can be extended outside the project
• I locked down access to the Context to internal so that it can't be used outside the project

The next thing I did was to create my broker class in the same project. This is a static class that hands out data as needed by the application. I called mine "NorthwindService". From an external application (a test application for instance), your DB interface class now looks like this: Nice and tight! There's no hint of LinqToSql anywhere - which is how it should be. The Data{} namespace simply exposes the entities, but no DB logic. Just how we want it. Explosion, overpowerin'; Over the competition, I'm towerin' LinqToSql exposes groups of entities using generics. So if you want to get a group of records to iterate over programmatically (in other words you're not DataBinding) you'll need to do a ToList<Type>():

NorthwindContext db=new NorthwindContext();
List<Product> prods=db.Products.Where(p => p.CategoryID==5).ToList<Product>();

This is OK most of the time, but it's a bit of a pain to work with Lists directly - especially if you want to extend them. An example of extending List<> might be for sorting, or overriding the index method. You also might want to add some business logic to a List<> - say OrderItems.AdjustQuantity() for instance. Before I work with the model, I'm going to abstract the List<> by entity - in other words I'm going to create Collection classes for the classes that need it (I probably don't need collection classes for all my tables). For this post that means the product table. Being nice and tidy, I'll create a folder called "Collections" and in it, I'll add a new class called "ProductCollection":

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind.Data;

namespace Northwind.Data {
    public class ProductCollection:List<Product> {

    }
}

Doing it this way locks things down nice and tight, and we've effectively erased LINQ's footsteps from our application :). Don't ever compare me to the rest; They'll all get sliced and diced In my last LinqToSql post I mentioned there was some weirdness with the way Update() works (Rick goes into it a lot here), and how the DataContext was attached to the model at the hip. This is important so I'll go into that now. Up above, in this post, I mentioned this is ORM done Microsoft's way. That's a very important thing to consider when working with the DataContext and updating data. Built into LinqToSql is automatic change tracking, with Optimistic Concurrency checks turned on. What this means is that if you pull a record from the DB, work on it, and someone else comes and updates the same record in the DB, your changes are likely to be invalid (or else piss someone else off extremely). This all works nicely in the DataContext, but I don't want that thing lurking all over my application - I want to do things in one place. This is where ORM has a shortfall - when you try to go outside it's rules it doesn't like you anymore. LinqToSql let's you work outside it's context, but if you want to keep playing with it you need to be a good developer and consider concurrency issues in your model. There are two ways to do this:

• enable version-tracking with a timestamp field in your tables (usually a very good idea) or
• tell LinqToSql to take a hike and leave you alone

In some ways I don't like it when technology dictates design changes, but at the same time a timestamp is a good idea for all tables with important data and in this case I have to say I'm grateful that I've been made to think about. So I'm going to add a "version" field to the Products table (type timestamp) to play ball. But I'm also going to tell LinqToSql to take a hike, and disable change tracking on the Employees table since they are the only ones (in my business case) who can update their data. I don't think it's necessary to keep adding timestamp fields to all my tables, so I get my crank going and have at the designer:

• Open up the dbml file and find the Employees table
• Shift-click all the columns (it has to be ALL of them - not just some of them. Not sure why this is a column setting because it doesn't work without setting for all columns) and then set "Update Check" to "Never":

This is a nice feature since it makes you think through these scenarios. You can't gain or maintain; Unless you say my name Now let's add some code and run some tests to make sure it all works as we want to it to. To the NorthwindService class I'll add these methods to expose data to my application - you can use whatever class structure you want for this, for now I just put it into one class:

    public static class NorthwindService {

        //static single-point context builder
        static Northwind.NorthwindContext db {
            get
            {
                return new NorthwindContext();
            }
         }

        #region Products
        public static Product ProductGet(int id){
            return db.Products.Single(p => p.ProductID==id);
        }
        public static ProductCollection ProductsByCategory(int categoryID){
            List<Product> prods=db.Products.Where(p => p.CategoryID==categoryID).ToList<Product>();
            return prods as ProductCollection;
        }
        public static int ProductSave(Product p){
            NorthwindContext context=db;
            bool isNew=p.Version==null;

            if(isNew){
                context.Products.Add(p);
            }else{
                context.Products.Attach(p, true);
            }
            context.SubmitChanges();
            return p.ProductID;
        }
        #endregion

        #region Employees
        public static Employee EmployeeGet(int id) {
            return db.Employees.Single(e => e.EmployeeID == id);
        }
        public static int EmployeeSave(Employee e) {
            NorthwindContext context = db;
            bool isNew = e.EmployeeID == 0;

            if (isNew) {
                context.Employees.Add(e);
            } else {
                context.Employees.Attach(e, true);
            }
            context.SubmitChanges();
            return e.EmployeeID;
        }
        #endregion

    }

Notice the ProductSave() method - by using a timestamp field called "version", I know whether it's been newly created. I could test the primary key value as well, but this type of thing doesn't always hold. The Customers table, for instance, uses a char(5) value for customerID. Since I have that timestamp field for Products, I can run the attach() method and Linq wont' complain. Also, since I told Linq to cheese off with regards to checking the Employees table, EmployeeSave also works, but I still have an issue. The check of the Primary Key is not really a good way to gaurantee that the passed in class is new. Currently this type of state checking isn't available in LinqToSql (that I've seen). To get around this I can create a set of partial classes for all of my objects and hook into their events to set some properties - but that's Fudge (note the capital F). The other thing I can do is to set something that LinqToSql should have set for me in the first place - the Read Only property for the primary key (it should be set to true - it's not by default): Much better - now we can reliably check EmployeeID (if it's 0, it's new) - there's no set accessor any more so people can't set this value. I'm gonna knock you out; Mama said knock you out You can plainly see where Microsoft doesn't want to lay tread on your turf, and they've always been that way (for better or worse). SubSonic, on the other hand, is all about convention and I can see how we can wrap LinqToSql nicely, generating things (and resetting settings) to wrap your schema. We could also generate up a set of partial classes for each Linq entity and throw in some state checks that are a little more explicit (like IsNew), and also do an audit of the schema to make sure your plan will play ball with Linq. You can see that this means moving from helper to more of an architectural standard, which in some ways is OK with me. I'd love to hear your thoughts on this!

Technorati Tags: LINQ, LinqToSql, Rob Conery, SubSonic