How to Link VS2010 Database Project and LINQ to SQL

Question

As I am working with the new database projects in VS2010, and as I am learning LINQ to SQL, I am curious as to the best way to link the two groups of information so that when I update one, the other updates along with it.

From my research here at SO, as well as in Google, it appears the general rule of thumb is: "Build the database, and then create your LINQ to SQL classes." Of course, if I make a change in my database, the LINQ to SQL doesn't update automatically and I have to do it by hand. This is fairly simple right now as my database is small, but I am curious if there is an easier way for this to happen.

In addition, the LINQ to SQL tool is pretty nice. The ability to create tables, add associations, and even create inheritance is very simple. As my second question, I am curious as to whether or not VS2010 can work the other way - I design the database in the DBLM file and then link it back to my database project.

I appreciate any help with either of these two questions. I'm really interested in making this as easy as possible to reduce errors during development and improve the speed at which changes can be made.

Answer 1

Jason - We have built a mission critical L2S based application platform for our solar business. I was primarily responsible for building the back-end data access layer. Early on I realized that maintaining a .DBML was going to be a royal pain in the backside. Every time you add/update/delete objects in the schema, the DBML must be updated. And this isn't always easy to do. My solution was to build our own code generator that generates our L2S classes, application level classes (a lightweight version of the L2S classes), and all the logic/DAL code. This way, each time we make a schema change, we just run the code generator and everything is created for us.

I very much like L2S, but without our custom code generator, I think I would have pulled all my hair out by now. Besides, it seems that on occasion, the generated classes from the .DBML are just plain wrong.