SyndicationLINQ to SQL and Database Schema Sync
Since the new year I’ve been working on a project that attempts to solve the following problem:
How do I keep my generated entity classes in sync with the current database schema using LINQ to SQL?
For some people, this question is moot. It’s easy enough to utilize the current “resync” mechanism in LINQ to SQL by deleting all the entity classes and functions (the LINQ to SQL term for sprocs and UDFs) on the designer surface and then redragging and dropping the tables and functions of interest, thus updating the entity classes by regenerating from scratch. However, for others, this solution is not good enough for a few reasons:
- In the organizations I’ve been in, column names are camel case, and .NET property names are Pascal case. When creating the entity classes, I have to fix the casing of all the properties since their names default to the names of the columns. Using the solution above, I would have to repeat this process every time the database schema changed!
- If developers prefix their table and column names with “tbl_”, “int_”, “str_”, “usp_”, etc. but have different standards in .NET code, then they have to change the generated entity class and property names every time the database schema changes.
- There are quite a few properties that a developer can set in the .dbml (LINQ to SQL) file that cannot be set in the designer. Every time one of those properties is set, it exposes a risk in that they have to be reset every time the database object on which that property was set changes.
- Not even considering name changes (which are understandably trivial to some people), what about inheritance relationships? The designer can’t infer when a table maps to a class hierarchy (table-per-hierarchy, or TPH). Every inheritance relationship has to be remapped when the database is changed.
And the list goes on…
As you can see, all of these customizations can become quite expensive over time if a database changes often. Perhaps some developers have taken it upon themselves to write their own scripts to keep their customizations in the .dbml file for a single database, which is great, but not scalable. I believe something that the community could benefit greatly from is a tool that tracks customizations to the file and allows them to restored after the designer re-generates the entity classes and functions. Perhaps something that could manage the entire resync process for you would be even better.
At this point I am fishing for requirements, more specifically common tweaks that developers want to make to the LINQ to SQL files. The common tweaks I have identified so far are (1) to transform property names to be Pascal case, (2) to remove prefixes from the names of database objects, and (3) to automate mapping the types of columns to various (developer-defined) enumerations.
Any input you guys can give would be appreciated! I’ll be posting more on this tool in the future. Currently its name is “DBML Fixup,” but if you have any better alternatives feel free to share.
February 16th, 2008 at 4:09 pm
[...] About Me « LINQ to SQL and Database Schema Sync [...]
February 17th, 2008 at 2:00 pm
“Perhaps something that could manage the entire resync process for you would be even better.”
I think this is feature is going to grow in popularity VERY quickly. Having some way to automate the synchronization process would be fantastic for two reasons that I can see. The first being a combination of all the points you mentioned. The second being code generation and other automation strategies.
As you said, some developers have more than likely written custom-tailored scripts to manage their environments but they are not scalable. It feels like the ideal solution here one that could automate the synchronization process (which could in turn create it from scratch given the right input parameters).
I’ve been looking into the possibility of doing something similar to this for the iteration of the code generation application. I would like to target a database and automate the generation of a corresponding LINQ model. If automation is where you’re looking, then I’m certainly interested in touching base with you whenever possible.
February 19th, 2008 at 5:12 am
Hi,
Glad I found this link. Other features like Delay Load and Update Check will be helpful too.
If you are too busy let me know, I will proceed for this development.
Regards,
Parag.
February 20th, 2008 at 8:13 pm
Hi Parag,
I will keep those in mind. I am actively working on this project now and have no intentions of stopping any time soon.
I am not sure what you mean by “I will proceed for this development.” Would you care to explain?
Regards,
David
March 14th, 2008 at 7:14 am
[...] I get further into my DBML Fixup project, I’m learning more and more about Visual Studio Extensibility (VSX). Along the way, I [...]
March 22nd, 2008 at 9:37 pm
[...] reliant on built-in UI contexts, such as UICONTEXT_NoSolution. However, what I needed for my DBML Fixup project was a way to add menu commands when right clicking on .dbml files (LINQ to SQL Classes) in [...]
March 24th, 2008 at 6:30 pm
Could much of this be performed via the mygenerationsoftware.com code gen framework? via some fancy templates?
yes i need this right now as well. my requirements are a bit different though - i have a lot of associations created in the dbml file which dont exist in the actual db. So if i delete and reimport them, the assocs are lost and have to be hand created. do this with 50 assocs and you get the pic
March 24th, 2008 at 8:40 pm
Hi David,
Have you heard about a new database synchronization tool Database Restyle (http://www.perpetuumsoft.com/Product.aspx?lang=en&pid=55) that supports LINQ To SQL object model and allows automatic synchronization of LINQ To SQL model with your live database? Probably, this tool will be helpful.
March 25th, 2008 at 7:29 am
Hi boomhauer,
It is possible that code generation techniques can be used (like Mygenerationsoftware or CodeSmith) for generating code that the LINQ to SQL provider can use to query a database, but that is not really what I’m going for. The developer customizations I’m trying to preserve are at the .dbml level (mainly by “enhancements” to the designer). Consequently, the code generation layer will stay the same, but the mapping layer will be enhanced.
Thanks also for mentioning the scenario you have to undergo every time you want to make updates. It’s something I’ll note down.
David
March 25th, 2008 at 7:31 am
Hi Kate,
I had not heard of the Database Restyle tool, but I did look into briefly this morning. My understanding is that it helps you propagate changes from your LINQ to SQL model to your database, but not the other way around. Is this correct?
I think it would be a useful tool, but it is not quite the same path I’m going down.
David
March 28th, 2008 at 12:12 am
Hi David,
You are quite right, Database Restyle only propogates changes from LINQ To SQL Model to the database.
Just in case, if you ever think about doing this, you will aleady know that such functionality is implemented.
Kate.
June 18th, 2008 at 12:17 pm
Hi David,
Sounds like you are really getting deep! But that is only expected from a Neumont graduate :). http://www.llblgen.com/defaultgeneric.aspx is an OR mapper that supports LINQ to SQL and also handles synchronization from the DB to the DBML file by completely regenerating the DBML file for you. Thing is it has a way of retaining your custom changes to the DBML file I think through the use of specifically named #Region tags. It pulls them out generates and then reinserts them, well this is what I have been told, I haven’t tried it out myself of looked into it all that deep. Though you might want to take a look at it and see how they do it and then implement that into your extension of the VS designer. Let me know what you find out…
July 7th, 2008 at 8:05 pm
I have written an add-in for synchronizing simple changes (added/removed fields, removed tables) from the db to the dbml. I also have an add-in for enforcing naming conventions, however it only supports lowercase_underscore_separated_names to UppercasePascalNames. Anyway, it is related to what you are describing so you may want to take a look at it at blog.huagati.com
July 8th, 2008 at 5:54 am
Tim, good to hear from you again. I’ll have a look at LLBLGen Pro and see what I can find.
Kris, thanks for the heads up as well as the add-in. I was wondering how to get around an issue in my own work, but your code provided some good leads.
July 30th, 2008 at 10:48 pm
Hi David,
I added a pile of new functionality to the add-in so it now supports new/dropped tables, new/dropped/modified columns, new/dropped FKs and will update the dbml accordingly. Stored procs are not part of the synchronization yet but I’m planning it in a future update.
I moved the download out of my blog onto a separate page: http://www.huagati.com/dbmltools/ and split it into three editions; free, standard and pro. The standard and pro editions has a small license fee attached to them to pay for continued development but the fee should soon pay itself back for the time saved on not having to make apply all db changes to the dbml by hand…
August 24th, 2008 at 8:41 pm
[...] in February I blogged about DBML Fixup—a tool whose goal was to sync LINQ to SQL models with their [...]