DB design strategy in Visual Studio

Question

Hi All,

I'm currently investigating ASP.NET MVC 2 and LINQ to SQL. It all looks pretty cool. But I have a few application and development lifecycle issues.

Currently, I design the DB in SqlServer Management Studio. Then I update my DBML files by deleting and re-importing modified tables.

Issues:

1. I can't find how to simply update the whole DBML schema.
2. My DBML then loses some of the changes I made such as renaming relation members or mapping of some int to an enum.
3. If I want a SQL script to deploy my DB (or to keep the schema under source control), I need to go use the 'Genererate Script' SSMS wizard which would be cool if a) it could remember my settings and b) it could be automated.

Should I work the other way around (start from my DBML and generate the DB)? Should I go for some other framework (NHibernate? Can I use some Linq flavor with it?)

Also, I read that LINQ2SQL is already obsolete in favor of Linq to Entities. Does it mean that the ultimate tool supposed to make my life so much better will again make me lose time in the long term?

Thanks for shedding some light.

Answer 1

If you are starting your DB Schema from scratch you could consider "Code-First Development with Entity Framework 4" as outlined by Scottgu.

I have been using this on a new project and am finding it extremely beneficial - especially for testing.

I started with simple POCO classes representing my data, then as the project progressed I would allow EF4 to generate the schema to a "real" DB using my "in-memory" example data ... now I am using a mixture of both in memory POCO (for development and TDD) and auto-generated DB Schema (auto-loaded with more "realistic" data) for demonstrations etc ... so far I am very happy.

Answer 2

There is a lot of opinion over LINQ2SQL and whether it's 'obsolete' or 'discontinued'. But it is still in the .NET framework and a good tool, so if it suit your needs then you should use it. Frankly the Entity Framework is still not perfect and if you don't need the extra flexibility that it affords then it is not worth the pain. If I had a small to midsize project then I would definitely use LINQ2SQL again (and over EF).

As for your question, yes you'll lose any names or different type mappings when you remove and re-add a table. The options that I'm aware of are

1. Only remove / re-add the table that has changed (not all tables)
2. Try altering the DBML tables in place, rather than remove / re-add. You can add and remove columns, change column names and data types, add relationships all on the DBML.

I like JcMalta's suggestion of creating objects as classes before rendering into the database, but if you find SQL Studio to be quick to develop with then it might simply be quickest to create tables there are drop them into your DBML. It's a touch annoying to have to change something in a database and the push the changes into your code but the code-gen tools are quite good and take away most of the pain.

Answer 3

You can try CodeSmith/PLINQO to auto-sync DB/code: http://plinqo.com/