.NET 3.5’s LINQ to SQL functionality provides a great way to write data access layer code that automatically handles mapping relational data to object properties.  Although I generally prefer to use stored procedures when performing insert, update or delete operations against a database (see my previous post on this), I still use LINQ to SQL in projects since it eliminates the time I used to spend creating SqlParameter objects or writing AddWithValue() parameter statements.  Overall, LINQ to SQL has made me much more productive as a developer.

Most of the samples involving LINQ to SQL involve using the designer built-into Visual Studio 2008 since it’s very productive.  The LINQ to SQL designer is a great way to go and normally what I use when doing my ORM mapping.  I recently had someone ask if using the LINQ to SQL designer was required in order to leverage LINQ to SQL in their applications.  They had existing data entity classes that they wanted to use and didn’t want to re-create them using the designer.  In situations like this you can use built-in XML mapping features available in LINQ to SQL to get around using the designer if desired.  Going this route leads to writing more custom code and XML mapping files but also provides the ultimate in control especially if your data entity classes are created by another tool, you don’t want your classes littered with LINQ to SQL attributes, or you have some other reason for not wanting to use the designer.  In this article I’ll provide a step by step introduction to working with LINQ to SQL XML mapping files.

Step 1. Create the Data Entity Class

If you’re not using the LINQ to SQL designer then you’ll need to create your own data entity classes (or use a 3rd party tool to create them) that can hold data from the target database.  If you already have existing data entity classes that you want to use then you can skip this step.  Here’s a simple Customer class capable of holding some of the data found in the Customer table in the AdventureWorksLT database.  A diagram of the Customer table follows.

namespace Model
{
    public class Customer
    {
        public int CustomerID { get; set; }
        public string Title { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime ModifiedDate { get; set; }
    }
}

2. Create the XML Mapping File

Visual Studio 2008 provides a LINQ to SQL intellisense schema located at Program Files\Microsoft Visual Studio 9.0\Xml\Schemas\LinqToSqlMapping.xsd.  The schema contains a namespace of http://schemas.microsoft.com/linqtosql/mapping/2007 in it that can be used in your custom LINQ to SQL XML mapping files to get intellisense as you create your XML mappings.  Start by creating an XML file in your Visual Studio 2008 project.  Once the file is created add a Database element that defines the http://schemas.microsoft.com/linqtosql/mapping/2007  namespace on it (see the XML code below for an example).  Doing this will give you intellisense for the XML mapping file as you type in additional tags.  Within the Database element add one or more Table elements.  Each Table element will contain child Column elements that define how the individual table columns map to class properties.  In addition to defining table mappings, stored procedures can also be defined using a Function tag. 

Here’s an example of mapping the Customer class shown earlier to the Customer table in AdventureWorksLT.  Notice that the intellisense xsd schema namespace mentioned earlier is defined on the root element in the mapping file. While the namespace isn’t required, having intellisense definitely speeds up the process of creating the mapping file.

<?xml version="1.0" encoding="utf-8" ?>
<Database Name="AdventureWorksLT" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="SalesLT.Customer" Member="Model.Customer">
    <Type Name="Model.Customer">
      <Column Name="CustomerID" Member="CustomerID" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Title" Member="Title" />
      <Column Name="FirstName" Member="FirstName" />
      <Column Name="LastName" Member="LastName" />
      <Column Name="ModifiedDate" Member="ModifiedDate" />
    </Type>
  </Table>
  <Function Name="dbo.ap_GetCustomerByLastName" Method="GetCustomerByLastName">
    <Parameter Name="lastNameLetter" Parameter="LastNameLetter" />
    <ElementType Name="Model.Customer" />
  </Function>
</Database>

Looking through the file you’ll see that the Model.Customer class is mapped to the SalesLT.Customer table and that each field is mapped as appropriate.  The primary key in the table is defined using the IsPrimaryKey attribute along with the IsDbGenerated attribute.  The XML mapping file also includes a reference to a stored procedure named ap_GetCustomerByLastName which looks like the following:

CREATE PROCEDURE dbo.ap_GetCustomerByLastName
    (
        @LastNameLetter char(1)
    )
AS
    BEGIN
        SELECT CustomerID, Title, FirstName, LastName, ModifiedDate 
        FROM SalesLT.Customer
        WHERE LastName LIKE @LastNameLetter + '%'
    END

The mapping file maps the stored procedure to a custom method named GetCustomerByLastName which accepts a single parameter named lastNameLetter.  Data returned from the stored procedure call is automatically mapped to the Model.Customer class using the mapping file’s ElementType element. 

Note:  The SqlMetal.exe command-line tool can be used to automatically generate the XML mappings for a database quickly and easily (it can generate code as well if desired).  Although it won’t handle mapping the database fields to your custom data entity classes, it can be used to generate initial XML mapping code that can be modified quickly.  To generate the mapping file you can run the following command using the Visual Studio 2008 command prompt.  This command generates an XML mapping file for the entire AdventureWorksLT database and includes functions and stored procedures in the output.

sqlmetal.exe  /server:YourDBServer  /database:AdventureWorksLT  /dbml:AdventureWorksLT.dbml  /functions  /sprocs  /namespace:Data

3. Create a Custom DataContext Class

If you’ll be querying the database directly using LINQ or Lambda expressions you can use the built-in System.Data.Linq.DataContext class directly to run your queries and skip this step.  However, if you also need to query a stored procedure defined in a mapping file then you’ll want to create a custom class that derives from the DataContext class.  This is necessary since you’ll need to access the protected ExecuteMethodCall() method found in DataContext which can be used to call stored procedures.  Here’s the signature for the ExecuteMethodCall() method:

//
// Summary:
//     Executes the stored database procedure or scalar function associated with
//     the specified CLR method.
//
// Parameters:
//   instance:
//  The instance of the method invocation (the current object).
//
//   methodInfo:
//     Identifies the CLR method that corresponds to a database method.
//
//   parameters:
//     The array of parameters to be passed to the command.
//
// Returns:
//     The result (the return value and output parameters) of executing the specified
//     method.
protected internal IExecuteResult ExecuteMethodCall(object instance, MethodInfo methodInfo, params object[] parameters);

Creating a custom DataContext class is a bit of extra work, but I normally create a custom class that inherits from DataContext or my LINQ to SQL designer class if I’m using the Visual Studio 2008 designer anyway so that I can control things like logging and connection strings in a more centralized place.  Here’s a simple example of a custom class named CustomDataContext that derives from DataContext and exposes a GetCustomerByLastName() method that is used to call the ap_GetCustomerByLastName stored procedure shown previously.  Notice that the method calls the DataContext class’s ExecuteMethodCall() method and passes in the name of the method to call (contained within the System.Reflection.MethodInfo object) as well as the parameter data to pass to the stored procedure.

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace ConsoleApplication1
{
    public class CustomDataContext : DataContext
    {

        public CustomDataContext(string connStr, XmlMappingSource xmlMap) : base(connStr, xmlMap) { }

        public IEnumerable<DAL.Customer> GetCustomerByLastName(string lastNameLetter)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), lastNameLetter);
            return result.ReturnValue as IEnumerable<DAL.Customer>;
        }
    }
}

