Adrian Grigore

A generic base class for LINQ to SQL Data Layers

In LINQ on October 13, 2008 at 17:04

It’s hard not to fall in love with LINQ to SQL. It provides a type safe, powerful and extremely flexible way to implement data access in .NET applications. And it looks so easy to use in those nifty Microsoft evangelist presentations!

Unfortunately, after having a closer look at LINQ, I found that using LINQ in a multi-tier application can be quite a struggle. This article shows the typical pitfalls of implementing the data layer with LINQ to SQL and provides an simple, convenient and flexible way to circumvent most of them.

The generic base class for a LINQ-to-SQL Database Abstraction Layers (DAL) that comes with this article has the following features:

  • Implements the Repository pattern, allowing you to conveniently implement CRUD (Create, Update, Delete) operations with less than ten lines of code per LINQ entity type.
  • Works seamlessly in disconnected LINQ mode
  • Supports transparent database updates of LINQ entity hierarchies in one single database roundtrip.
  • As a convenience feature, it also writes all executed SQL statements to the output console when debugging your application.

I’m going to assume that you have a basic understanding of what LINQ to SQL (also known as DLINQ) does and how it is used. If you don’t, have a look at this tutorial first and come back to this page to see how to use LINQ to SQL in multi-tier applications.

The Problem

LINQ to SQL is incredibly easy to use if you simply hook your UI layer directly to the database with LinqToDataSource objects. But that’s not very object-oriented, and certainly not an advisable architecture unless you are coding a quick and dirty application and do not plan on extending it in the long run.

Instead, most developers divide their applications into several layers, for example the following:

  • Data Access Layer
  • Business Layer
  • UI Layer

This is known as a multi-tier database application design. LINQ to SQL would be used in the Data Access Layer.

The problem with LINQ to SQL is that – despite it’s many advantages – it’s not very simple to use when implementing the data layer.

Have a look at the following database schema:

As long as you are loading and saving LINQ entities to the same data context instance (this is known as “connected mode”), implementing your Data layer with LINQ is very straightforward.

For example, let’s fetch the customer entity with ID==1 from the database, change its first name to “Homer” and save it back to the database. In an multi-tier database application the code might be somewhere in the UI or business layer and look like this:

//create a new repository instance
CustomersRepository customersRepository = new CustomersRepository();
//load a customer instance and change it's FirstName;
Customer customer = customersRepository.Load(2);
customer.FirstName = "Homer";
//commmit customer to database
customersRepository.Save(customer);

The easiest way to implement the data layer Load and Save functions used above is this:

static DataClassesDataContext context=new DataClassesDataContext();
public Customer Load(int CustomerID)
{
return context.Customers.Single(c => c.ID == CustomerID);
}
public void Save(Customer toSave)
{
context.SubmitChanges();
}

This approach is using connected LINQ mode: The data context never goes out of scope, so it can always be reused to save entities to the database which are still connected to it.

Granted, it is convenient and works for the isolated example above, but it has severe concurrency issues because one database context is used for all database operations:
When calling Save(), SubmitChanges commits ALL changed entities, not only those related to the LINQ entity that the Save Method received in the toSave parameter.

But even setting this flaw aside, you can’t implement the data layer in the same manner when using LINQ in a multi-tier ASP.NET application. Here, chances are that your LINQ entity is loaded with the one page request, then updated and saved to the database with the next page request. Meanwhile, your original data context has gone out of scope, making your LINQ entity disconnected.

And there are also many other scenarios where you need to use disconnected LINQ mode: For example you might want to implement your Database Layer as a web service, commit previously serialized LINQ entities to your database, etc.

Implementing the data layer with disconnected LINQ to SQL

So, how do we implement a Data layer Save() method that works in disconnected LINQ mode?

We have to

  • Detach the entity from the old data context
  • Create a new data context
  • Attach the entity to the new context
  • Submit changes

In source code, it looks like this:

public Customer Load(int CustomerID)
{
DataClassesDataContext context = new DataClassesDataContext();
return context.Customers.Single(c => c.ID == CustomerID);
}

