Generating a database script from entity framework code-first model

4
votes

I'm using entity framework with code-first (primarily because with RIA Services, you need to have some form of the code classes anyway for additional attributes).

I want to get a database generation script to easily update the visual studio database project. I don't want to use ef migrations, I prefer the database projects.

I can use something like

string sqlscript = (context as IObjectContextAdapter).ObjectContext.CreateDatabaseScript();

at runtime, but I want to have a convenient method to get the script without running the project in some way, and without using migrations.

Does anybody have any ideas?

I'm using ef4.1 and ef5 in different projects.

[EDIT: One way of answering this question would be a way to call above line from the package manager console.]

4
entity-framework
Migrations are tool for creating SQL scripts at design time. There is currently no integration between EF code first and database projects in VS. I would say your workflow uses wrong direction. With database project you should start with the database definition, build a database and generate classes from the database. Your direction is not supported - at least officially and I doubt it will be any soon. – Ladislav Mrnka
@LadislavMrnka I'm not looking for any integration. I only want the database generation script, ie what comes from "ObjectContext.CreateDatabase". I only use the database projects to generate update scripts - basically just like migrations. The reason why I don't use code-first migrations is because I doubt they are a lot less mature and feature complete in comparison to the database project. – John

4 Answers

4
votes

One way is using the Entity Framework Power Tools. They add a context menu entry for the file containing the DbContext class which generates a DDL file for the database.

Another way is using LinqPad. After opening the dll in LinqPad, one can execute the code snippet given in the question in a query:

(this as IObjectContextAdapter).ObjectContext.CreateDatabaseScript()

Both ways are slightly inconvenient though as they require third-party tools.

3
votes

I have enabled migrations but I always do get the script at runtime without using the Package Manager Console to update the database as I have dynamic entities that can only be discovered at runtime depending on what references are included in the project.

The code to get the script looks like this:

var config = new DbMigrationsConfiguration<MyContext> { AutomaticMigrationsEnabled = true };
var migrator = new DbMigrator(config);
var scriptor = new MigratorScriptingDecorator(migrator);
string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);
1
votes

You can also generate custom queries using the `ObjectQuery class. Here's an example:

var query = from emp in context.Employees
            select emp;

var sqlScript = ((ObjectQuery)query).ToTraceString();
1
votes

An approach you can consider is to use ef migrations to generate the script, then use MSBuild post build task to copy the script over to the database project at build time.