An Example of a Multi Tier Architecture for Linq to Sql

NEW POST: What is the fastest way to get a scalar value back

tintin5 — Wed, 19 Nov 2008 16:27:19 GMT

What is the fastest way to get a scalar value

I am wondering about the most efficient way to get back 1 non key value from an entity.

Things I would consider are
1. pre compiled queries
2. Object Tracking
3. Creating a “new” entity.

See this interesting article from Sidar

http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

NEW POST: How to use in NON ASP.NET code sections?

rbates — Wed, 05 Nov 2008 11:18:33 GMT

Hello all, this project has now moved to http://www.codeplex.com/MultiTierLinqToSql
The most recent version also has the method to discard the datacontext requested in this thread

NEW POST: Problem on Update

rbates — Wed, 05 Nov 2008 11:15:26 GMT

Hello rabindradalai, I can't reproduce this error but I think it is because the movId column is the primary key of the table. You might be better fetching the entity first with GetEntity(id) and then updating it.
If you have any other questions then post them on the CodePlex site:
http://www.codeplex.com/MultiTierLinqToSql

NEW POST: Problem on Update

rabindradalai — Mon, 03 Nov 2008 08:12:19 GMT

RTPeat wrote:
Found the bug, problem solved.

Hi RTPeat, what was the problem you are facing..and how did you solved it. is it similar to the one that i am facing below:

Hi,

I have cerated my own DBMovieController<TEntity> which is inherited from GenericController<TEntity, DBMovieDataContext> where TEntity : class

when i try to call the following code

tblMain manupdate = new tblMain();
manupdate.movID = "3";
manupdate.movTitle = "FANNA !";
manupdate.movRunTime = 30;
DBMovieController<tblMain>.Update(manupdate);

it throws following error, but my movID is not a identity column in tblMain.

Value of member 'movID' of an object of type 'tblMain' changed.
A member defining the identity of the object cannot be changed.
Consider adding a new object with new identity and deleting the existing one instead

please give some inputs..

NEW POST: Problem on Update

rabindradalai — Mon, 03 Nov 2008 08:01:22 GMT

Hi,

I have cerated my own DBMovieController<TEntity> which is inherited from GenericController<TEntity, DBMovieDataContext> where TEntity : class

when i try to call the following code

tblMain manupdate = new tblMain();
manupdate.movID = "3";
manupdate.movTitle = "FANNA !";
manupdate.movRunTime = 30;
DBMovieController<tblMain>.Update(manupdate);

it throws following error, but my movID is not a identity column in tblMain.

Value of member 'movID' of an object of type 'tblMain' changed.
A member defining the identity of the object cannot be changed.
Consider adding a new object with new identity and deleting the existing one instead

please give some inputs..

UPDATED WIKI: Home

rbates — Thu, 30 Oct 2008 17:56:17 GMT

This project is now being hosted on CodePlex

http://www.codeplex.com/MultiTierLinqToSql

Please post any questions etc. over there as this site will no longer be updated

Multi tier architecture for Linq to Sql

Introduction

This resource shows an approach for using Linq to Sql within a multi tier Asp.Net application, and particularly how it can be made to work well with ObjectDataSources. It is here as a discussion piece as well as a usable bit of code.

The key features are a Request-scoped datacontext and entity controller classes which provide easy to use Create, Retrieve, Update and Delete (CRUD) operations implemented with Generics. It also discusses the use of DataLoadOptions and approaches to submitting changes back to the database.

Entity Controller classes

Each database entity has an associated controller class which is used to perform common CRUD operations. Specific entity controllers inherit from a generic class GenericController which already implements all the basic Select, Insert, Update & Delete operations.

[System.ComponentModel.DataObject]
public class GenericController<TEntity, TDataContext> where TDataContext : DataContext
{
    public static List<TEntity> SelectAll()
    {
          ...
    }
    public static void Insert(TEntity entity)
    {
        ...
    }
    public static void Update(TEntity entity)
    {
        ...
    }
    public static void Delete(TEntity entity)
    {
        ...
    }
}

