Subscribe to this Blog

Subscribe to this Blog

ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods - Stephen Walther on ASP.NET MVC

ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

In this tip, Stephen Walther demonstrate how you can create new LINQ to SQL extension methods that enable you to dramatically reduce the amount of code that you are required to write for typical data access scenarios.

By taking advantage of LINQ to SQL, you can dramatically reduce the amount of code that you need to write to access a database. LINQ to SQL has given me back several weeks of my life that I would have otherwise wasted in writing tedious ADO.NET code. In short, I am a fan.

However, LINQ to SQL is a general data access technology. It is not specific to ASP.NET MVC. You can use LINQ to SQL when building Windows Forms applications, console applications, or just about any other type of Microsoft application. For this reason, it is not optimized for ASP.NET MVC. You are required to write more lines of code to access a database within an ASP.NET MVC application than strictly necessary.

In this tip, I streamline (optimize? reinterpret? re-imagine?) LINQ to SQL for ASP.NET MVC. I show how you can add extension methods to LINQ to SQL that enables you to reduce the amount of code that you must write for common data access scenarios to a single line of code.

For example, normally you need to write the following code to add a new Movie database record to the database:

var movieToAdd = new Movie();
movieToAdd.Title = title;
movieToAdd.Director = director;
movieToAdd.DateReleased = dateReleased;
_dataContext.Movies.InsertOnSubmit(movieToAdd);
_dataContext.SubmitChanges();

You need four lines of code to create a new Movie and set its properties. You need an additional two lines of code to add the new Movie to the database (InsertOnSubmit() and SubmitChanges()).

After we add our extension methods, we can do the same thing with the following line of code:

_dataContext.Insert<Movie>(formParams);

With this tip, you can save another few weeks of your life for more useful things like seeing movies, going to the park, baking cakes, building new MVC view engines, and so on.

Using the LINQ to SQL Extension Methods

You can use the LINQ to SQL extension methods with an ASP.NET MVC project by following these three steps:

1) Download the LinqToSqlExtensions project attached to the end of this blog entry. After downloading the file, right-click the file and select Properties. Next, click the Unblock button. After you unblock the file, unzip the file.

2) Within an ASP.NET MVC application, add a reference to the LinqToSqlExtensions assembly. Select the menu option Project, Add Reference and browse to the LinqToSqlExtensions.dll assembly located in the \LinqToSqlExtensions\Bin\Debug folder.

3) Add a using LinqToSqlExtensions statement to any controller in which you want to use the extensions.

The LInqToSqlExtensions project contains a single class, named DataContextExtensions, which contain the LINQ to SQL extensions. Unfortunately, the source for this class is a little too long to include in this blog entry.

The DataContextExtensions class adds the following methods to the DataContext:

· Select() – Enables you to select all records from a database table

· Get() – Enables you to get a single record from a database table

· Save() – Enables you to either insert a new record or update an existing record

· Insert() – Enables you to insert a new record

· Update() – Enables you to update an existing record

· Delete() – Enables you to delete an existing record

The Save(), Insert(), and Update() methods accept a NameValueCollection of form parameters. For example, you insert a new record with the following line of code:

_dataContext.Insert<Movie>(formParams);

There are two ways that you can get a reference to the form parameters in a controller action. First, you can use the Request.Form collection like this:

        public ActionResult Insert()
        {
            _dataContext.Insert<Movie>(Request.Form);
            return RedirectToAction("Index");
        }

Alternatively, you create a custom action invoker and pass the form parameters to the action method like this:

        public ActionResult Insert(NameValueCollection formParams)
        {
            _dataContext.Insert<Movie>(formParams);
            return RedirectToAction("Index");
        }

I discuss creating a custom action invoker in the following tip:

http://weblogs.asp.net/stephenwalther/archive/2008/07/11/asp-net-mvc-tip-18-parameterize-the-http-context.aspx

I prefer this second method because it is more testable. If you create a custom action invoker then you can test an action method in your unit tests by creating a new NameValueCollection and passing it to the controller action. I use this second method in the rest of the code samples in this tip.

Creating a Controller with the Extension Methods

I’m going to show you two ways that you can use the extensions methods. First, I’m going to create a controller that contains separate actions for displaying, inserting, updating, and deleting Movie database records. Next, I’m going to show you how you can combine the Insert() and Update() actions into a single Save() action.

The Home controller in Listing 1 contains separate Insert() and Update() methods. This controller can be used when building a Movie database application. It contains all of the methods that you need to manage a simple Movie database (see Figure 1).

Figure 1 – Movie Database Application

clip_image002

Listing 1 -- \Controllers\HomeController.cs

using System.Web.Mvc;

using LinqToSqlExtensions;
using Tip38.Models;
using System.Collections.Specialized;

namespace Tip38.Controllers
{
    public class HomeController : Controller
    {
        private MovieDataContext _dataContext;

        public HomeController()
        {
            _dataContext = new MovieDataContext();
        }

        public ActionResult Index()
        {
            return View("Index", _dataContext.Select<Movie>());
        }

