1

Closed

Improve database first model generation on subsequent updates

description

When using the designer to update an existing model from a database there are some scenarios that do not update properly. My use case scenario is based around only doing database first and doing every single table, view and sproc in my database in one edmx. So I never want to manually edit my edmx. I just want to sync against the database to pull in any new tables, columns, sproc changes, etc. EF currently has some problems with this. It should update the edmx at all times to properly match the database. Saying I have to manually edit anything in the edmx totally defeats the purpose of "database first" for my purpose.

I did some tests of various scenarios and here is what works and doesn't.
  1. Add brand new table to DB then update model - WORKS
  2. Add new column to existing table - WORKS
  3. Change the data type on existing column - FAILS. Still shows old data type
  4. Remove column from an existing table - FAILS. Doesn't remove it.
  5. Add new sproc to database then update model - WORKS
  6. Change the input parameters of existing sproc - WORKS
  7. Change sproc result set from a single value to actual result set - FAIL
    ex. Change from SELECT 1 as Col1 hard coded to SELECT 1 as Col1, 'Test' as Col2. First scenario makes it an int return type. Second should be a custom complex type. It will never switch it. Only way to fix this is in the designer to use the model browser and delete both the function import and stored proc. Then reupdate the model from the database.
  8. Change columns in the result set - FAIL. Exact same issue as #7.
    Any changes to the sproc columns and output seems to never update properly.
My expectation is all of these things should work. Perhaps "database first" is mean to be "database once" and not reused over and over. But my idea of it should be more complete syncing. I would suggest adding a checkbox in the designer that just says "overwrite my model" or something that blows away those entities as they exist and completely updates them to what the database says.

I can understand why people would do database first and then add a column to the model. However, I think they can just as easily do it with a partial class and not touch the emdx. That way the edmx is in sync with the database and if they want to extend the entity class to add other fields, they can do so in their own code in partial classes.

For my purposes I would be even happier to have edmgen2 work properly to produce an edmx for the database. Currently it does not work with stored procedures properly. But I do strongly feel the UI should also do complete database model syncing properly.

The most frustrating of all about EF is even if I go in and delete everything in the EDMX and try to just rebuild the whole thing by syncing from the database again, it doesn't really work. I believe it works for tables but it doesn't for stored procedures. I can't actually open the designer and delete the stored procedures for example all at once. You have to use the model browser part of it and delete them which is a complete nightmare if you have a lot of stored procedures.

Simply put, how EF works in regards to database first only sort of works and is a nightmare for real world use. Database first to me sets the expectation that I don't plan on manually modifying my entities or writing any code. I want to just sync it from the database. I want what sqlmetal or edmgen do basically.
Closed Jan 7, 2014 at 6:10 PM by RoMiller
EF Team Triage: This is mostly By Design, parts of the conceptual model are preserved when you update from the database (just storage and mapping are updated). This is to allow you to edit the conceptual model without loosing changes when you update.

We agree this could definitely have been implemented better and only preserved changes that you actually make to the conceptual model. Unfortunately this is fundamental to the way the designer is implemented and we can not spend the time/resources to re-implement it.

While not ideal, deleting and re-creating the EDMX is a reasonable work around to achieve the desired behavior.

comments

ErikEJ wrote Jan 2, 2014 at 6:39 AM

even if I go in and delete everything in the EDMX
  • why not simply nuke the edmx and start over each time?
Could you share a couple of edmx files from Adventureworks that demonstrates the stored proc issue, then I can investigate if the edmgen2 code can be improved.

sporty81 wrote Jan 2, 2014 at 3:31 PM

Deleting the EDMX out of the project and recreating one from scratch takes way too long.

The problem with edmgen2 is it doesn't populate the CSDL and MSL portions for sprocs which declare the return types and complex type of the results of the sproc. I will work on getting some samples for you.

vcpatel wrote Aug 6, 2014 at 5:35 PM

Hi, I have been battling the same issue for last few days. I used EDMGEN2 to create EDMX. And we only use Store Procs in our EDMx. SSDL looks perfectly fine but CSDL is missing function import entries for all the store procs. Have you guys found any solution for this?

sporty81 wrote Aug 6, 2014 at 6:36 PM

@vcpatel For whatever reason the EF team doesn't care about making it work properly in database first type mode in the designer or via command line. They instead claim you need to do a 5-10 step process to remove the current sproc stuff out of the designer, and then do an update from database in the designer again. It is very frustrating.

vcpatel wrote Aug 6, 2014 at 6:46 PM

@sporty81 so it looks like there is no way for us to generate EDMX from Database the way we want. This is really mind boggling. The reason I am trying here is because i want to generate this edmx pragmatically and then build all the projects from that. So that if the Database guys have made any changes in store procs or any thing else, i could catch it in the build cycle.

sporty81 wrote Aug 6, 2014 at 8:29 PM

@vcpatel What is even more frustrating is they already do this with linq to sql via sqlmetal. But not with EF.

vcpatel wrote Aug 6, 2014 at 9:13 PM

@sporty82 Seriously. I was so pumped up to generate edmx the way i wanted and this killed all the excitement. Any idea if they will ever add this to the EF?