Specific controllers then inherit from this by specifying the entity type which they are concerned with as well as the type of the DataContext used, as follows:

//ProductController class
public class ProductController : GenericController<Product, NorthwindDataContext>
{
}

which allows client code such as the following:

   IList<Product> products = ProductController.SelectAll();

The use of Generics means that I don't need to add any code at all into my ProductController to get all the standard CRUD operations in a fully type safe way. My ProductController class can now be customised to add any further data access methods which are specific to Products, for instance GetByCategoryId().

Request scoped DataContext

The second part of this solution is the request scoped DataContext. This is a pattern that is commonly used with the Hibernate OR mapper as well as elsewhere, and it means that there is only ever one DataContext per Http request which is stored in the HttpContext.Items collection. It is implemented as a protected property of the GenericController class as follows:

        protected static TDataContext DataContext
        {
            get
            {
                //We are in a web app, use a request scope
                if (HttpContext.Current != null)
                {
                    TDataContext _dataContext = (TDataContext)HttpContext.Current.Items["_dataContext"];
                    if (_dataContext == null)
                    {
                        _dataContext = Activator.CreateInstance<TDataContext>();
                        HttpContext.Current.Items.Add("_dataContext", _dataContext);
                    }
                    return _dataContext;
                }
                else
                {
                    //If this is not a web app then just create a datacontext
                    //which will have the same lifespan as the app itself
                    //This is only really to support unit tests and should not
                    //be used in any production code. A better way to use this
                    //code with unit tests is to mock the HttpContext
                    if (_dataContext == null)
                    {
                        _dataContext = Activator.CreateInstance<TDataContext>();
                    }
                    return _dataContext;
                }
            }
        }

Using the HttpContext.Items collection does introduce a dependency on System.Web to your data layer, but I've made the decision that since this solution is specifically designed for use in Asp.Net applications, this is acceptable. Checking for a valid HttpContext and then using an static variable if it doesn't exist gives a bit of flexibility to use the class outside of a web app, for instance in unit tests, although this will produce subtly different results, so mocking the HttpContext would probably be a better approach.

The usefulness of this approach can be seen if you consider a page which uses a number of ObjectDataSources; if we configure our ObjectDataSource to use the DataContext directly then each one will create and dispose of a new DataContext this will not only quickly become a performance overhead, but will also present us with a problem when specifying LoadOptions for the DataContext resulting in more complex, less maintainable code or quite possibly code which doesn't specify loading options at all.

It should be noted that this approach means that if I have a CategoryController class as well as a ProductController class, both of which inherit from GenericController, both of these classes will use the same DataContext. In other words the DataContext is shared across the whole request, not just by one class type.

Custom ObjectDataSource

In order for updates to work correctly with databinding it is necessary to use the GenericObjectDataSource control which is included in the DataLayer project. For more discussions of the details behind this, see https://code.msdn.microsoft.com/Thread/View.aspx?ProjectName=multitierlinqtosql&ThreadId=488

DataLoadOptions

Before going any further, we need to look at the issue of DataLoadOptions. As a number of people have noted the way that Linq to Sql works, means that the DataLoadOptions for your DataContext need to be set before any operations are carried out, and cannot subsequently be changed. This means that in my Asp.Net application I will need to set the DataLoadOptions for the whole request somewhere near the start (assuming of course that I need to set DataLoadOptions at all). It also means that my UI or business layer will need to take responsibility for this.

What this means in practice is that in a classic Web forms type app I will probably set the DataLoadOptions in the OnInit event of my page, if I'm using MVC then my controller will do it. I don't really have a problem with this, particularly in the case of an MVC app as I think this is probably the best place to actually specify how you want your data loading logic to work. I'm interested to hear other opinions on this.

Submitting changes

Another feature of the Linq to Sql architecture is the way that changes are only submitted to the database when the SubmitChanges() method is called on the DataContext. This means that we must either decide at which point to call SubmitChanges() or delegate this responsibility to client code.
To make the classes as flexible as possible any operations which require a call to SubmitChanges (Insert, Update and Delete), are provided with a boolean parameter which specify whether to submit changes immediately or not. There is also an overload of these methods without this parameter which is equivalent to passing true (again this makes use of ObjectDatasources much more straightforward).
The SubmitChanges method of the DataContext is also exposed through the controller class to give Client code the ability to call this explicitly.

