Linq to SQL – Mapping Tables to Objects

In this post I am going to cover how Linq to Sql deals with the mappings between the objects in your assembly and the database.

There are two ways in which Linq to SQL handles the mapping, either AttributeMappingSource or XmlMappingSource.  By default, Linq uses AttributeMappingSource where all the information which links tables and columns to classes and properties are stored as attributes within the DataContext.

For example, this code links the Categories object to dbo.Categories table.

[Table(Name=”dbo.Categories”)]
public partial class Categories : INotifyPropertyChanging, INotifyPropertyChanged

While this code links the CategoryID property to the column, also called CategoryID.

[Column(Storage=”_CategoryID”, AutoSync=AutoSync.OnInsert, DbType=”Int NOT NULL IDENTITY”, IsPrimaryKey=true, IsDbGenerated=true)]
public int CategoryID

There are a few advantages to having attribute saved within the code. Reading the code is easier as everything is in a single place and the compiler can verify that everything is correct.

The other choice is to use an external file which contains all the database information as XML.  This is then combined with a DataContext (which doesn’t have attributes) in order to be able to query the database.

The external mapping file can be generated by SQLMetal as I discussed in my previous post.  The following command would generate the file.

SqlMetal /server:. /database:northwind /map:”%~dp0Northwind.map”

The contents of the map file would look something like this.


 
   
   
   
   
   
 

We can then generate the DataContext code and tell it to use the external file by having both map and code as options, like below.

SqlMetal /server:. /database:northwind /map:”%~dp0Northwind.map” /code:”%~dp0Northwind.cs”

The datacontext would then just be the code.

public partial class Categories : INotifyPropertyChanging, INotifyPropertyChanged

and

public int CategoryID

Like with attributes, there are also advantages to be had when using an external file.  For one, you can make schema changes without having to recompile the application – but you could only make minor changes without breaking the relationship.  Another advantage is that the same mapping file could be used by multiple DataContext objects.   One possible disadvantage could be the two becoming out of sync, which would result in runtime errors.

However, to use this in our application we need to specify that we want to use the external mapping source, we could just modify the DataContext constructors to do this for us.

Northwind db = new Northwind(LinqConsole.Properties.Settings.Default.NorthwindConnectionString, XmlMappingSource.FromXml(“Northwind.map”));

So which one to use?  Well it really depends on your requirements, for the most part using either one will be fine.

Finally, the instead of SQLMetal connecting to the database directly to generate the mapping and datacontext, it can gain all the information from the dbml file.  The dbml file contains all the database metadata and everything SQLMetal requires to generate the code and mappings.

To generate the dbml you use the command.

SqlMetal /server:. /Database:Northwind /dbml:Northwind.dbml

Which creates an XML file containing data like this.  It looks similar to the mapping file, however is slightly different.


 
   
   
   
   
   
 

Then to use the dbml to generate the mapping file and code instead of a database connection you would use this command.

SqlMetal /map:”%~dp0Northwind.map” /code:”%~dp0Northwind.cs” Northwind.dbml

Technorati Tags: ,

One thought on “Linq to SQL – Mapping Tables to Objects”

  1. The Data Mapping engine in Data Transformation Server allows any-to-any transformations between different data formats. It includes complex data functions such as string, math, and conditional operations as well as DB and XML file look-up.

Leave a Reply

Your email address will not be published. Required fields are marked *