LINQ Architectures in 3 Layer, 3 Tier Applications

Before I get into the great features and great dangers of LINQ, lets get a couple thing out of the way:

• A 3 tier application refers to the physical separation of system in the database (in my case, usually SQL 2005), web application server (in this case Asp.Net 3.5), and web browser (a growing plethora :). 
• A 3 Layer application, in my vernacular, refers to a system that has a data access layer (DAL),  business logic layer (usually a combination of code-behind and shared assembly code that call the DAL), and the actual UI layer (usually a combination of controls and fixed markup).

Normally, a data access layer will be a set of data access methods that invoke procedures or dynamic SQL and return a combination of data object collections or DataSets/DataTables.  This system fits well into the stateless model, since it doesn't allow any "leakage" of utility from layer to layer; the business logic layer can't participate in a transaction unless that object is explicitly exposed, either implicitly of explicitly from the DAL. 

Enter LINQ-to-SQL and the whole thing is shot to bits.

Don't get me wrong - I love the fact that I can model a system in a DBML file that be accessed extremely easily from the DAL, and on top of that, it means that the data objects that are moved throughout the system are ready-made for me - but therein lies the catch.

Imagine a system that has a Order table and foreign key to an OrderItem table.  In LINQ we select an Order record by its OrderID.  In our DAL, we could return the Order object - subsequent call to the the OrderItems collection of Order object would, because LINQ interprets the foreign key, end up with a call to the OrderItems table. 

Great! Now our lives are SO much easier; we can let the business logic or even the UI layer decide if they need the OrderItems collection populated or not.  That way, we only get the extra database hit if we need it.  I can see cases where this would make life easier, but also whole lot of cases where this into the equivalent of a data access "goto" statement.

I won't tell you that you shouldn't allow the non-data access layers of your system perform data access via LINQ-SQL.  After all, isn't the DBML file and its auto-generated DataContext a kind of data access layer anyway?  Yep, it is a data access layer - but beyond a fairly simple system, you really ought to lock down the DataContext such that it is internal to your DAL, and ensure that it is disposed at the end of the data access method.  The easiest way to ensure this in your code is a simple using { } block. This will make sure that the database connection is closed, and that any child objects that are not already loaded into memory won't be accessible.

What's the nasty catch?  You won't be able to detect any code that is attempting to access the child collections (such as an Order's OrderItems) when that child collection is not going to be available until runtime.  So you'll get a null reference exception at this point.  Which should seem infinitely better than an unexpected call to the database that was not intended.

Also, now you are back to a situation where the DAL can be reliably tested by a series of unit tests that blast the database with all of the possible combinations of calls.

In my opinion, LINQ-SQL is a fantastic step forward, but we shouldn't think of it in a way that takes us too far outside of the nascent limitations of a 3 tier, 3 layer system.

More later - joel.