New stuff added in Release 1.2

There are now a number of new methods added to the GenericController class to automatically support paging and sorting in GridViews.
These are:

public static IQueryable<TEntity> SelectAll(int maximumRows, int startRowIndex)
{
...
}
public static IQueryable<TEntity> SelectAll(string sortExpression)
{
...
}

public static IQueryable<TEntity> SelectAll(string sortExpression, int maximumRows, int startRowIndex)
{
...
}
public static int Count()
{
...
}

To use this functionality all you need to do is turn it on in the ObjectDataSource and GridView with the following attributes:
ObjectDataSource:
EnablePaging="true" SelectCountMethod="Count" SortParameterName="sortExpression"

Gridview:
AllowPaging="true" AllowSorting="true"

And hey presto it just works! This makes it really easy to create sortable, pagable GridViews with no extra coding.

This release also includes some code from Scott Gu's blog to support Dynamic Querying, see http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx for more details

New stuff in release 1.6

Logging

There is now an easy way to configure Linq to Sql's logging feature (DataContext.Log) to write to either a text file or the System.Diagnostics.Debugger class which then appears in the output window within Visual Studio.

To use this functionality you need to add the following lines into your web.config:

In the <configSections> element:

<section name="GenericDataSection" type="DataLayer.GenericDataSection, DataLayer" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>

Then directly after <configSections>:

<GenericDataSection LogType="file" LogFile="C:\Projects\MultiTierLinqToSql_release\test.txt" DeleteExistingFile="true"/>

The first of these lines just declares the second line which is the one that contains the interesting stuff.
There are 3 config options in this section:

LogType - this can be either 'file' (logs output to a file), 'debug' (logs output to Systems.Diagnostics.Debug) or 'none' (switches logging off).
LogFile - only applies when LogType is set to 'file', and the default is 'log.txt' it specifies the name of the file to use for logging.
DeleteExistingFile - only applies when LogType is set to 'file', values can be 'true' or 'false', and the default is true. When this is set to true the file will be overwritten every time a new DataContext is created, effectively once per request. If set to false then all output is appended to the end of the file. This can result in a very large log files so be careful.

Notes

As with all file logging this can be pretty detrimental to performance and so should only be used when debugging.

To handle the logging to the output window I have used the DebuggerWriter class described here: http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11

The code

The code consists of 2 projects

The data access layer - this is made up of Linq to Sql dataclasses generated from the Northwind database, a class GenericController (discussed above) and two concrete subclasses ProductController and CategoryController.
A test web project - this project has one page with a GridView and a FormView which use the data layer via ObjectDataSources to list all the products in the Northwind database, allow editing and deletion of those projects, and insert new products via the FormView.

To run the example project:

Download and unzip the release
Open the solution in VS2008
Change the connection string in the web.config to point to a Northwind database
Run the web project

To use the code in your own projects

Copy the class GenericController into your Linq to Sql project
Create one entity controller class for each Linq to Sql entity, inheriting from GenericController
Optionally define any new entity specific data access operations in your entity controller classes

UPDATED WIKI: Home

rbates — Thu, 30 Oct 2008 17:55:59 GMT

This project is now being hosted on CodePlex

http://www.codeplex.com/MultiTierLinqToSql

Please post any questions etc. over there as this site will no longer be updated

Multi tier architecture for Linq to Sql

Introduction

Entity Controller classes

[System.ComponentModel.DataObject]
public class GenericController<TEntity, TDataContext> where TDataContext : DataContext
{
    public static List<TEntity> SelectAll()
    {
          ...
    }
    public static void Insert(TEntity entity)
    {
        ...
    }
    public static void Update(TEntity entity)
    {
        ...
    }
    public static void Delete(TEntity entity)
    {
        ...
    }
}

Specific controllers then inherit from this by specifying the entity type which they are concerned with as well as the type of the DataContext used, as follows:

