Click here to Skip to main content
Click here to Skip to main content

An ASP.NET Data Layer Base Class for LINQ Disconnected Mode

, 14 Mar 2009
Rate this:
Please Sign up or sign in to vote.
Quickly and easily implement your LINQ Data Layer with this abstract class

Introduction

LINQ to SQL is a fantastic addition to .NET 3.0! It provides a type safe, powerful, and extremely flexible way to implement data access in .NET applications.

Unfortunately, using LINQ to SQL is not quite as straightforward when using it in a multi-tier database application. This article shows the typical pitfalls of implementing the data layer with LINQ to SQL, and provides a simple, convenient, and flexible way to circumvent most of them.

The generic base class for 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.

Prerequisites

This article assumes 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, then 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 LINQDataSource objects. But, that's not very object-oriented, and certainly not an advisable architecture. That is, 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 its many advantages – it's not very simple to use when implementing the data layer.

Have a look at the following database scheme:

entities.png

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 a multi-tier database application, the code might be somewhere in the UI or business layer, and will look like this:

//create a new repository instance
CustomersRepository customersRepository = new CustomersRepository();

//load a customer instance and change its FirstName
Customer customer = customersRepository.Load(2);
customer.FirstName = "Homer";

//commit 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 uses a 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. However, it has severe concurrency issues because one database context is used for all database operations: when calling Save(), SubmitChanges commits all changed entities, not just 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 a 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 many other scenarios where you need to use a 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

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

We have to:

  1. Detach the entity from the old data context
  2. Create a new data context
  3. Attach the entity to the new context
  4. 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<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);
    }

    //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 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.

A Solution: RepositoryBase

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:

orm_properties.png

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<Customer, DataClassesDataContext>
{
    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<Customer,DataClassesDataContext> 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:

ProviderBase-Interface.png

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.

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. 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.

If you have any questions, please let me know. And, feel free to stop by my blog for more development articles.

Version History

  • 07 Oct. 2008 - V.0.1
    • Initial release
  • 26 Feb. 2008 - V.0.2
    • RepositoryBase now updates ID and version attributes of saved entities
    • Added support for multiple ID columns

Thanks!

Thanks to Kris Vandermotten for the handy DebuggerWriter component, which is used by RepositoryBase for the SQL debug output.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Adrian Grigore
Chief Technology Officer Lobstersoft
Germany Germany
Adrian Grigore holds a German diploma in Computer sciences from the University of Würzburg, Germany.
 
He founded Lobstersoft early during his studies and has since then released several commercially successful game titles. All of these games were created using Adrian's custom build game engine.
 
In addition to object-oriented software design in C+ and C#, Adrian's main areas of expertise are ASP.NET, Perl and XML/XSLT.
 
Apart from leading Lobstersoft, Adrian also works as a freelance software development consultant.

Comments and Discussions

 
QuestionQuestion about maintaining state of a dynamic variable in disconnected mode. PinmemberRazor4tx2-Mar-12 19:39 
AnswerRe: Question about maintaining state of a dynamic variable in disconnected mode. PinmemberAdrian Grigore2-Mar-12 23:47 
GeneralLinq to SQL Lives! Pinmemberdavehamdan20-May-09 3:52 
GeneralPLINQO Implements Detach Automatically PinmemberEric J. Smith4-May-09 12:31 
GeneralSome points PinmemberFabrizio Magosso18-Feb-09 23:23 
AnswerRe: Some points PinmemberAdrian Grigore19-Feb-09 4:43 
Hi Fabrizio,
 
Thanks for your kind words and your interest in my article.
 
Both of your points do make a lot of sense.
 
Point 1: This is an outdated comment I forgot to update. Originally I was looking at the version attribute to see if an entity is being updated or inserted, but then I moved to looking at the identity because not everybody uses version columns (although it does IMO make sense to use it on every table).
 
Point 2: I did indeed forget to take multiple identity columns into account.
 
I hope to find some time next week to fix both bugs within the next week and post an updated version.
 
Thanks for pointing these two bugs out. Thumbs Up | :thumbsup:
 
Best Regards,
 
Adrian Grigore
 

GeneralNice post PinmemberMohammad Ashraful Alam30-Oct-08 23:37 
GeneralRe: Nice post PinmemberAdrian Grigore31-Oct-08 0:37 
QuestionWhy? Pinmemberliammclennan13-Oct-08 16:03 
AnswerRe: Why? PinmemberStevenHobbs13-Oct-08 20:48 
AnswerRe: Why? PinmemberAdrian Grigore13-Oct-08 22:37 
GeneralQuestion about SQL Transactions PinmemberToddHileHoffer7-Oct-08 3:30 
GeneralRe: Question about SQL Transactions PinmemberAdrian Grigore7-Oct-08 4:22 
GeneralRe: Question about SQL Transactions PinmemberCustec7-Oct-08 4:36 
GeneralRe: Question about SQL Transactions PinmemberAdrian Grigore7-Oct-08 5:38 
GeneralRe: Question about SQL Transactions PinmemberAlen_ekt1813-Jul-10 1:10 
GeneralRe: Question about SQL Transactions PinmemberAdrian Grigore13-Jul-10 5:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 14 Mar 2009
Article Copyright 2008 by Adrian Grigore
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid