Corey Roth and Friends Blogs

Group site for developer blogs dealing with (usually) Ionic, .NET, SharePoint, Office 365, Mobile Development, and other Microsoft products, as well as some discussion of general programming related concepts.

Not Necessarily Dot Net

Linq to SQL Designer Weirdness

The Linq to SQL designer that's built into Visual Studio 2008 is pretty, and a huge improvement over what Microsoft's provided before, in terms of data modelling.  I haven't used enough other OR/M's to really have an opinion about how it measures up to other products.

But it does have its issues.  For one thing, it seems to magically cache its schema somewhere internal.  So you better have your database design pretty much set in stone before you start actually doing any data access.  (Yes, I know.  Have your product designed before you start writing code.  For some small projects, especially when the real point to them is to learn a new technology, design-as-you-go and incremental development have their place).

But that isn't the point.  If you change (by this I mean add/remove columns) your database schema after you do the drag-and-drop thing to have the designer generate the data access layer code for you, the only way I've found to get that data access code to match your changes is to update the generated code by hand.

This,  of course, is a Bad Thing (TM), Should Be Avoided At All Costs, and is a general pain in the ass.  But even deleting the table from the designer, rebooting, and re-adding it didn't work for me.  (I didn't go so far as to delete the DBML file, because I've spent several hours tweaking it...maybe that would work).

Actually, tweaking the dbml by hand isn't that big a deal.  Just tedious, because you have to change several things in several different places, and the compiler errors are useless if you forgot to dot a t or cross an i.  But the designer has an annoying tendency to want to overwrite your changes, so use caution.  (It's so much easier to not actually look at your changes with TFS...)

Sometimes, though, even that isn't enough.  I realized at one point yesterday that  a bigint field in the database really should be an int, to match up to where that field connects to everything else. So I changed it, then dutifully changed the matching dbml.

Then general distractions slipped in.  By the time I got done juggling and back to testing that change, I'd really forgotten about it.  And got a "Specified cast is not valid" SystemException (not an InvalidCastException as I'd expect) every time I tried to query that table.

I verified the data types of all the columns (especially the bigint vs. int). Did everything I could think of to get it to regenerate it with the correct column types.  Finally, out of desperation, I switched the column specifications in the dbml (OK, you can do this with the designer) back to long/bigint.

And it started working again. I think this is the first "just let VS 2008 magic, even though it's blatantly wrong" thing I've run across, but I really haven't had a chance to hit it hard yet.

If anyone has any work-arounds to add/remove columns (maybe there's something obvious hidden in a context menu that I'm just missing?) I'd love to hear about it.




CoreyRoth said:

You can always skip the designer and generate your domain objects yourself and just apply the LINQ to SQL attributes.

June 24, 2008 1:29 PM

Leave a Comment

Powered by Community Server (Non-Commercial Edition), by Telligent Systems