//ProductController class
public class ProductController : GenericController<Product, NorthwindDataContext>
{
}

which allows client code such as the following:

   IList<Product> products = ProductController.SelectAll();

Request scoped DataContext

        protected static TDataContext DataContext
        {
            get
            {
                //We are in a web app, use a request scope
                if (HttpContext.Current != null)
                {
                    TDataContext _dataContext = (TDataContext)HttpContext.Current.Items["_dataContext"];
                    if (_dataContext == null)
                    {
                        _dataContext = Activator.CreateInstance<TDataContext>();
                        HttpContext.Current.Items.Add("_dataContext", _dataContext);
                    }
                    return _dataContext;
                }
                else
                {
                    //If this is not a web app then just create a datacontext
                    //which will have the same lifespan as the app itself
                    //This is only really to support unit tests and should not
                    //be used in any production code. A better way to use this
                    //code with unit tests is to mock the HttpContext
                    if (_dataContext == null)
                    {
                        _dataContext = Activator.CreateInstance<TDataContext>();
                    }
                    return _dataContext;
                }
            }
        }

Custom ObjectDataSource

DataLoadOptions

Submitting changes

New stuff added in Release 1.2

New stuff in release 1.6

Logging

LogType - this can be either 'file' (logs output to a file), 'debug' (logs output to Systems.Diagnostics.Debug) or 'none' (switches logging off).
LogFile - only applies when LogType is set to 'file', and the default is 'log.txt' it specifies the name of the file to use for logging.
DeleteExistingFile - only applies when LogType is set to 'file', values can be 'true' or 'false', and the default is true. When this is set to true the file will be overwritten every time a new DataContext is created, effectively once per request. If set to false then all output is appended to the end of the file. This can result in a very large log files so be careful.

Notes

The code

The code consists of 2 projects

The data access layer - this is made up of Linq to Sql dataclasses generated from the Northwind database, a class GenericController (discussed above) and two concrete subclasses ProductController and CategoryController.
A test web project - this project has one page with a GridView and a FormView which use the data layer via ObjectDataSources to list all the products in the Northwind database, allow editing and deletion of those projects, and insert new products via the FormView.

To run the example project:

Download and unzip the release
Open the solution in VS2008
Change the connection string in the web.config to point to a Northwind database
Run the web project

To use the code in your own projects

Copy the class GenericController into your Linq to Sql project
Create one entity controller class for each Linq to Sql entity, inheriting from GenericController
Optionally define any new entity specific data access operations in your entity controller classes

UPDATED RELEASE: Release 1.7 (Oct 30, 2008)

Thu, 30 Oct 2008 17:02:48 GMT