Step 4. Load the XML Mapping File into the DataContext Object

Once the data entity class, XML mapping file and custom DataContext class have been created you can use the DataContext class to query the database and automatically map the appropriate database fields to their corresponding Model.Customer properties.  When using the LINQ to SQL designer you can simply create a new instance of the DataContext class and be on your way since the designer generates code that includes C# or VB.NET mapping attributes.  When using XML mapping files you’ll need to load the mapping file into the DataContext object so that it knows the proper mappings to use.  Here’s an example of creating a CustomDataContext object instance and loading the XML mapping file.  Notice that the mapping file is loaded by using System.Data.Linq.Mapping.XmlMappingSource class’s FromUrl() method.  XML mapping files can also be loaded from a string, stream or XmlReader.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Text;

namespace LINQAndXMLMapping
{
    class Program
    {
        static void Main(string[] args)
        {  
            string connStr = "server=.;database=AdventureWorksLT;integrated security=true;";
            using (CustomDataContext context = new CustomDataContext(connStr, XmlMappingSource.FromUrl("CustomerMapping.xml")))
            {
                //Perform query


            }
            Console.Read();
        }
    }
}

Step 5. Query the Database Using the DataContext Object

Once the XML mapping file is loaded into the DataContext object you can query the database using LINQ or Lambda expressions.  Here’s an example of grabbing all of the customer records that have a last name starting with “M”:

IEnumerable<DAL.Customer> custs = from c in context.GetTable<DAL.Customer>()
                                  where c.LastName.StartsWith("M")
                                  select c;
foreach (DAL.Customer cust in custs)
{
     Console.WriteLine(cust.FirstName + " " + cust.LastName);
}

The ap_GetCustomerByLastName stored procedure can be called using the CustomDataContext object’s GetCustomerByLastName() method as shown next:

IEnumerable<DAL.Customer> custs2 = context.GetCustomerByLastName("A");
foreach (DAL.Customer cust in custs2)
{
    Console.WriteLine(cust.FirstName + " " + cust.LastName);
}

The results of the two queries are shown next:

Although the LINQ to SQL designer provides the biggest bang for the buck as far as productivity goes when working with LINQ to SQL, the XML mapping features discussed here (although not as productive from a time standpoint) allow you to have more control over the classes that are used in an application and the manner in which they’re mapped to database tables.  Regardless of which route you choose to go, LINQ to SQL will definitely provide a productivity boost for many applications especially when compared to writing data access code from scratch.

Downloads

• Download the sample code here. 
• Download the AdventureWorksLT database used in the application here (look for AdventureWorksLT.msi)