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:
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
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
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!