Using External Mapping File With Linq To SQL DataContext
By default when you use visual studio to drag tables onto Linq to SQL designer, you get table per entities generated in a single file. Those entities have attributes defined on them that defines how an entity is mapped to a table in the database. Although each of the generated entities are marked as partial, you never have to look at them because if you want to write code in those classes, you have the option to create your own partial class and put your logic in there. This makes your partial classes clean and devoid of any generated attributes which clutters up the code, making it hard to read. However using sqlmetal command line utility you have the option to move those attributes to an external mapping file and read the external mapping file at runtime as one of the parameters to the DataContext's constructor. Below are some of the examples of different usages of sqlmetal that provides various flexibility in code generation and gives control in how the code is generated.
In the above example, I am specifying to use local server by using dot, /database is used to specify which database to do code generation on. The last option /code indicates the location of where to generate the class files which contains all the code need to read and write an entity to the database. By default, the code generated classes will contain attributes that defines how an entity is mapped to a table. As we discussed earlier, if you do not want to clutter up your generated code with attributes, you can use /map option as one of the parameters to sqlmetal to generate the mapping in a separate XML file. Below is an example that shows how to generate separate mapping file.
Using the /map option to generate the code, removes all the attributes from your code generated classes and puts the mapping in a separate file. Below shows a screen shot of category mapping generated by sqlmetal.
Not only do you get control over of how code is generated, sqlmetal offers few other options that helps you control name, serialization and namespace. If you do not use /namespace option, the entities are not put in any namespace. You can use /namespace option to specify the namespace where all your Linq to SQL entities would reside.
Sqlmetal /server:. /database:NorthWind /code:C:\Code\NorthWind.cs
/map:C:\Code\mapping.xml /namespace:NorthWindEntities
Most of the times, your tables in the database are defined as plural like Customers but when you are using entities in your application, you want to access them as singular because you are dealing with a single entity. But when you are dealing with collection of entities such as when you do DataContext.Customers, you want those names to appear as plural. The default code generation in sqlmetal does not take care of this issue. You can use /pluralize option to make sure your entities are created with singular name which is more easier to read and write code against it.
Sqlmetal /server:. /database:NorthWind /code:C:\Code\NorthWind.cs
/map:C:\Code\mapping.xm /namespace:NorthWindEntities
/pluralize
By default, Linq to SQL entities classes generated cannot be serialized over the wire using WCF. If you are going to be exposing entities using a service, you can use /serialization option and set it to unidirectional. This would attribute your generated classes with DataMember and DataContract attributes.
Sqlmetal /server:. /database:NorthWind /code:C:\Code\NorthWind.cs
/map:C:\Code\mapping.xm /namespace:NorthWindEntities
/pluralize
/serialization:unidirectional
After you have generated your mapping in a separate xml file, the question comes how do you use that mapping file when you create your DataContext. Well, one of the constructors for the DataContext, allows you to specify the connection to the database and XmlMappingSource which contains the mapping for entities to table. In order to use my xml mapping file, I have marked my xml file as an embedded resource. Below screen shot shows where I setting my xml file to be stored as an embedded resource.
Notice in the above screen shot, I have set my build action on the mapping file as an embedded resource. What this allows me to do is, at runtime, I can load my xml file from the assembly simply by specify the name of the resource. Below example shows how I am reading the xml file from the assembly and assigning it to XmlMappingSource.
In the above code, I am calling Assembly.GetExecutionAssembly to get a reference to the currently assembly my code is running under. After obtaining the assembly I am reading a specific resource by its resource name. Since we have stored our xml file as a resource, we can read it by its name. Notice that instead of using just the filename, the complete resource name for my file is namespace.filename. This is mandatory, otherwise GetMainfestResourceStream wont be able to locate the resource. After obtaining the resource I am simply passing the stream to XmlMappingSource by calling FromStream method. After initializing my stream and setting my connection, I create my DataContext, passing the mappingsource and database connection to obtain my DataContext.