Mapping options in LINQ to SQL

A recent thread on the ALT.NET message board asked:

How does everyone create the DataContext for their DB?

  1. Do you use the IDE and generate your custom .dbml (which also generates all your DTO’s)?
  2. Do you use a generic DataContext object and just .GetTable<>() and create your DTO’s by hand?

Some of the comments indicated there were other mapping options other than the designer mapping I had seen 50 million times before in demos.  I bought LINQ in Action this past weekend to get the scoop. It turns out there are four (count ’em, four) ways of mapping your LINQ to SQL entities DTO’s:

  • VS designer
  • Command-line SqlMetal tool
  • Hand-coded with attributes
  • Hand-coded with external XML files

Take a real close look at that last one.  Reaaal close.  Now three times fast:

LINQ to SQL supports persistence ignorance

LINQ to SQL supports persistence ignorance

LINQ to SQL supports persistence ignorance!

You read that right folks, LINQ to Entities does not support persistence ignorance, but LINQ to SQL does!  Perhaps one of these teams should talk to the other.

So is it the real deal?

I ordered the options from most to least intrusive on your entity objects.  Obviously, the designer does not support a very maintainable solution, as it will run into the same issues of the last fifty drag-and-drop database products.

SqlMetal can help create your initial entities, but this is from a DB-first perspective, which I don’t run into for greenfield projects.  I don’t create my database first, I create entities first, so this won’t help me unless I run into an existing database.  In that case, I probably still won’t use the tool, as existing databases can be their own form of crazy legacy code.

Hand-coding with attributes allows me to decorate my classes, but it tends to clutter up my entities.  I don’t want to look at my business objects and see a mess of persistence decorations.

Finally, the external XML files.  Now I’m pretty much where I am with NHibernate, except in a much more primitive version.  The mappings are nowhere close, not even in the same ballpark as NHibernate’s options.  LINQ to SQL is strictly table-per-class, and collection mappings aren’t that hot.  Additionally, I wind up having to repeat a lot of the same information.

Finally, I have to specify the location of the XML file whenever I create the DataContext object.  I have a few options, but basically LINQ to SQL has no mechanism that I see to “figure out” where the mapping might be.

For me, the final verdict is: Though LINQ to SQL gives me powerful strongly-typed querying abilities with LINQ, the only maintainable mapping option still pales in comparison to NHibernate.

Whether or not LINQ to SQL queries belong anywhere but inside your Repositories is another question…

Review – xUnit Test Patterns