Sql – Is SqlMetal a good solution for mapping to a database that contains *only* tables

linqlinq-to-sqlormsqlmetal

I'm thinking of using SqlMetal to auto-generate LinqToSql code for a simple and small database. The database will have only tables with some primary and foreign keys (i.e., no views, stored procedures, functions, etc.). I'd like to do all joins, grouping, sorting, and advanced data manipulation using Linq.

I have experience with LinqToObjects and LinqToXml, but I've never used a proper ORM or LinqToSql.

Some questions:

  1. How steep is the learning curve for SqlMetal/LinqToSql, given my prior experience?
  2. Is SqlMetal reliable for simple databases?
  3. What kind of surprises, if any, might I encounter?
  4. How would I automate my project so that the LinqToSql code gets regenerated every time I build or, better yet, rebuild my project? (I will be using Visual Studio 2008.)
  5. Can you recommend a good tutorial for getting me up to speed quickly on using SqlMetal and LinqToSql?

Best Answer

The answer to your title question is yes it is a fine solution for database with tables only (and to a point can grow when your database grows to include other database constructs like views and stored procedures).

  1. Pretty shallow. The main difference you'll find is that certain methods that work on Linq2Objects don't work for Linq2SQL. You have to be careful when mixing objects and Linq2SQL together. You'll know you hit this when you get an exception saying something to the effect of "The only thing we support is Contains".
  2. Quite.
    • You'll probably find that in certain situations Linq2SQL doesn't generate the most efficient SQL.
    • Sometimes things can get tricky with transactions.
    • There is no update statement. Every time you want to update a set of records you have to select them, update each one and then SubmitChanges().
    • Unless you're only doing selects you need to have a primary key on your table. Without a primary key you can't delete or update.
    • Linq2SQL doesn't directly support many-to-many relationships.
  4. A simple solution would be to setup a prebuild task that generates your Linq2SQL DataContexts every time you build.

As to your goal of using Linq2SQL for everything, be ready for some nasty LINQ queries (and nasty debugging cycles). The join syntax is verbose and depending on the complexity of your queries can quickly become harder to maintain than the SQL equivalent. Grouping isn't as easy as GROUP BY.

Related Topic