        public ActionResult Details(int id)
        {
            return View("Details", _dataContext.Get<Movie>(id));
        }

        public ActionResult Edit(int id)
        {
            return View("Edit", _dataContext.Get<Movie>(id));
        }

        public ActionResult Update(int id, NameValueCollection formParams)
        {
            _dataContext.Update<Movie>(formParams, id);
            return RedirectToAction("Index");
        }

        public ActionResult Delete(int id)
        {
            _dataContext.Delete<Movie>(id);
            return RedirectToAction("Index");
        }

        public ActionResult Create()
        {
            return View("Create");
        }

        public ActionResult Insert(NameValueCollection formParams)
        {
            _dataContext.Insert<Movie>(formParams);
            return RedirectToAction("Index");
        }

    }
}

Notice that the data access code for each action has been condensed to a single line. The LINQ to SQL extension methods perform all of the work behind the scenes.

If you prefer, you can combine the Insert() and Update() methods into a single action. I took this approach when writing the Movie controller in Listing 2.

Listing 2 – MovieController.cs

using System.Web.Mvc;

using LinqToSqlExtensions;
using Tip38.Models;
using System.Collections.Specialized;

namespace Tip38.Controllers
{
    public class MovieController : Controller
    {
        private MovieDataContext _dataContext;

        public MovieController()
        {
            _dataContext = new MovieDataContext();
        }

        public ActionResult Index()
        {
            return View("Index", _dataContext.Select<Movie>());
        }

        public ActionResult Edit(int? id)
        {
            return View("Edit", _dataContext.Get<Movie>(id));
        }

        public ActionResult Update(int? id, NameValueCollection formParams)
        {
            _dataContext.Save<Movie>(formParams, id);
            return RedirectToAction("Index");
        }


    }
}

The controller in Listing 2 uses the same view to display an XHTML form for inserting and updating a Movie database record (see Figure 2). This form is displayed by the Edit() controller action. The form is submitted to the Update() controller action. The Update() action calls the Save() method to either insert or update a Movie record.

Here’s how the Save() method works. If you call the Save() method with an Id parameter with a value of 0 or null, then the Save() method calls the Insert() method. Otherwise, the Save() method performs an update on the record with the matching Id.

Figure 2 – \Views\Movie\Edit.aspx

clip_image004

Summary

Never forget that the ASP.NET MVC framework is a framework. You always have the option of customizing it to fit your particular needs. In this tip, I demonstrated how you can add extension methods to LINQ to SQL to dramatically reduce the amount of data access code that you are required to write in typical data access scenarios. Save yourself some time! Use this tip!

Download the Code

Published Tuesday, August 26, 2008 9:35 AM by swalther
Filed under: , ,

Comments

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 2:04 PM by paul.vencill

Stephen,

Love this technique.  It occurred to me that for many apps you could greatly simplify the design of a number of controllers by building a generic base class templated off your MovieController, then subclass to create the specific model controllers.  You'd just pass the model type in through the generic type parameter, possibly even both the model and the datacontext (design depending), and the rest could be handled in a very generic fashion.  Then you just code to the actions that don't follow the convention for whatever reason.

Paul

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 2:36 PM by swalther

@Paul - I think you describe a great approach. Take a look at the following tips:

weblogs.asp.net/.../asp-net-mvc-tip-4-create-a-custom-data-controller-base-class.aspx

weblogs.asp.net/.../asp-net-mvc-tip-5-create-shared-views.aspx

In these tips, I create a DataController base class and a set of shared views to display the data automatically.

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 3:05 PM by paul.vencill

yep, saw those before.  I was contemplating a merged approach between tip 4 and this one, to leverage the datacontext extensions that you describe here.  

Thanks,

Paul

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 3:10 PM by rgoytacaz

Hello there Stephen,

I used to program using J2EE.. I'm into .NET Now and I love your videos, I've watched every single one of them from ASP.net website. Still reading your blog posts, a lot to catch up. Specially about MVC its being really helpful, looking forward to buy your MVC book.

One thing I would like you to answer me is that, I'm designing a MVC app using LINQ to SQL and repository pattern, but I'm stuck with a DB2 8.1 which I need to pull information from. I've read that you can't use that relational designer for anything other than MS SQL Server, but I really enjoyed that approach, How can I build something like that for DB2?

Cordially,

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 4:34 PM by Vladan Strigo [MVP]

Hi Stephen,

2 cents...

I follow your blog (actually weblogs.asp.net feed) and I see that lately mostly mvc tips are actually linq to sql tips.

First and foremost this sounds wrong (if its mvc, talk about mvc!), secondly it gives the impression that linq to sql is a part of asp.net mvc, and it is not.

Thanks,

Vladan

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 4:41 PM by swalther

@rgoytacaz - Thanks for the comments on the videos, glad you liked them!  It sounds like the Microsoft Entity Framework is a better fit for your needs. The Entity Framework does not officially support Oracle yet, but there have been public announcments that there should be Oracle support in the next few months. Microsoft does have a sample Oracle provider at:

code.msdn.microsoft.com/EFOracleProvider