public void Save(Customer toSave)
{
//the old data context is no more, we need to create a new one
DataClassesDataContext context = new DataClassesDataContext();
//serialize and deserialize the entity to detach it from the
//old data context. This is not part of .NET, I am calling
//my own code here
toSave = EntityDetacher<Customer>.Detach(toSave);
//is the entity new or just updated?
//ID is the customer table's identity column, so new entities should
//have an ID == 0
if (toSave.ID == 0)
{
//insert entity into Customers table
context.Customers.InsertOnSubmit(toSave);
}
else
{
//attach entity to Customers table and mark it as "changed"
context.Customers.Attach(toSave, true);
}
}

Now you can load and alter as many entities as you like and only commit some of them to the database. But due to using disconnected LINQ, this implementation does not account for associations between LINQ entities.

For example, imagine you want to do the following in your business or UI layer:

//load currently selected customer from database
Customer customer = new CustomersRepository().Load(1);
//change the customer's first name
customer.FirstName = "Homer";
//add a new bill with two billingitems to the customer
Bill newbill = new Bill
{
Date = DateTime.Now,
BillingItems =
{
new BillingItem(){ItemPrice=10, NumItems=2},
new BillingItem(){ItemPrice=15, NumItems=1}
}
};
customer.Bills.Add(newbill);
//create a new provider to simulate new ASP.NET page request
//save the customer
new CustomersRepository().Save(customer);

The disconnected mode Save() method above would commit the change to the FirstName column, but simply forget about the new bill and billing items. In order to make it work, we also need to recursively Attach or Insert all associated child entities:

public void Save(Customer toSave)
{
//the old data context is no more, we need to create a new one
DataClassesDataContext context = new DataClassesDataContext();
//serialize and deserialize the entity to detach it from the
//old data context. This is not part of .NET, I am calling
//my own code here
toSave = EntityDetacher.Detach(toSave);
//is the entity new or just updated?
//ID is the customer table's identity column, so new entities should
//have an ID == 0
if (toSave.ID == 0)
{
//insert entity into Customers table
context.Customers.InsertOnSubmit(toSave);
}
else
{
//attach entity to Customers table and mark it as "changed"
context.Customers.Attach(toSave, true);
}
//attach or save all "bill" child entities
foreach (Bill bill in toSave.Bills)
{
if (bill.ID == 0)
{
context.Bills.InsertOnSubmit(bill);
}
else

{
context.Bills.Attach(bill, true);
}
//attach or save all "BillingItem" child entities
foreach (BillingItem billingitem in bill.BillingItems)
{
if (bill.ID == 0)
{
context.BillingItems.InsertOnSubmit(billingitem);
}
else
{
context.BillingItems.Attach(billingitem, true);
}
}
}
}

Not very complicated, but a lot of typing. And that’s only for a trivial database scheme and one single entity type. Imagine you were implementing the database layer for several dozen entity types with a few dozen foreign key relationships. You would have to write dozens of nested foreach loops for every single LINQ entity you need a DAL Repository class for. This is not only tedious, but also error-prone. Whenever you add a new table, you’d have to add a few dozen foreach loops to various DAL Repository classes.

How I avoided all these problems

After quite a bit of online research, I implemented a class called RepositoryBase that you can use to quickly implement your data layer that works fine with the examples shown above.

In order to use it, you must first instruct the Object Relational Mapper to generate serializable LINQ entities: Open your dbml file in Visual Studio, left-click somewhere in the white area, and set “Serialization Mode” to “Unidirectional” in the “Properties” panel:

 

 

Now you can derive from RepositoryBase to implement your own Repository:

public class CustomersRepository :
//derive from RepositoryBase with the entity name and
//data context as generic parameters
DeverMind.RepositoryBase
{
override protected Expression<Func<Customer, bool>> GetIDSelector(int ID)
{
//ID needs to be the entity's ID column name
return (Item) => Item.ID == ID;
}
}
public partial class Customer
{
public static RepositoryBase CreateRepository()
{
//create and return an instance of this entity type's repository
return new CustomersRepository();
}
}

Do this for each of your entity types, and you have a data layer working seamlessly in disconnected mode. Your derived Repository classes automatically implement the following methods:

 

 

As a small bonus, you can also see the SQL commands that were run against the database by ProviderBase in your debug output console when debugging your application. Thanks to Kris Vandermotten for the handy DebuggerWriter component, which is used by RepositoryBase for the SQL debug output!

There’s no free lunch…

There is no significant performance penalty for the Load operations, but there is a bit of reflection going on behind the scenes when you are calling the Save or Delete
methods.

