A Generic Base Class for LINQ-to-SQL Data Access in a Multi-Tier Application

(Recent update: While this example provides some good background and options for LINQ-to-SQL, I later noted there were a few use cases, for example when I deleted Orders from Customer objects, where this code may not have fully handled the updates as I had hoped, so if you’re interested in checking out the LINQ-to-Entities version of this article, click here.)

I was exploring options recently for ways to take advantage of LINQ and Generics to avoid having to write the typical CRUD Data Access Layer code over and over. Since I was looking to work with Domain-Driven Design (DDD) it would also be nice if the code made use of the Repository pattern. To that end, I came across a few articles and code samples that took advantage of LINQ-to-SQL to do just that. (I should also mention that I looked for a Generic Base Class LINQ-to-Entities solution, although it seems most only involve LINQ-to-SQL, likely due to its close tie to the SQL Database source and knowledge of the underlying table schema. I also found that some people noted missing features in LINQ-to-Entities, so perhaps someday we’ll find an equivalent LINQ-to-Entities solution.)

In particular, I was most impressed with this great article by Adrian Grigore. Adrian does a very nice job explaining some of the nuances of LINQ-to-SQL (i.e. – Working with it in “Connected” mode vs. “Disconnected” mode) and he also provides a sample code solution for you to look at. Unfortunately, the solution I was looking for was one that could be used in a true multi-tier application (i.e. – where the UI does not call the DAL directly, as it does in Adrian’s code samples).

Under the new multi-tier design, the LINQ classes in the DAL are converted to a very similar (“intermediate” / data transfer object (DTO)) class before its passed back to the UI. While we do find ourselves doing a bit more work “translating” the original LINQ class (Customer for example) into this new (intermediate) Customer / DTO class, what we hope to gain is the keeping of a more pure “OO” approach here between Layers, and to keep the UI layer totally unaware of LINQ-to-SQL and its “data access” classes.

If you like, you can download the sample VS 2008 solution here.

So, just to get a simple working example going, I went for the old Customer –> Order –> Order Line Item database tables, and made a few changes to Adrian’s original code:

1. Since I was already creating new entities to pass back to the UI layer, there was no need to “detach” the LINQ-to-SQL entities from the original data context, so I removed that from the code. For the same reason, I did not need to perform any serialization, so I didn’t need to set the “Serialization Mode” to “Unidirectional” in the “Properties” panel for the LINQ-to-SQL dbml class file.
2. Added a SetDataLoadOptions() method that can be overridden to allow the concrete Repository class to provide for immediate loading of the specified child records, instead of using LINQ’s default deferred execution / lazy-loading model.
3. Made a few minor bug fixes to the code.

Other important notes:

1. I followed Adrian’s lead on adding an ID Identity field and Version (timestamp) column to each of the tables to help LINQ “attach” entities to the new data context created (when objects are passed from the UI back to the DAL to be persisted) and to speed up the Save and Delete operations.
2. To keep the UI Layer totally free of any DLL references to System.Data.Linq (i.e. – normally used for the Binary data type for the Version (i.e. – timestamp SQL Server column) for the classes), I found this nice article which included two Extension methods that allow you to easily convert from a Binary field to a String and back again, that I also incorporated into the solution.
3. To improve performance for DELETEs, I opted for implementing CASCADE DELETEs in the database, and only utilizing the DELETE operation for the top-most parent object.

Instead of creating a UI project, I decided instead to create a Visual Studio Test Project. The unit tests in this project made it easier to test the functionality and to confidently refactor the code when needed. Before running any of the tests, be sure to checkout the ReadMe.txt in the test project.

That’s it. Hope you find this helpful …