Another option is NHibernate which does support Oracle right now.

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 4:58 PM by Peter

This is really neat ... but I'd also be nervous about trying to put form data directly into my database without any checking, validation and/or error handling.

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 5:15 PM by brad

this is really bad form.  You should have a (well tested) layer between the controller and the datacontext that handles this sort of thing.  Another bastardized use of an extension method (i have found some great uses of them to be sure, but nasty little bits like these keep popping up)

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 5:21 PM by ceilidhboy

Hi Stephen

Had a look at the source and ReSharper immediately highlighted an assignment that doesn't do anything. It's in DataContextExtensions.Update<TEntity>(), just before the final return:

 var table = dataContext.GetTable<TEntity>();

table isn't used.

Also, why does Update always return null? Were you intending to return the entity instance that's just been updated?

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 5:34 PM by swalther

@ceilidhboy - Thanks very much for catching this! Yes, the method should return the entityToUpdate. I'm updating the code download with the fixed version (and a VB.NET version).

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 5:37 PM by swalther

@Peter -- Yes, absolutely, there needs to be validation code here. I've been dancing around the issue of validation until more support is added for validation in the MVC framework.

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 7:23 PM by Claus

Great tip on extension methods. However, it strikes me that while this follows the more sloppy rails approach, in .NET shouldn't the db code move to a separate assembly/WCF service? As far as I'm concerned, the M in MVC is still part of the presentation layer and should only be responsible for accessing components to build it's model, i.e. get the data transport object from a service and serve them to a controller/view...Thoughts?

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 7:50 PM by panjkov

Actually, you can put those 4 statements into one (in your first listing) by writing

var movieToAdd = new Movie {

               Title = title,

               Director = director,

               DateReleased = dateReleased };

:)

IMHO, dataContext should not be accessed directly in controller action, but only through some (generic?) repository.

Did you tried to perform unit testing on these controllers?

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Tuesday, August 26, 2008 8:07 PM by swalther

@panjkov - I wrote the following tip on unit testing the DataContext within a controller:

weblogs.asp.net/.../asp-net-mvc-tip-33-unit-test-linq-to-sql.aspx

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Wednesday, August 27, 2008 5:27 PM by Daniel

I've been working on some similar extension methods.  An additional tweak you can make to the 'Get' methods is to use reflection to find the key property.  Obviously, there's a performance tradeoff, but you're using reflection anyway.  Caching the expression may also be possible...

 public static class IQueryableExtensions

   {

       /// <summary>

       /// Gets an entity by it's primary key value.  Note that this method uses reflection, which may affect performance.

       /// </summary>

       /// <typeparam name="T"></typeparam>

       /// <typeparam name="V"></typeparam>

       /// <param name="query"></param>

       /// <param name="value"></param>

       /// <returns></returns>

       public static IQueryable<T> GetByKey<T, V>(this IQueryable<T> query, V value)

           where T : class

       {

           var keys = GetKeyProperties(typeof(T));

           if (keys.Count() == 0 || keys.Count() > 1) throw new NotSupportedException("GetByKey currently only supports LINQ-to-SQL classes with a single primary key column.");

           var expression = BuildSelectByPropertyExpression<T>(keys.First(), value);

           return query.Where(expression);

       }

       private static Expression<Func<TEntity, bool>> BuildSelectByPropertyExpression<TEntity>(PropertyInfo propertyInfo, object value)

       {

           var e = Expression.Parameter(typeof(TEntity), "e");            

           var m = Expression.MakeMemberAccess(e, propertyInfo);

           var c = Expression.Constant(value);

           var b = Expression.Equal(m, c);

           var lambda = Expression.Lambda<Func<TEntity, bool>>(b, e);

           return lambda;

       }

       private static PropertyInfo[] GetKeyProperties(Type type)

       {

           var props = type.GetProperties();

           var keys = from prop in props where DefinesKeyAttribute(prop) select prop;

           return keys.ToArray();

       }

       private static bool DefinesKeyAttribute(PropertyInfo prop)

       {

           if (ColumnAttribute.IsDefined(prop, typeof(ColumnAttribute)))

           {

               var attribute = ColumnAttribute.GetCustomAttribute(prop, typeof(ColumnAttribute)) as ColumnAttribute;

               return attribute.IsPrimaryKey;

           }

           return false;

       }        

   }

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Thursday, August 28, 2008 2:22 PM by swalther

@Daniel -- Very cool methods. I think adding support for caching (especially SQL cache dependencies) is a great idea!

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Thursday, August 28, 2008 8:38 PM by Damien Guard

This technique of automatically calling SubmitChanges() on insert/update exposes a number of other problems for you to deal with such as order of related inserts, transactional rollback on failure and additional overhead for multiple operations.

I'm not convinced that saving typing one line at the end of your method is worth that.

[)amien

# re: ASP.NET MVC Tip #38 – Simplify LINQ to SQL with Extension Methods

Wednesday, October 1, 2008 4:26 PM by CelikICT - Web Tasarım

I love this technique. Thank you very much