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.

Discussion

  1. jdelator says:

    I don’t understand. What is so bad about using attributes?

  2. http:// says:

    And of course all those “this.” are extra to make the code look simpler…

  3. http:// says:

    If both the cs and xml files are embedded into the assembly than I really don’t see any benefit of using xml instead of attributes.

    In general I prefer using attributes.

    If you could show a way of breaking the SQL file into multiple files that would definitely make for a good tip ;)

    Anyway thanks .. and keep them coming.

  4. http:// says:

    Great tip, didn’t know that! That moves Linq-to-Sql a little more towards Linq-to-Entities. And thereby makes Linq-to-Sql an even more attractive choice!

  5. cibrax says:

    Hi Stephen,
    In order to have a complete POCO Model, you still have to get rid of the EntityRef and EntitySet collections for associations, which is a bad thing because you lose all the benefits of lazy loading.

    Also, I would not implement the data context initialization in the controller itself, it makes the controller method very hard to test. Specially for all the references to configuration that you have in there.

    Anyway, great work. Thanks
    Pablo.

  6. http:// says:

    Here’s an interesting explanation of why/how the INotifyPropertyChanging, INotifyPropertyChanged interfaces are used.

    davidhayden.com/…/2949.aspx

  7. http:// says:

    If your table changed you still have to re-generate the code of entities and compile your project? Is there work around not need to do that just replace the mapping file?

  8. http:// says:

    @Michael – You can change the mapping file directly without a recompile. You also can change the entity class files by hand (changing the class files would, of course, require a recompile). There is no reason that you can’t completely ignore SqlMetal.exe and do everything by hand.

  9. http:// says:

    Linq still doesn’t appear to be loosely coupled even with the map file. With sqlmetal, the map and partial entity class is created but the class still has references to Data.Linq which means it’s tightly coupled to Linq. Is there a way to use the map configuration without needing to reference Linq in the model?

  10. Great tips..
    thanks for sharing..

  11. Nice Post! Stephen your posts are always useful. Thanks for sharing.

  12. b42h I tried to mock a call to a Linq to SQL query, but I am struggling.

  13. With sqlmetal, the map and partial entity class is created but the class still has references to Data.Linq which means it’s tightly coupled to Linq. Is there a way to use the map configuration without needing to reference Linq in the model?

  14. Data.Linq which means it’s tightly coupled to Linq. Is there a way to use the map configuration without needing to reference Linq in the model?

  15. the map and partial entity class is created but the class still has references to Data.Linq which means it’s tightly coupled to Linq. Is there a way to use the map configuration without needing to reference Linq in the model?

  16. If your table changed you still have to re-generate the code of entities and compile your project? Is there work around not need to do that just replace the mapping file?

  17. have to re-generate the code of entities and compile your project? Is there work around not need to do that just replace the mapping file?

  18. If your table changed you still have to re-generate the code of entities and compile your project? Is there work around not need to do that just replace the mapping file?

  19. online games says:

    Great post:) Thanks for sharing this post and blog.

  20. gggrw It looks like DataContextExtensions.cs line 45 of the Save method should pass the primaryKeyName through to Update.

  21. HD Video Converter says:

    As the users of HD Camcorders like Sony, Canon, Panasonic, this HD Video Converter is necessary to help us convert hd Video easily and quickly. The Converter for HD provides several practical editing functions to help you achieve ideal output effect. Trim function is to cut videos into clips which you can just convert and transfer to your player. Crop function helps you remove black bars around the movie. You could use Effect function to adjust video brightness, contrast, saturation and more parameters. More powerful and considerate functions are waiting for you to explore.Mac Video Converter l Rip Blu Ray l VOB Converter ..