I am trying to set up a continous delivery flow around a new ASP.NET Core / EF7 application.
I would like to use Code First EF7 to generate and update my local dev database, and then get the changes into my SSDT database project. From there I plan to use the MSDeploy with dbSqlPackage Provider to update my Azure SQL prod db with any changes when I deploy the webapp to my Azure App Service. https://msdn.microsoft.com/en-us/library/hh550081(v=vs.103).aspx
Also note, that I am going to have some kind of pre-prod deploy step, where I do some testing on the preprod system, before I mindlessly deploy the db updates to production.
My question is - how do I locally on my devbox, automate the step where I update the SSDT project to reflect my local dev database? I can do a manual SSDT compare from the database, to the SSDT project, and that will update the SSDT project sql files. I was looking at the SQLProject.exe, but from what I can see, it will only create dacpac's or publish to a database.
Anyone knows of a way to automate this step?
I think that given your setup I'd be looking at EF migrations rather than SSDT, particularly given that you are on EF7 and migrations are, like syphilis, not as awkward as they used to be.
Assuming you don't want to pursue EF migrations, there's no easy way to automate the generation of a .sqlprojand associated .sql files from a deployed database; but if you don't care about the database project itself, it isn't too difficult to use sqlpackage - or your own code on top of DacFX - to generate a .dacpac from your deployed database, and then use that as the artefact that you promote through your other environments.
As for managing the pre-prod / prod segregation, there are a bunch of ways to do this, including but not limited to VSTS Release Management - which has built in tasks for deployment to Azure SQL DB.