A LINQ to the CRUD

A question many people have run into is: How does CRUD fit into LINQ-to-SQL? While LINQ-to-SQL (I'll abbreviate as DLINQ) provides a very fast and easy way for us to start querying our data, it doesn't handle updates as beautifully. It is particularly noticeable when you are doing multi-tier and hence cannot share a DataContext.

Let's consider an example database called "MembersDatabase" which has a table called "Accounts". Accounts has an int primary key, and a varchar Email field. We use the DLINQ designer and create the dbml that generates a class called MembersDataContext. How does our app-tier code look? I'll give you a hint, it starts with "ug" and rhymes with "nasty":

// Select
int someId = 123; // Passed from another tier
Account someAccount; // Can't use implicit typing -- no anonymous types
using (var dc = new MembersDataContext()) {
    someAccount = dc.Accounts.SingleOrDefault(a => a.AccountId == someId);
}

// Insert
var myAccount = new Account();
myAccount.Email = "me@contoso.com";
using (var dc = new MembersDataContext()) {
    dc.Accounts.Add(myAccount);
    dc.SubmitChanges();
}

// Update
int myId = 1; // Id and email passed from another tier
string newEmail = "cool@cool.com";
var changedAccount = new Account();
changedAccount.AccountId = myId;
changedAccount.Email = newEmail;
using (var dc = new MembersDataContext()) {
    dc.Accounts.Attach(changedAccount, true);
    dc.SubmitChanges();
}

// Delete
int idToKill = 2; // Passed from another tier
using (var dc = new MembersDataContext()) {
    using (var txScope = new System.Transactions.TransactionScope()) {
        var acc = dc.Accounts.SingleOrDefault(a => a.AccountId == idToKill);
        if (acc == null) throw new ChangeConflictException("Row not found.");
        dc.Accounts.Remove(acc);
        dc.SubmitChanges();
        txScope.Complete();
    }
}

It's not horrible; it's certainly better than anything before it. But, we can do better. I created some helper classes to do so (see attached file).

First, DatabaseBase<TContext>. This holds our tables, and provides DataContext helper functions Use and Query. Second, TableBase<TItem, TKey>. This actually provides our CRUD methods. I don't think anyone is overly interested in the implementation details (comment if I'm wrong), so here's how you declare your CRUD types:

class MembersDatabase : DatabaseBase<MembersDataContext>
{
    public static readonly TableBase<Account, int> Accounts
        = CreateTable(dc => dc.Accounts, a => a.AccountId);
}

That's it.

You create a new class to serve as your "database" class. All that's required here is to inherit from DatabaseBase.

Next, for each table, simply create a new field via CreateTable. The first parameter is a lambda function that selects the right table off the DataContext. The second parameter is a lambda expression that selects the primary key. Not much too it.

So, how does our previous chunk of code look with this small helper library?

// Select
int someId = 123; // Passed from another tier
var someAccount = MembersDatabase.Accounts.SelectByKey(someId);

// Insert
var myAccount = new Account();
myAccount.Email = "me@contoso.com";
MembersDatabase.Accounts.Insert(myAccount);

// Update
int myId = 1; // Id and email passed from another tier
string newEmail = "me@me.com";
var changedAccount = new Account();
changedAccount.AccountId = myId;
changedAccount.Email = newEmail;
MembersDatabase.Accounts.Update(changedAccount);

// Delete
int idToKill = 2; // Passed from another tier
MembersDatabase.Accounts.Delete(idToKill);

That's about 40% less code. It’s far more straightforward, being a single block.

To start using this code, just drop DatabaseBase.cs into your project. It adds DatabaseBase to System.Data.Linq. Then subclass as shown above, and you're on your way to LINQ updating bliss. What do you think?

DatabaseBase.cs (5.65 KB)

P.S. At any rate, I should get points for the Zelda pun, right?

Update: I forgot to mention, you'll want to turn UpdateCheck to Never on your columns in the LINQ-to-SQL designer.

Code