Wednesday, January 7, 2009

LINQ to SQL

Oh My Stars and Garters! I think I may be in love. I was working on a personal project last night, and decided on two things:

  1. I would use OpenID as the login provider
  2. I would use Linq to Sql as the Data Access Layer
I'll talk about my experiences with OpenID later, for now, I need to gush a bit about the Linq stuff.

First though, I need to admit something: I was hesitant about adopting Linq. I mean seriously, look at the stuff. You do some fancy hand waving and all of a sudden, you have a data layer? It just seemed so... unnatural.

And I was right, it fundamentally is some fancy hand waving, and then you have a datalayer. It is unnatural, but it is also awesome!

So, what happens is that I build myself my data structures in SQL Server. I then use the diagram tool to make sure that all those fun Primary & Foreign Key and associated relationships are there. At that time, I drag and drop those tables onto the Linq designer surface. My computer freezes for a moment as it does some crunching and then I have data access stuff.

I'm being quite literal here; and it's fast. Fast to use, and fast to develop against.

Consider, I have a Customer object as part of my DataContext. The steps I would take, to save my changes to this are thus:
  1. Instantiate an instance of the Data Context
  2. Check to see if the Customer exists in the Data Context
  3. If yes, return that, if no return nothing
  4. If nothing, instantiate an new instance of a Customer, and add to Customers collection
  5. Make changes to the properties of Customer
  6. Update the Data Context
And sure, those are fundamentally, the exact same steps I would take with any data access layer. But the power is in the code. Consider, to do these steps, I would end up writing probably a hundred lines of code, and a good 50 or so lines of SQL. I mean, I would need to instantiate connections and commands, and just do all that stuff manually.

For Linq, it's this:
Dim db as DataContext
Dim Cust As Customer
Cust = (Select d From db.Customers Where d.Id = MyIdVariable ).SingleOrDefault
If Cust Is Nothing Then
Cust = New Customer
db.Customers.InsertOnSubmit(Cust)
End If
Cust.CustomerName = txt.Text
db.SubmitChanges
That is it.

What's happening there is I'm instantiating the datacontext, grabbing either the Customer object I want, or it returns Nothing. Checking for nothing, and if so, instantiating a new instance of the Customer object, and adding it to the DataContext, and then changing values via properties.

It's clean and straightforward, and utterly awesome.

No comments:

Blog Widget by LinkWithin