LinqToSql: Ranch Dressing for your Database Pizza

Lots to read lately on LINQ and Linq2Sql, and for the last couple of days I've been tits-deep in code, trying to figure out neat and fun ways to clamp LINQ to my Database's privates while not causing offense...

Let's start this post again. WTF Rob...

So, over the last week I've been duct-taping LINQ onto Northwind and my little tester application. I know that might not sound so pretty but it's the best way I can put it since I'm not trying to code with it - I'm trying to understand it and break it (not hatin... i promise).

The presence of LINQ, while all around very positive, makes me question my commitment to my database and my application. To play with LINQ I need to change my rules, need to work from a "different perspective" as it were. It's not a bad thing - Rails made me do this and good things have happened because of it (I think - well it was good for me anyway... isn't that what's important? :p).

I've always been your typical, faithful geek that doesn't stray too far from the barn - but then LINQ comes along, wearing all that leather and smelling enticingly of chocolate and burnt...

Crap. Let's just get to the code... enough analogies already so I'll just get to the point:

LINQ Confuses Me

Sure It's Cute- But Can It Dance?
The first thing I did, cause as you know I'm sorta performance-minded, was to make sure my DB could party with LINQ and hold it's own. The last thing I need is my DB crawling into work with bags under it's eyes, tired and sore from the night before. So I kicked up a console app and decided to crank out some perf tests. The tests were simple and involved me rolling over the Products table 1000 times (using LinqToSql), and loading it to a List<>:

DateTime dEnd = DateTime.Now;
DateTime dStart = DateTime.Now;

List<Product> prods = null;
for (int i = 0; i < 1000; i++) {
    Northwind.NorthwindDB db = new NorthwindDB(GetConnString());

    Table<Product> tbl = db.Products;
    prods = tbl.ToList<Product>();
}
dEnd = DateTime.Now;
TimeSpan ts = SubSonic.Sugar.Dates.Diff(dEnd, dStart);
int ms = ts.Milliseconds;
WriteOut("result: " + ms.ToString() + " ms for " + prods.Count + " records");

For fun, I then loaded up the same thing in SubSonic:

for (int i = 0; i < 1000; i++) {
    new ProductCollection.Load();
}

To my surprise LINQ was within 50ms of SubSonic - which is pretty groovy! Looping 1000 times using SubSonic came in at~600ms (an average), and LINQ came in between 600 and 700 ms - as far as I'm concerned that's a tie.

I was talking to ScottGu the other day and he mentioned that you can improve even more on performance by using "CompiledQuery", so I gave it a shot and added it to the test. The trick here is you declare the query in advance, then use it to pull back the result set:

var resultComp = CompiledQuery.Compile((NorthwindDB ndb) => from p in ndb.Products
                                                            select p);
for (int i = 0; i < 1000; i++) {
    Northwind.NorthwindDB db = new NorthwindDB(GetConnString());
    prods = resultComp.Invoke(db).ToList<Product>();
}

There was a small improvement but it was negligible in this case - mainly because the query I'm doing is pretty basic. If it was more complicated I'm sure this would help.

Also - I can improve even more on the Linq2Sql query by creating the instance of the DB context outside of the loop - but that's not quite representative of load really. Or is it?

This is where the confusion begins. What do I do with this context thinger anyway?

I'm Sorry, I Can't Hear You, There's a DataContext In My Ear
So I shoot an email off to ScottGu and ask him if he has a minute to let me in on the DataContext secret. I'll summarize here and say that the DataContext is the Secret Sauce behind LinqToSql - sort of like Ranch Dressing for your fries. Sure fries taste good, but there's something special about a slopping them around in a bunch of Ranch Dressing isn't there. Come on... I know it was the 80's... but you still sneak some Country Fresh Ranch from the market... I know Atwood does. It's why he's got the Twitter Jitters.

Anyway - he explains to me that the idea behind the context is to have an persistence medium (my words) between the app and the DB.

