There are a number of very significant drawbacks in the O/R Designer that have no reasonable work around.

• The Visual Studio 2008 LinqToSql O/R Designer is fine for a database with ~5 tables, no views, no stored procedures, and no functions, but it doesn't scale well much beyond that.
  • If the database changes, it's very difficult to manually update the O/R Designer properly.  As database complexity increases, this becomes near impossible.
  • When you need to remove an existing object from the O/R Designer to force it to update, it can be difficult to find that object.
• It is overly difficult to diff the generated files from one version to another since the files are rewritten in a different order than they were read in when changes are made.  Even if the files can be sorted in a reasonable way, diffing the files is still not an effective solution for managing changes.
• There are bugs with how the O/R Designer generates code.  In general, it is difficult to evaluate if the O/R Designer is working properly since it is difficult to diff the generated files before and after generation.  There are cases where the O/R Designer output is different than the SQL Metal output and in some of those cases, it could be a bug in either or both of the tools.
• If two or more developers are working under an edit/merge/commit style of source control, it is difficult to resolve conflicts in the LinqToSql O/R Designer files during the merge phase.
• It is not effective to try to diff O/R Designer generated files against files generated by SQL Metal as they don't use the same underlying code generation techniques and file sorting.  The two tools also have meaningfully different feature sets which negatively impacts the ability to diff the underlying files.

Once you start down the path of the O/R Designer, it gets increasingly harder to migrate away from it the farther you go.  This is a significant hidden risk as many of the scalability issues with the tool don’t present themselves right away.

It's not clear if any of these problems will be addressed in Visual Studio 2010.

This is not to say that the O/R Designer doesn't have very interesting and useful features.  It does have interesting and useful features.  They just aren't packaged in a way to make them at all usable for anything but very tiny databases.

Are there alternative LinqToSql code generation tools?

The main alternative tool is SQL Metal, which has quite a few drawbacks of its own.  While SQL Metal can scale up to much larger databases than the O/R Designer, it still can't scale effectively past a certain point.

Another free alternative is Damien Guard's LinqToSQL T4 Template.  Since the T4 template is not an official product of Microsoft and was built by a developer in their spare time, you'll have to judge for yourself whether it meets your criteria for "production worthy".

I may write a future blog entry with additional details on SQL Metal and the T4 templates.

There are non-free alternatives to the O/R Designer as well, but in many cases it is just not possible to get software tool purchases approved as part of the software development project budget.  It would be surprising if LinqToSql adoption hinged on the success of commercial third party tools.

Updated 2009/09/16 - Related blog posts:

The drawbacks of adopting Linq To Sql
.NET and ORM - Decisions, decisions