none
Simple property changes require database drop and recreate? RRS feed

  • Question

  • I'm coming back to researching EF after a long time and it sounds like even with the current version that simple changes like a new property are problematic on existing databases. It is not uncommon for us to find the need for a new column in our tables. From what I'm reading, it sounds like if we were to use the EF and add a new property to one of our classes, then it would attempt to drop and recreate our database. This leads to a few questions from those who have used EF in real production applications.

    1. Is this really required for adding a new column/property?
    2. What is the practical way to work around such behavior so that data is not lost?
    Tuesday, August 23, 2011 9:37 PM

Answers

  • Code First migrations V1 has already released and you can download it now through the link I posted. Not sure if you're aware of that. 

    Also, if you have an EDMX-based approach, the link I posted to the database generation power pack details how to generate migration T-SQL that doesn't touch your data. See the blog post here: http://blogs.msdn.com/b/adonet/archive/2010/02/08/entity-designer-database-generation-power-pack.aspx which basically goes through a similar scenario to the one you just described regarding the mobile phone number.

    Down the road, would something like this be useful to you? (http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DEV314). Skip to 6 minutes in. When you build up your model, it will automatically build up your database 'intent' through a database project. 23 minutes in, we perform a refactoring from the database project after we've deployed (and added data). 26 minutes in, we build/deploy the database project which will generate migration T-SQL (sp_rename) which doesn't touch the existing data in the database.

     


    Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question
    • Marked as answer by pretzelb Monday, August 29, 2011 9:29 PM
    Wednesday, August 24, 2011 6:31 AM