For instance, let's say you create an SP that returns all products for a given category (ProductsByCategory) - the same thing Scott showed on his blog today. One of the cool things you can do with Linq2Sql is change the return type so that you return a Product for this SP - super slick.

So let's call this SP and bring back all products for CategoryID 1:

NorthwindDB db = new NorthwindDB();
//get products from SP
List<Product> product1 = db.ProductsByCategory(1).ToList<Product>();

For fun, let's bring back another result set for all products:

//get the products from L2S
List<Product> product2 = db.Products.ToList<Product>();

So now we have two List<Product> collections to play with. Here's where the "persistence" bits come in - if I change one of the objects in product1, product2 will also be changed under the covers:

//grab the first product from L2S
Product p1 = product1.Single<Product>(prod => prod.ProductID==1);
Product p2 = product2.Single<Product>(prod2 => prod2.ProductID == 1);
//reset the name
p1.ProductName = "Keoki Gold Lager";
Assert.IsTrue(p1.ProductName == p2.ProductName);

... And this test passes nicely. Pretty slick stuff!

There's a lot more this way, and I know Scott has a post coming on it in the next week or so, so I'll let him go deeper into it. Just know there's a lot going on with the DataContext in the background.

So in running up this code, I couldn't help but feel like the DataContext was sort of ... insinuating itself into my application's architecture. Sort of like the Great Gazoo used to follow Fred and Barney around. Fred was always confused by the whole Gazoo thing wasn't he? I mean - was he a friend? Or was he annoying? 

Well I'm not afraid to admit that I like Ranch Dressing, and I don't mind flying green martian guys either for that matter - so let's see if we can buddy up and party like Rock Stars.

Molly Ringwald and Co-Dependent Relationships
I think I was the only kid in Junior High that didn't have a crush on Molly Ringwald - even after that whole "lipstick scene" in the Breakfast Club. Cute, but sort of clunky and sad. Now Phoebe Cates. THAT's a scene I don't need to explain to anyone. In fact I don't even need to use her name - it's just "The Scene" as far as most guys go.

So here we are, with our new friend the DataContext, wondering if we should try and hold hands or just be friends. Initially I thought of it as a sort of Molly Ringwald - "cute... but...". So I created a Singleton out of it and decided that my entire application would work from one context:

NorthwindDB _context;
public NorthwindDB DBContext {
    get {
        if (_context == null) {
            lock (this) {
                _context = new NorthwindDB();
            }
        }
        return _context;
    }
}

The problem here is that the DataContext keeps track of what's going on with all of your objects, and that leads to some serious overhead. Also, if you're working from a static class that serves up business logic (i.e. a Service class or BLL) then you're going to really screw things up since the DataContext stores and tracks state per table (as shown above) - so if a user changes some data, that change will ripple out to your entire application. Usually not good.

OK so now we know that the DataContext needs to be instanced up and used with certain rules. It's not as easy as Molly - it's more high maintenance -  like Phoebe.

For example - let's create some CRUD methods for a Service class (Rick Strahl has a great post about this, in a lot more detail): inserting and updating a Product:

//create a new product
Product p = new Product();
p.ProductName = "Phils Heinous Chai Tea";
p.CategoryID = 1;
p.UnitPrice = 100;
p.UnitsInStock = 0;
p.SupplierID = 2;
p.CreatedOn = DateTime.Now;
p.ModifiedOn = DateTime.Now;

Service.InsertProduct(p);

public static void InsertProduct(Product p) {
    //instance up the Context
    NorthwindDB db = new NorthwindDB();
    //attach the product to the context
    db.Products.Add(p);
    //save it down
    db.SubmitChanges();

}

This works exactly as you would expect - so no problems here. But what about updating? This is a little muddier:

//get product
Product p = db.Products.Single<Product>(prod => prod.ProductName == "Phils Heinous Chai Tea");
//Phil says people don't know what heinous means
p.ProductName = "Phil's Horrible Chai";
UpdateProduct(p);