For the vast majority of your DAL needs, this probably has no significant impact on your application either. However, if you are performing a lot of update / insert / delete operations, especially with lots of nested child entities involved, then you might want to hand-code your own Save / Delete functions for the Repository classes of those child applications as described above. All Save / Delete functions are virtual, so you can easily override them.

Also, please note that RepositoryBase does not support recursive save or delete operations with circular dependencies.

Conclusion

This article and the included source code provide a simple, convenient and extensible way to implement your multi-tier LINQ data layer CRUD methods. It works in disconnected mode and supports saving and loading of nested child entities. There is a small performance penalty on Save and Load operations, but you can override those for those Repositories where Save or Load performance is critical.

For everything else, you’re good to go with just a few lines of code.

Source Code

The updated source code for this article can be found here.

Feedback Wanted!

Thanks for your interest in this article. If you have any hints, questions or suggestions, please let me know. I’m using this class on a daily basis and am therefore always interested on how to improve it even further.

Advertisements
  1. Thanks and this is great work!

    I’m asking your opinion on the following:

    I’m facing an architectural issue while trying to integrate this repository into my project.
    I want to keep the usual application architecture:

    1 – DAL
    2 – BusinessLayer
    3 – Presentation layer (in my case it’s web services)

    The purpose of this architecture is that the Presentation Layer would only have a reference of the BusinessLayer. And the BusinessLayer would only have a reference of the DAL. (This involves the typical benefits of designing multi layers)

    Now, using the repository pattern that you provided, all entities and repositories would reside in the DAL.
    The presentation layer needs a reference to the Entities, witch reside in the DAL… The application cease to be multi-layered in dependencies?!
    -PresentationLayer has a reference of BusinessLayer
    -PresentationLayer has a reference of DataAccessLayer
    -BusinessLayer has a reference of DataAccessLayer

    In the example you gave, we have to create a partial class for all entities, this involves the same namespace, same dll.
    All of the repository classes need to be in the same dll of the linqToSql.dbml. Otherwise, the partial class needs a reference to the dll owning the repository classes. And the dll owning the repository classes needs a reference to the dll owning the entities (linq to sql classes), this is a circular dependency = impossible!
    /*****************************/
    public partial class Bill
    {
    public static RepositoryBase CreateRepository()
    {
    return new TimeRequiredsRepository();
    }
    }
    /*******************************/

    In the example you provided, there was no BusinessLogic layer, the repositories are used directly in the presentation layer.

    Unless I’m missing something here, how do you consider resolving such an issue.

    Thanks

  2. Joe,

    Thanks for your interest my my posting.

    I understand about your concern to separate the application into UI, BL, and DAL. It’s the same separation I usually use as well. I just did not use it in the demo website to keep things simple and cut down on coding time for the demo.

    About the recursive reference problem you mentioned: I usually put my LINQ entities in the DAL, thus avoiding this issue. It seems to be a fairly common approach (for example it’s recommended in ASP.NET 2.0 Website Programming Problem Design Solution by Marco Bellinaso). Since the LINQ entities are essentially related to data access, I do not think this violates your 3-tier separation.

  3. Adrian

    Thanks, for the quick reply, I appreciate.

    So basically, the UI can use the LINQ entities in the DAL, but the repository classes should only be used by the BL.

    I’m fairly new to architecturing, so I’m taking each steps cautiously!

    Regards

  4. Joe,

    Yes, that’s exactly the way it works in my web applications.

    However, these decisions depend on how object-oriented you want to work. There is no “right” way to do it.

    For example, hard-core OO enthusiasts might even advocate not using any LINQ entities in any layers other than the DAL since it exposes functionality related to data access which should not be used directly, but through the DAL. This means of course much more overhead, both in terms of coding and system resources since you’d have to copy or wrap all information from LINQ business entities into your own hand-coded business entities. Plus, it might even hide useful functionality, namely relationships, depending on how you implement it. But it is a cleaner OO approach.

  5. Hi – good article and I’ve taken a similar approach by implementing a generic Save routine which takes any entity object and recursively loops down through its object tree using a combination of generics and reflection to insert/update/delete items. I thought it was long winded but seemingly there is no other way to simplify the saving process in a multi-tier environment and keep the datacontext’s out of the presentation layer…

  6. Hi,

    Thanks for the article.

    I’ve noticed as I’ve been scouring the web for the past few weeks that many examples (yours included) seem to expose the data access functionality to the the presentation layer (PL).

    That’s probably fine, for most situations, because the same developer(s) that created the BLL & DAL are also making the presentation layer and (through self-discipline 🙂 they would -never- try to code directly to the DAL from the PL.

    But, when creating “mini” apps that are meant to plug into larger frameworks (such as doing a plug-able module for a DotNetNuke site), the developer is actually taking a risk in exposing the data access functionality to the PL. What if a 3rd party developer “notices” this and then writes a module that accesses that data.

    Keep in mind that the “good-guy” developer is selling his module to sites, and the “bad-guy” developer is also selling his “other” module with the hopes of getting a customer who’s bought the “good-guy’s” module. The customer’s data gets messed around with because the “good-guy” used Linq2SQL in a way that exposes the whole DAL.

    In short, there seems to be a major assumption in these examples that seems to violate basic security concerns (namely, never letting someone by-pass your BLL and “hiding” the data access parts of the DAL, while exposing the entities).

    Anyway, I thought I would post in the hopes that you can add your insights to my comments. I realize this might be thought of as an “edge” case, but it still translates into a large number of potential cases, what with the rise in popularity of using plug-able portal frameworks like DotNetNuke.

    Thanks for any thoughts you share!

  7. Dan,

    Thanks for your kind comment.

    While I did take care to avoid exposing the LINQ data context to BLL or UI layer, you are right that the business entities are in fact LINQ entities created by SQLMetal. I agree that this is not a purist approach, however there is (as usual) a trade-off between practicability and object-oriented design. One could for instance create new business objects without any LINQ ties such as lazy loading or any references to old data contexts at all. But this would also mean throwing a lot of the functionality overboard that makes LINQ so useful (such as lazy loading of associated entities).

    In the end, how much (or how little) of this you use (or encapsulate) is just a matter of personal preference. I tried to find some middle way which does not sacrifice too much functionality while at the same time avoiding to expose the LINQ data context.

    Best Regards,

    Adrian Grigore

  8. Great work.

    I’ve started implemeting a website based on your code. (A generic base class for LINQ to SQL)
    I encountered a serious problem , after inserting a new entity using the Save() function the entity ID column (unique identifer) isn’t updated and remains “0”.

    The result is that you cannot work with the inserted entity and must reload it from the db to get the correct ID , which isn’t really easy as you dont have the ID.
    from what i know LINQ doesn’t support @@IDENTITY and should update the ID automaticly.

  9. […] It’s been a while since I posted the first version of my generic base class for LINQ2SQL data layers. The idea behind this class was to provide a quick and simple way to implement a repository with LINQ2SQL and therefore also a solid foundation for implementing your n-tier architecture data layer. If you missed the article, you can read more about the base class here. […]

  10. Mike,

    Thanks for your kind comment and your interest in my article. You are right, the functionality for updating a linq entity’s version and ID is still missing in the version posted in this article. I have meanwhile implemented it, but forgot to post a new version. You can find the new article here:

    http://devermind.com/uncategorized/updated-generic-base-class-for-linq2sql-data-layers

  11. TO :Joe
    For resolving your problem,you can redo the Method IterateEntity of RepositoryBase.cs like this:
    //Edit By 宗子城,解决业务逻辑层和数据访问层不分的问题
    /*
    object Repository = association.OtherType.Type.GetMethod(“CreateRepository”).Invoke(null, null);
    Repository.GetType().GetMethod(“IterateEntitySet”,
    BindingFlags.NonPublic | BindingFlags.Instance).Invoke(
    Repository,
    new object[4]
    {
    AssociationProperty.GetValue(theEntity, null),
    context,
    OperationMode,
    Recursively
    }
    );
    */

    Assembly assm = Assembly.LoadFrom(“DAL.dll”);
    string enRepositoryName = GetType().Namespace +”.”+association.OtherType.Type.Name + “Repository”;

    object entyRepository = assm.CreateInstance(enRepositoryName);

    entyRepository.GetType().GetMethod(“IterateEntitySet”,
    BindingFlags.NonPublic | BindingFlags.Instance).Invoke(
    entyRepository,
    new object[4]
    {
    AssociationProperty.GetValue(theEntity, null),
    context,
    OperationMode,
    Recursively
    }
    );

Comments are closed.

%d bloggers like this: