Any way to speed up CreateIfNotExists in Entity Framework?

Question

I'm using EF 4.3 Code First on SQL Server 2008. I run several test suites that delete and recreate the database with CreateIfNotExists. This works fine but is dog slow. It can take up to 15 seconds to create the database on the first call, and typically 3-6 seconds after that. I have several places where this is called. I've already optimized to call this as few times as I can. Is there something I can do to speed up database creation programmatically? I'm willing to go around EF to do this if that helps, but I would like to keep my database build in code and not go back to a SQL script. Thanks!

Answer 1

This works fine but is dog slow.

Yes. The point is to use the real database only for integration tests which don't have to be executed so often and the whole set of integration tests is usually executed only on build server.

It can take up to 15 seconds to create the database on the first call

This is because of slow initialization of EF when unit testing (you can try to switch to x86). The time is also consumed by view generation. Views can be pre-generated which is usually done to reduce startup and initialization of the real system but in case of speeding up unit tests using view pre-generation will not help too much because you will just move the time from test to build.

I'm willing to go around EF to do this if that helps, but I would like to keep my database build in code and not go back to a SQL

Going around would just mean using plain old SQL script. The additional time needed for this operation is may be spent in generating that SQL. I think the SQL is not cached because normal application execution normally doesn't need it more than once but you can ask EF to give you at lest the most important part of that SQL, cache it somewhere and execute it yourselves every time you need it. EF is able to give you SQL for tables and constraints:

var dbSql = ((IObjectContextAdapter) context).ObjectContext.CreateDatabaseScript();

You just need to have your own small SQL to create database and use them together. Even something like following script should be enough:

CREATE DATABASE YourDatabaseName

USE YourDatabaseName

You must also turn off database generation in code first to make this work and to take control over the process:

Database.SetInitializer<YourContextType>(null);

When executing database creation SQL you will need separate connection string pointing to Master database.