To get the update to happen, you need to create a context and use Attach() - but this isn't working at the moment (Rick has more on his blog). I've been told this might be a bug - I'd like to assume that it is. Here's how the code should look:

public static void UpdateProduct(Product p) {
   //load the context
    NorthwindDB db = new NorthwindDB();
    //this is a bug - I think. Or is it? Phoebe?
    db.Products.Attach(p,true);
    db.SubmitChanges();
}

You can check to see if Phoebe's been listening to you at all if you'd like, by taking a look at the the ChangeSet - inserting the following lines of code right before db.SubmitChanges():

//so did the change happen? Is our product queued?
ChangeSet changes = db.GetChangeSet();
int mods = changes.ModifiedEntities.Count;
int adds = changes.AddedEntities.Count;

It turns out that, in fact, that this doesn't work at this time. I think it will get fixed - we shall see.

LINQ Drops Its Top...
So up to this point we've been looking at the DataContext and how to leverage it into an application. It's mighty pretty stuff as it saunters casually through the sprinklers towards us, but that doesn't mean that it won't be screaming at us for leaving our empty beer bottles in the shower just six months from now.

Normally a degree of separation is wanted when you talk to your DB - this is true for most "client-server" apps out there today, which is about 90% i'd say. In other words, the app asks for data, get's it, and says "buh bye" until it needs to hand some data back. There can be issues with this (concurrency violations for one) and people have spent a lot of time working on solutions to locking and concurrency issues.

The interesting thing is that once you get to know LinqToSql, you see that it's an ORM model that persists a sort of "state" of your domain - pushing the changes to the DB only when you tell it to. In the past we've been told to make more calls to the DB to keep things running happily - this is something different, even for ORM and using it might take some getting used to - and also call for doing some things differently. 

For instance - the DataContext can exist in the same way, perhaps, as a User Session does in a web application. In fact you might even be able to store it in the user's Profile (I haven't tried this but... well you never know!).

...And We Find Gazoo Dressed In Phoebe's Clothes
So the DataContext seems to be this bucket - or perhaps a floating green martian that helps us to work with our DB. We're not really sure where it lives, nor what it needs to eat (Ranch Dressing?) - but it has its rules and if we can play by them, it could be a good thing:

You whistle a tune and here comes the DataContext, our Great Gazoo:

"What can I do for you sir?"
"You can get all the products from the db, update the view stats, and then save down this log for me. But wait a minute cause this user might want to one of the products to their basket"
"OK - Just let me know. I'll hang out here in the Session I spose. "
"Sounds good - we should find a better home for you but for now that'll work."
"OK the user wants to add these products. Can you get the sales tax info, shipping, and then check out - can you handle this for me?"
"Sure, I'm transactional you know."
"Perfect - submit your changes now and then we'll see about that new home..."

(I seriously resisted using the Tron/Bit thing here I'll have you know...)

The thing here is that you're now making architectural decisions based on the concept of "state", where the web is a stateless sort of thing. But that's so 2002 - haven't things sort of changed now with Ajax and all that jazzy snazzy kid stuff? In a way it's almost refreshing to abstract the database from this "thing that holds data" to more of a gateway. This is a new and different sort of ORM and I think there are some creative ways to work with it.

Ranch Dressing for The New Millennium
So here we are - in the age of client server, slowly retracing our steps back into a "Thick Client" world. Consider that AJAX, Silverlight, and Flash have ushered in the "Thickness Renaissance". Before Google brought Ajax out of the bathroom closet, Microsoft had used the XMLHTTP thinger to do some fancy stuff "waybackwhen" and was basically lambasted for it only to have Google club it over the head with it's own technology - done right.

Is the DataContext doing the same? Why yes - it is. Is it bad? I don't know yet - I haven't broken it enough, to know. I do know that it's high-maintenance, powerful stuff and it's demanding that I change some old habits.

It's been a long time since I put Ranch Dressing on anything - but you never know, it might just taste good again.