ASP.NET MVC Tip #23 – Use POCO LINQ to SQL Entities
In this tip, I demonstrate how you can create LINQ to SQL entities that do not contain any special attributes. I show you how you can use an external XML file to map LINQ to SQL entities to database objects.
I’ve talked to several people recently who are deeply bothered by the fact that the LINQ to SQL classes generated by the Visual Studio Object Relational Designer contain attributes. They want to take advantage of the Object Relational Designer to generate their entity classes. However, they don’t like the fact that the generated entities are decorated with a bunch of attributes.
For example, if you use the Object Relational Designer to generate a LINQ to SQL class that corresponds to the Movies database table, then you get the class in Listing 1. This class is generated in the Movies.Designer.cs file.
Listing 1 – Movie Class (abbreviated)
[Table(Name="dbo.Movies")]
public partial class Movie : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
private int _Id;
private string _Title;
[Column(Storage="_Id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int Id
{
get
{
return this._Id;
}
set
{
if ((this._Id != value))
{
this.OnIdChanging(value);
this.SendPropertyChanging();
this._Id = value;
this.SendPropertyChanged("Id");
this.OnIdChanged();
}
}
}
[Column(Storage="_Title", DbType="NVarChar(100) NOT NULL", CanBeNull=false)]
public string Title
{
get
{
return this._Title;
}
set
{
if ((this._Title != value))
{
this.OnTitleChanging(value);
this.SendPropertyChanging();
this._Title = value;
this.SendPropertyChanged("Title");
this.OnTitleChanged();
}
}
}
}
Notice that the class in Listing 1 includes both [Table] and [Column] attributes. LINQ to SQL uses these attributes to map classes and properties to database tables and database table columns.
Some people are disturbed by these attributes. They don’t want to mix their persistence logic with their domain entities. They want to use POCO objects (Plain Old CLR Objects) for their entities.
Fortunately, LINQ to SQL supports two methods of mapping classes to database objects. Instead of using the default AttributeMappingSource, you can use the XmlMappingSource. When you use the XmlMappingSource, you use an external XML file to map classes to database objects.
You can create the XML file by hand or you can use the SqlMetal.exe command line tool. You run SqlMetal.exe from the Visual Studio Command Prompt (Start, All Programs, Microsoft Visual Studio 2008, Visual Studio Tools, Visual Studio 2008 Command Prompt).
Here’s how you use SqlMetal.exe to create an XML mapping file from a RANU SQL Express database named MoviesDB.mdf:
1. Navigate to the folder containing the MoviesDB.mdf database
2. Execute the following command:
SqlMetal /dbml:movies.dbml MoviesDB.mdf
3. Execute the following command:
SqlMetal /code:movies.cs /map:movies.map movies.dbml
After you execute these commands, you will end up with three files:
· movies.dbml – The movies database markup file
· movies.cs – The movie classes that correspond to the database objects
· movies.map – The XML map file that maps the classes to the database objects
After you generate these files, you can add the movies.cs and movies.map file to your ASP.NET MVC application’s Models folder.
The C# Movie class file from the movies.cs is contained in Listing 2. The file in Listing 2 is almost exactly the same as the file in Listing 1 except for the fact that the file does not contain any special attributes. The Movie class in Listing 2 is a POCO object.
Listing 2 – Movie Class (abbreviated)
public partial class Movie : INotifyPropertyChanging, INotifyPropertyChanged
{
private int _Id;
private string _Title;
public int Id
{
get
{
return this._Id;
}
set
{
if ((this._Id != value))
{
this.OnIdChanging(value);
this.SendPropertyChanging();
this._Id = value;
this.SendPropertyChanged("Id");
this.OnIdChanged();
}
}
}
public string Title
{
get
{
return this._Title;
}
set
{
if ((this._Title != value))
{
this.OnTitleChanging(value);
this.SendPropertyChanging();
this._Title = value;
this.SendPropertyChanged("Title");
this.OnTitleChanged();
}
}
}
}
The file in Listing 3 contains the XML mapping file generated by the SqlMetal.exe tool. You could create this file by hand.
Listing 3 – movies.map
<?xml version="1.0" encoding="utf-8"?>
<Database Name="MoviesDB" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Movies" Member="Movies">
<Type Name="Movie">
<Column Name="Id" Member="Id" Storage="_Id" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
<Column Name="Title" Member="Title" Storage="_Title" DbType="NVarChar(100) NOT NULL" CanBeNull="false" />
</Type>
</Table>
</Database>
After you create an external XML mapping file, you must pass the mapping file to a DataContext object when you initialize the DataContext. For example, the controller in Listing 4 uses the movies.map file within its Index() method.
Listing 4 – HomeController.cs
using System.Data.Linq.Mapping;
using System.Linq;
using System.Web.Configuration;
using System.Web.Mvc;
namespace Tip23.Controllers
{
[HandleError]
public class HomeController : Controller
{
public ActionResult Index()
{
// Create data context
var connectionString = WebConfigurationManager.ConnectionStrings["movies"].ConnectionString;
var map = XmlMappingSource.FromUrl(Server.MapPath("~/Models/Movies.map"));
var dataContext = new MoviesDB(connectionString, map);
// Get movies
var movies = from m in dataContext.Movies select m;
// Return movies in view
return View(movies.ToList());
}
}
}
The Index() starts by retrieving a connection string from the Web configuration file. Next, the XML mapping file is loaded from the Models folder. The connection string and mapping source are passed to the DataContext constructor when the DataContext is created. Finally, a list of movies is retrieved from the database and sent to the view.
The point of this tip was to demonstrate that you can use an external XML file instead of attributes within your LINQ to SQL classes to map your classes to your database objects. Some people don’t want to dirty their classes with database persistence logic. LINQ to SQL is flexible enough to make these people happy.