Dacpac/SSDT Integration

[rowanmiller]

There are a couple of scenarios here:

• Generate a model from an existing database project (Feature Request: EF tools and SSDT integration #1186)
• Rather than using EF Migrations, translate model changes to schema changes in a database project
• Generate a dacpac from an existing model

Note that I don't think we should look at integrating migrations and dacpac/SSDT in anyway - we should allow folks to use dacpac/SSDT instead of migrations. The main reason for this is that they use fundamentally different approaches to how schema changes are tracked. Migrations is very explicit and has a step by step process to take a schema thru a well ordered set of states (i.e. no schema comparison is done when applying migrations, we just run the exact steps recorded in each migration). Dacpac/SSDT takes a target schema and some other info (like refactor log) to calculate the changes during deployment.

[rowanmiller]

BTW here is some info on the API for interacting with dacpac http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.aspx

[ErikEJ]

The latest daily build of "EF Core Power Tools" allows you to reverse engineer from a Dacpac https://github.com/ErikEJ/SqlCeToolbox/wiki/EF-Core-Power-Tools

[kant2002]

Link to DacPac public model now is here:
https://devblogs.microsoft.com/ssdt/dacfx-public-model-tutorial/

[bricelam]

I turned Erik's awesome code into a NuGet package. Now you can do this too:

dotnet add package ErikEJ.EntityFrameworkCore.SqlServer.Dacpac
dotnet ef dbcontext scaffold My.dacpac ErikEJ.EntityFrameworkCore.SqlServer.Dacpac

[syndicatedshannon]

@bricelam Thank you for that. I think we're pretty far past that in our development. That will only work for early dev, right? I think we'll find that not everything will be representative, need to apply attribute/fluent overrides, and then not be able to run scaffold again without losing our work.

I'm still holding out hope that EF team will get to this / #1186 issue. A little less hopeful when I see attempts to adapt code-first to DACPAC tasks, though...

Anyway, you've become a staple in my work life, through this window on you. Thanks for the work you've done!

[ajcvickers]

Functionality is covered by EF Core Power Tools. We don't plan to implement anything here.

[AliFayazbakhsh]

@ajcvickers:
EF Core Power Tools have great support for reverse engineering scenarios, but forward engineering (Entity Model to Data Model) is not covered yet.
It will be very helpful to generate Data Model from Entity Model especially in SSDT Database project, so we can add other database objects (stored procedures, functions, ... ) there and use all compare and migrate tools in SSDT.
It will be better if it can be integrated into compiling pipeline (source generator).

[ErikEJ]

@AliFayazbakhsh You can create a SQL script from the current model using EF Core Power Tools, and import that into the database project

[AliFayazbakhsh]

@ErikEJ
Thank you,
I look for some more automated way, such as compile pipeline generator or CLI command to use in post build script (similar to #4321 (comment)).

[ErikEJ]

@AliFayazbakhsh

dotnet ef dbcontext script ?

and then some sqlpackage command perhaps?

[AliFayazbakhsh]

@ErikEJ
Yes, It is what I'm looking for.
Although the script that generated by dotnet ef dbcontext script is not fully compatible by SSDT, for example in SCHEMA creating. I'm working to fix it with some PowerShell script but it can be an option or switch in CLI.