! Release 1.7
This project is now being hosted over on CodePlex
[url:http://www.codeplex.com/MultiTierLinqToSql]
Please get the release from there.

!! Release notes

This release contains a number of fixes to the way the GenericObjectDatasource works, mainly to make error handling function correctly. If an error is thrown during an insert or update, the exception is now passed into the Updated or Inserted event of the GenericObjectDatasource, so that it can be handled by client code.
See the Error Handling section of the main page for more details.

This release has also refactored the code, so that all the generic code is now kept in one assembly GenericDataLayer and the project specific stuff has been moved out into another assembly. This makes it easier to use this code in your projects without having to remove example code from the main project.

CREATED RELEASE: Release 1.7 (Oct 30, 2008)

Thu, 30 Oct 2008 16:55:56 GMT

This project is now being hosted over on CodePlex
[url:http://www.codeplex.com/MultiTierLinqToSql]
Please get the release from there.

NEW POST: Question - Error handling

rbates — Thu, 30 Oct 2008 16:52:27 GMT

Hello beljakm, to answer your question, the way you should handle this is in the OnUpdated/OnInserted event of your GenericObjectDataSource using code like the following:

 protected void ProductDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e)
    {
        if (e.Exception != null)
        {
            //litError is a literal control I am using to display the error to the user
            litError.Text = e.Exception.Message;
            e.ExceptionHandled = true;
        }
    }

Basically you check if an exception has occurred, do something with it, probably show the details to the user, and then tell the GenericObjectDatasource that you've handled it. To use this you'll need to get version 1.7 as there was a bug in the way this worked in previous versions.
Note that I'm moving the code over to CodePlex, so you can now use svn to get the latest source, should be easier for everyone!
Rupert

NEW POST: Question - Error handling

beljakm — Tue, 21 Oct 2008 15:14:43 GMT

Hello,

I have probably posted my question in the wrong discussion group earlier, so I am re-posting it.

rbates - the solution is very elegant, and its evolving. However, here is a question:

Where and how do you handle events, e.g. OnLastNameChanged() in your person class? If I extend Person class, as shown below, and an exception is thrown, I keep getting error in GenericObjectDataSourceView because PropertyDescriptor, SetValue property does not handle ArgumentException. Should I somehow handle this in PersonController? Any easy way to get around this? This based on ScottGu's blog.

namespace DataLayer
{
public partial class Person
{
partial void OnLastNameChanged()
{
if (this.LastName=="")
throw new Exception("Error: Last Name cannot be empty.");
}
}
}
P.S.
The example below is trivial, and it is only for illustrative purposes. I would like to know what is the best way to handle DataContext events.

NEW POST: Using LINQ to sql in three tier architecture with ObjectDataSource and BusinessObject

beljakm — Sat, 18 Oct 2008 15:26:46 GMT

I have been rapidly getting into generics, and learning its powers. Examples shown here are great. However, here is a question:

Where and how do you handle events, e.g. OnLastNameChanged() in your person class? If I extend Person class, as shown below, and an exception is thrown, I keep getting error in GenericObjectDataSourceView because PropertyDescriptor, SetValue property does not handle ArgumentException. Should I somehow handle this in PersonController? Any easy way to get around this?

namespace DataLayer
{
public partial class Person
{
partial void OnLastNameChanged()
{
if (this.LastName=="")
throw new Exception("Error: Last Name cannot be empty.");
}
}
}
P.S.
The example below is trivial, and it is only for illustrative purposes. I would like to know what is the best way to handle DataContext events.

UPDATED RELEASE: Release 1.6.1 (Oct 11, 2008)

Sat, 11 Oct 2008 10:58:01 GMT

Just an update to 1.6 to replace a missing attribute in the test project

1.6 release notes:

This release includes some new classes to support Logging as well as a change to the way that the code functions outside of an asp.net application.
!! Logging
There is now an easy way to configure Linq to Sql's logging feature (DataContext.Log) to write to either a text file or the System.Diagnostics.Debugger class which then appears in the output window within Visual Studio.

To use this functionality you need to add the following lines into your web.config:

In the <configSections> element:

<section name="GenericDataSection" type="DataLayer.GenericDataSection, DataLayer" allowDefinition="MachineToApplication" restartOnExternalChanges="true" requirePermission="false"/>

Then directly after <configSections>:

<GenericDataSection LogType="file" LogFile="C:\Projects\MultiTierLinqToSql_release\test.txt" DeleteExistingFile="true"/>

The first of these lines just declares the second line which is the one that contains the interesting stuff.
There are 3 config options in this section:
# LogType - this can be either 'file' (logs output to a file), 'debug' (logs output to Systems.Diagnostics.Debug) or 'none' (switches logging off).
# LogFile - only applies when LogType is set to 'file', and the default is 'log.txt' it specifies the name of the file to use for logging.
# DeleteExistingFile - only applies when LogType is set to 'file', values can be 'true' or 'false', and the default is true. When this is set to true the file will be overwritten every time a new DataContext is created, effectively once per request. If set to false then all output is appended to the end of the file. This can result in a very large log files so be careful.

!!! Notes
As with all file logging this can be pretty detrimental to performance and so should only be used when debugging.

To handle the logging to the output window I have used the DebuggerWriter class described here: [url:http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11]

!! Changes to DataContext creation outside of Asp.Net apps
For situations where an HttpContext does not exist (ie. where the code is being used outside of a web app) the DataContext is now stored in Thread local storage. See this post [url: https://code.msdn.microsoft.com/Thread/View.aspx?ProjectName=multitierlinqtosql&ThreadId=361] for more information.

CREATED RELEASE: Release 1.6.1 (Oct 11, 2008)

Sat, 11 Oct 2008 10:55:12 GMT

just an update to 1.6 to replace a missing attribute in the test project

NEW POST: Sorting with IEnumerable data not supported

rbates — Sat, 11 Oct 2008 10:46:53 GMT

Tess wrote:
Hello. Great discussion. I ran into a problem with the sample project, however, which I don't see discussed elsewhere. I get the error "data source 'ProductDataSource' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet."
Did anyone else see that? Fix that?
Cheers,
Tess

Hi Tess, the fix is to add the attribute SortParameterName="sortExpression" into the ProductDataSource. I don't know how that got broken in the example project but I'll update it now, thanks for pointing it out.

NEW POST: Sorting with IEnumerable data not supported

Tess — Fri, 26 Sep 2008 17:18:19 GMT

Hello. Great discussion. I ran into a problem with the sample project, however, which I don't see discussed elsewhere. I get the error "data source 'ProductDataSource' does not support sorting with IEnumerable data. Automatic sorting is only supported with DataView, DataTable, and DataSet."
Did anyone else see that? Fix that?
Cheers,
Tess

NEW POST: EntitySet property or seperate controller

rbates — Sun, 21 Sep 2008 19:35:04 GMT

I'd definitely go for option 1. Not that there's anything particularly wrong with option 2 it's just more code for no gain. Also if the child table only makes sense in the context of the parent table then it's better practice to deal primarily with that table.

NEW POST: Question - Error handling

tc — Thu, 18 Sep 2008 05:05:30 GMT

Hi rbates, I was thinking the same thing by implementing a static close method or alike dispose of the datacontext by request.

It is a little more verbose, but if I could overcome my other requirement using the static implementation then I would go with the static approach, however I cannot think of another way of doing this, unless you have some suggestions? I need to be able to use a controller(s) dynamically to insert and update (using reflection and configuration), i'm using your GenericController as the data layer component of a larger generic application i am working on.

The idea is to feed my generic business components with a controller (based on the GenericController class and an interface I have implemented) and using reflection I create/insert/update entities regardless of the type. I haven't been able to figure out a way to do this with the static approach, any ideas?

I've attached the interface I have implemented, which basically just enforces additional overloads to create and insert entity objects without having to know the type at compile time. I couldn't think of any other way to go about doing this without knowing the types at compile time. I've implemented CreateEntity methods in the GenericController class to create, insert and return the correct entity, including non-generic methods.

I would appreciate your thoughts on what I have done and if you think I should/could have done things different/better.

Cheer
TC

public interface IController
{
void Insert(object entity);
...

object CreateObject();
...

void SubmitChanges();
}

public class GenericController<TEntity, TDataContext> : IController, IDisposable ...
{
...
...
...

public void Insert(object entity)
{
Insert(entity as TEntity)
}

public TEntity CreateEntity()
{
TEntity entity = new TEntity();
Insert(entity)

return entity;
}

public object CreateObject() // Same as CreateEntity but couldn't overload because of existing definition
{
return CreateEntity() as object;
}
}

NEW POST: How to use in NON ASP.NET code sections?

DavidB — Thu, 18 Sep 2008 01:46:07 GMT

Hi,

Thanks for the framework - very helpful!

It would be handy to be able to undo changes made to the datacontext in non-asp.net applications. As far as I can see there is no way to discard changes made to tables in a datacontext without destroying it. What about a method in the genericcontroller to destroy the datacontext, so then the next time a DataContext is called a new one is created?

Thanks,
David.

NEW POST: Question - Error handling

rbates — Wed, 17 Sep 2008 15:31:51 GMT

Hi tc, I think the approach you suggest is fine, it makes the code a bit more verbose but that's just a cosmetic thing really. Another thought I had would be to allow the calling code to tell the controller to dispose of the datacontext when it needed to, this would be useful in a number of other scenarios as well so I'll probably add it in at some point.