(reposted because something ate this the first time)

Each major release of the .NET framework and Visual Studio has at least one killer feature that makes developers stuck in prior versions pine longingly for it. With .NET 3.5 and Visual Studio 2008, that big one is LINQ. The reason being is that it provides built in OR/M functionality with SQL Server. To put it succinctly: it can take database tables and create .NET classes and the code to bind them to each other.

It's pretty hot stuff, or it is until you look at your business objects and realize that they are directly tied to your data layer. This is known as "Tight Coupling" and is a Bad Thing^tm. It is also not very testable - to test anything having to do with your objects will result in the database being modified.

So does this mean we have to chalk up LINQ to SQL as another neato feature that is pretty but only suitable for marketing demos? Not necessarily; there are ways that you can detach the coupling, but it's not the easiest thing in the world.

In order to fully understand this, I'll make my way through this iteratively. The first example will only be a very simple table to class match up, then I'll move on to foreign key associations, updating data, stored procedures, and more.

As stated, the first database design is simple, just two tables: Person and TPS_Report. Essentially, a person can have 0 to n reports that they have written (though this part of  the functionality won't be implemented just yet).

Now that we have our database defined, we need to create the classes that represent this data. For this you have a couple of options. You can create your classes yourself, or use the sqlmetal.exe utility generate them and then modify them to your purposes. Let's assume we're shoehorning LINQ into an existing project and create the classes ourselves to fully understand what's going on.

The breakdown of the class libraries are as follows:

• LinqExample.Core - Where our class definitions and business logic go.
• LinqExample.Data - The location of our data access code.

Using some of the other new features in .NET 3.5, you might make your Person class look like this:

public class Person {
  public int Id { get set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

However, the mapping files that LINQ generates does not like Automatic Properties. So you'll need to have to do it like you would in the 2.0 era (see the attached zip file for the full code including the TpsReport class):

public class Person {
  private int _id;
  private string _fname;
  private string _lname;

  public string FirstName {
    get { return this._fname; }
    set { this._fname = value; }
  }

  public string LastName {
    get { return this._lname; }
    set { this._lname = value; }
  }

  public int Id {
    get { return this._id; }
    set { this._id = value; }
  }

  public Person() {
  }
}

Ok. Now that we have our classes defined, let's map the them to the database. This is where sqlmetal.exe comes into play. (You could create the XML mapping file yourself, but that is tedious.) From a Visual Studio 2008 command prompt run the following command (substituting your own local values where appropriate):

sqlmetal.exe /server:localhost\sqlexpress /database:LinqTest /namespace:LinqExample.Core /code:c:\somepath\code.cs /map:c:\somepath\map.xml

This will generate both an XML mapping file and a code file that defines the classes we already created. Feel free to poke through it, but we won't be using it in this example. Now, let's examine the map.xml file:

<?xml version="1.0" encoding="utf-8"?>
<Database Name="LinqTest" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="dbo.Person" Member="Person">
    <Type Name="LinqExample.Core.Person">
      <Column Name="id" Member="Id" Storage="_Id" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Column Name="fname" Member="Fname" Storage="_Fname" DbType="VarChar(50)" />
      <Column Name="lname" Member="Lname" Storage="_Lname" DbType="VarChar(50)" />
    </Type>
  </Table>
  <Table Name="dbo.TPS_Report" Member="TPS_Report">
    <Type Name="LinqExample.Core.TpsReport">
      <Column Name="id" Member="Id" Storage="_Id" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Column Name="reporter_id" Member="Reporter_id" Storage="_Reporter_id" DbType="Int NOT NULL" />
      <Column Name="report_name" Member="Report_name" Storage="_Report_name" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
      <Column Name="report_body" Member="Report_body" Storage="_Report_body" DbType="VarChar(2000)" />
    </Type>
  </Table>
</Database>

As you can see, each table has a Type node. This is the name of the class. The first thing to change is that the report table thinks the class it maps to is called TPS_Report. Change the Type's name attribute's value to "LinqExample.Core.TpsReport".

Next, let's look at the column nodes. These map to the properties of the class. Here's a brief rundown of the important attributes:

• Name - the name of the column in the database. These should not need to be changed.
• Member - the name of the public facing property that this column map to.
• Storage - the name of the private member that the Member uses. This cannot have the same name as Member, thus Automatic Properties cannot be used.

So, you'll need to update the various attributes to match what you defined in your class. All the values are case sensitive as well:

<?xml version="1.0" encoding="utf-8"?>
<Database Name="LinqTest" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="dbo.Person" Member="Person">
    <Type Name="LinqExample.Core.Person">
      <Column Name="id" Member="Id" Storage="_id" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Column Name="fname" Member="FirstName" Storage="_fname" DbType="VarChar(50)" />
      <Column Name="lname" Member="LastName" Storage="_lname" DbType="VarChar(50)" />
    </Type>
  </Table>
  <Table Name="dbo.TPS_Report" Member="TPS_Report">
    <Type Name="LinqExample.Core.TpsReport">
      <Column Name="id" Member="Id" Storage="_id" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
      <Column Name="reporter_id" Member="ReporterId" Storage="_reporterId" DbType="Int NOT NULL" />
      <Column Name="report_name" Member="Name" Storage="_name" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
      <Column Name="report_body" Member="Body" Storage="_body" DbType="VarChar(2000)" />
    </Type>
  </Table>
</Database>

Ok, now before we create the code to use the mapping file, let's think about our main purpose here: to keep the database interaction separate from the business objects. So, let's abstract our loading code out into an interface to allow for different data layers:

public interface ILoader {
  List<Person> LoadAllPeople();
  Person LoadSinglePerson(int personId);
  List<TpsReport> LoadAllReports();
}

Put this interface in the Core assembly with your Person and TpsReport classes. Note that it doesn't actually tie to one particular data technology; we could create an implementation that uses ADO, or XML, or something else entirely. But since this post is about LINQ, let's make a LINQ implementation. If you haven't already, add a reference to the System.Data.Linq assembly in your data layer:

using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using LinqExample.Core;

namespace LinqExample.Data {
  public class LinqLoader : LinqExample.Core.ILoader {
    DataContext context;

    public LinqLoader() {
      XmlMappingSource source = XmlMappingSource.FromUrl(@"C:\somepath\map.xml");
      this.context = new DataContext(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=LinqTest;Integrated Security=True", source);
    }
    
    public List<Person> LoadAllPeople() {
      return this.context.GetTable<Person>().ToList();
    }

    public Person LoadSinglePerson(int personId) {
      var p = from persons in context.GetTable<Person>()
                where persons.Id == personId
                select persons;
      return p.Single();
    }

    public List<TpsReport> LoadAllReports() {
      return this.context.GetTable<TpsReport>().ToList();
    }
  }
}

For the sake of demonstration, the XML path and connection string are hard-coded. We'll update that to something better in future iterations.

One drawback to this process is the ToList() call in the LoadAll methods. By default LINQ iterates through a query result row by row as it's needed (similar ADO.NET's DataReader classes), but when ToList() is called, it pulls the entire table down to put it in our generic list. For small and medium sized tables this isn't a big deal, but for millions of rows, you'll want to do something else.

Phew, now let's actually run the thing. Since this post is already running long, I only created a stupid little Console app that prints out the list of people in the Person table. Fancy, I know, but you have to start somewhere.

static void Main(string[] args) {
  ILoader loader = new DataLoader();
  foreach (Person p in loader.LoadAllPeople()) {
    System.Console.WriteLine("{0} {1} is a person here.", p.FirstName, p.LastName);
  }
  System.Console.ReadLine();
}

Results in:

That's pretty happy. If an InvalidOperationException was raised at runtime when the DataContext was being created, something is probably not lining up correctly between your code and the mapping file, so double check to make sure the members and properties are named correctly.

That's about it for now. Things still are not quite completely decoupled, but it's a good step forward, and we'll improve the design in the next go around.

Source code for this post is located here.