All replies

  • Where is the new column being added?  To the database or to the Entity model?  In the case of the database, it's really a snap to just regen the model.  Takes about two seconds to do.  Without the new column being defined to the model, then there's problems right?  If the new column allows null then there's probably a way around it.  You can update the mapping manually too, but I find that a little over my head and deeper than I want to get into EF. 

     

    The practical way in my mind is this: If database changes, the model must change too.  There's no way around it.  A small field added to a DB is there for a reason, and it should/must be reflected in application in my mind.


    JP
    Tuesday, August 23, 2011 10:26 PM
  • 1. Are you using Code First?

    2. Are you adding a column to the table?

    3. Are you adding a property to an entity/class?

     

    To answer your questions, without taking into account the above questions I have:

    If you're using Code First, the Code First team has been working on Code First migrations which allows you to add a property to a class and incrementally update the database. This is alpha quality: http://blogs.msdn.com/b/adonet/archive/2011/07/27/code-first-migrations-august-2011-ctp-released.aspx. If you're using the EDMX based approach, you can use the entity designer database generation power pack (http://visualstudiogallery.msdn.microsoft.com/df3541c3-d833-4b65-b942-989e7ec74c87) or Huagati Tools (http://www.huagati.com/dbmltools/) to incrementally modify the database.

    For the reverse direction (Adding a column), as JP mentions, for an EDMX, you can use 'Update Model from Database' which will incrementally update the CSDL and MSL. However this will overwrite the SSDL. Huagati tools (http://www.huagati.com/dbmltools/) has a solution for this; MS is working on this experience. There is no solution for incrementally updating code from a database in a code first scenario. However you can manually update the property that you have in your class. By-convention, Code First will look for a column that matches. Otherwise, you can use mapping configurations to map the column name to your property name.

     


    Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question
    Wednesday, August 24, 2011 12:10 AM
  • Thanks for the replies. I think maybe I asked my question too soon in my relearning of the EF because it sounds like I don't have enough of a plan yet to answer your questions back to me. Let me give it a try.

    Regarding Code First, I saw the video explaining how the team hopes to have an answer for migration but for me that isn't acceptable unless it's released. Based on that I don't think we can use Code First because it sounds like it won't allow incremental changes.

    I'm also not too keen on using non-Microsoft solutions. I have nothing against them but our policy does not allow us much leeway in tools from different vendors.

    Regarding whether we are adding to the model or the database, I may be confused but I thought they were one in the same. From my reading today one big reason to use the EF is to avoid having generate all the DB code and instead rely on the model approach. Currently we use traditional methods and create our own SP and tables and all the plumbing code that goes with it. The appeal of the EF is avoiding that. So when I say our typical scenario is adding a new column to the DB, I mean that in our traditional sense but my hopes was we could use EF to just update the model and have EF take care of the rest - without losing our data.

    Let's take a simple example. You create an app that is based on employee information. You use a model approach with the EF and manage to promote to production. After a few months the client wants to also track the mobile phone number of each employee, which means a new column of data to store and an update to the model. Is there a way to do this without losing your data but still take advantage of all the benefits of the EF that you had when you were first in design phase (and constantly dropping and recreating the DB wasn't an issue).

    Wednesday, August 24, 2011 2:02 AM
  • Giving your example of Employee that needs a new field.  If the database adds a new field MOBILEPHONE, then you can regenerate the MODEL from the table in the database in about litterally 2 minutes.  If you have properly split off the partial methods of the Entity, you lose nothing, but pick up the new fields in the model.  So, this means that ANY database change is by definition a PROGRAMMING change too.  That's not too much to ask because if you don't have the MOBILEPHONE field exposed at application layer, then why add it to the database?
    JP
    Wednesday, August 24, 2011 2:14 AM
  • P.S. I had trouble with adding new fields in Model and getting them out to the database, I found it much much easier to add to the database then regenerate the model.  In EF 4.0 there were some gaps especially with respect to field relations being written back out to db schema.
    JP
    Wednesday, August 24, 2011 2:15 AM
  • Code First migrations V1 has already released and you can download it now through the link I posted. Not sure if you're aware of that. 

    Also, if you have an EDMX-based approach, the link I posted to the database generation power pack details how to generate migration T-SQL that doesn't touch your data. See the blog post here: http://blogs.msdn.com/b/adonet/archive/2010/02/08/entity-designer-database-generation-power-pack.aspx which basically goes through a similar scenario to the one you just described regarding the mobile phone number.

    Down the road, would something like this be useful to you? (http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DEV314). Skip to 6 minutes in. When you build up your model, it will automatically build up your database 'intent' through a database project. 23 minutes in, we perform a refactoring from the database project after we've deployed (and added data). 26 minutes in, we build/deploy the database project which will generate migration T-SQL (sp_rename) which doesn't touch the existing data in the database.

     


    Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question
    • Marked as answer by pretzelb Monday, August 29, 2011 9:29 PM
    Wednesday, August 24, 2011 6:31 AM
  • P.S. I had trouble with adding new fields in Model and getting them out to the database, I found it much much easier to add to the database then regenerate the model.  In EF 4.0 there were some gaps especially with respect to field relations being written back out to db schema.
    JP


    Oh, this might explain a few things. In the early examples I'm exploring (still need to look at more) the process is focused on changing the model and having EF make DB changes. What you are saying (from a practical standpoint) is that it's easier to change the DB and then regenerate the model. Sound right?

    I might be over influenced by the intro examples which are based on new development instead of maintenance. Also it seems like I read several documents and blog posts that stated EF did not work well with a data first approach, so much so that it caused more work to try and work from the DB to the model. That's why I assumed it best to work from the model and push to the DB.

    Wednesday, August 24, 2011 12:43 PM
  • I'm not too sure I want to try a CTP release with production code but I can look at the documentation.

    I have not seen a tutorial on a specific approach titled EDMX yet but I will look into that and the link to the power pack. I will look at the video link you posted also.

    Going to take a while to absorb all that. Hopefully I won't get interrupted and I can reply soon.

    Thanks.

    Wednesday, August 24, 2011 12:56 PM
  • Yes it does sound right, in my early adoption of EF 4.0 I too tried many times to make model changes to reflect back to DB.  Had lots of problems, came to this forum and posted a bunch of them here.  I was told that there are known issues with publishing back to DB at the time.  Since then we see that 4.1 is out, but since I learned to always update DB first then regen Model I don't have any issues.  If you study how the partial methods hooking works you'll see that custom code in model will NOT be affected, this allows you to regen the MODEL at will and takes only two minutes to accomplish. 

    I did see a dramatic change in 4.1 article wise with the code first mantra, so I know they've been working on it, I just haven't had time to dig in to see if it works as billed.  Plus I really don't need to do that for now anyway given circumvention above.

    I think my recommendation for you is simple, make schema changes in DB first then regen model.  Use partial methods anytime you have to insert buisness logic.  You should be all set.


    JP
    Wednesday, August 24, 2011 2:49 PM
  • Hi,

    yes, the default behavior of EF 4.0 makes it a bit problematic, but you could have another database for the schema generation and write a schema migration script every time you need to add a property ton an enity.

    But here's a tool http://visualstudiogallery.msdn.microsoft.com/df3541c3-d833-4b65-b942-989e7ec74c87

    that simplifies the scenario, it handles migration automatically.

     

    In EF 4.1 you have CodeFirst approach option that allows you to add property to the entity and a column with the same name to the corresponding table and everything works fine (though you'll have to delete the MetaData table generated by CodeFirst)

    take a look at this http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

     

    Regards


    Clarity VS Precision
    Thursday, August 25, 2011 8:39 AM
  • Adi

    I watched your video and I definitely like what I saw. If that were available now it would be fantastic. The idea of a database "snapshot" was very interesting, especially when you simulated checking the DB project into source control so the DBA resource could make changes. This showed a clear separate between the DB and model which allowed both to change and evolve without the other. I need to rewatch that again so I can see the part where you took the simulated snapshot back from the DBA and incorporated it into VS. The only downside is that I'm not sure this helps me for projects I'm looking to create right away.

    I'd like to keep this thread open just a bit longer as I go back and review some of the information. I should be able to mark it answered soon.

    Friday, August 26, 2011 1:01 AM
  • @pretzelb:

    I replied to this thread a few days ago, but my answer seem to have disappeared.

    The next-generation tools from MSFT look great, but it will probably take some time before those are RTM/RTWd. There are third-party tools that add incremental and selective sync between the EDMX and the database to the EF model designer in Visual Studio already today. 

    One tool that can give you this functionality today is my add-in ( http://huagati.com/edmxtools/ ) - it has a feature called the "model comparer" for EFv4. It shows a detailed breakdown of the differences between the database, the EDMX/SSDL and the EDMX/CSDL and allow you to select individual differences and propagate changes from the DB->SSDL->CSDL or CSDL->SSDL->DB. Model updates will only touch the entities/members/associations you select, and database update scripts are incremental scripts containing only the model changes you want to bring over to the database ("alter table xyz add [column]") etc.

     

    The following links will bring you to a couple of blog posts describing/showing the functionality I think you are looking for:

    http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html

    http://huagati.blogspot.com/2011/05/entity-framework-model-creation-beyond.html

    http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html

     

    Hope that helps!


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    Friday, August 26, 2011 3:14 AM