Friday, April 16, 2010

DBML (LINQ to SQL) Wonkiness

I like using DBML (LINQ to SQL) as an ORM system when I'm doing work in C#.NET. It's far from a perfect solution but it's easy to use and expandable (partial classes for the win).

Visual Studio 2008 has a graphical DBML file editor which allows you to drag tables from a SQL Server database onto its canvas. Probably the biggest pain working with this setup comes when making DB changes and you need to reflect those changes in this graphical editor. The best solution I've come up with in doing this is to basically delete all the tables you've updated, then drag them back onto the canvas. Unfortunately this requires deleting stored procedures that return an object of that table type.. So you have to re-add those as well, which makes this non-scalable solution. (If you have a better update solution, please post it!)

This is where the wonkiness comes in. I've had the following happen a few times: I have the DB Server connected inside Visual Studio and at the same time I'm making updates to it within SQL Server Management Studio. Changes are made to the database within Management Studio and I'll need to go through the above process in Visual Studio. Then something goofy happens. I'll be unable to add a stored procedure, receiving an error that indicates that the SP's return object does not match the Table shown in the graphical editor. Or Foreign Keys won't appear in the graphical editor or its produced code. The problem persists until Visual Studio is restarted. After a restart everything will work as expected.

This wonkiness has me caused some hair pulling over the last year. Hopefully this tip - restart Visual Studio if the graphical DMBL editor isn't behaving as expected - will save you some time.

2 comments:

  1. Does the LINQ to SQL ORM support many-to-many relationships out of the box now? At the time it seemed weird to me to release that product without that support

    ReplyDelete
  2. I don't think so. They've chosen configuration over convention in this case, this article describes the problem and shows how to implement n:n relationships (1 line of code in a partial class).

    ReplyDelete