Entity Frameworks 4.1 on SQL Server–some gotchas

I am using MVC3, Visual Studio 2010 SP1 & Entity Framework 4.1.

I was very excited to get my hands dirty with the Code First piece of the new Entity Frameworks.  There are some great articles out there for getting started and a couple of my favorites are:

• http://thedatafarm.com/blog/data-access/new-ef4-amp-ef4-1-content-on-msdn/ by Julie Lerman
• http://weblogs.asp.net/scottgu/archive/2011/03/19/rc-of-entity-framework-4-1-which-includes-ef-code-first.aspx by Scott Guthrie
• http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx by Scott Guthrie (based on a CTP so some things are still changing)

Armed with the above information I started right in on my first Code First project, more to learn how to rather than create something lasting.  I decided to target a “real” SQL Server as I already had SQL 2005 on my dev machine.  I am also keeping in mind that I want to rewrite my own site (http://sol3.net) and that runs on a SQL 2008 shared hosting server.  So, I went into SQL and created a SQL Server based Login with the right privileges.  Now remember, on a shared hosting plan what ever SQL Login you are using will not have rights to drop the database which appears to be one of the things Code First will do when it detects a change. 

Needless to say, I have not got it working with a SQL Login locally so opted to switch to a Trusted Login based on my NT account.  That worked like a charm – the first time I ran the code.  Database built, objects in place, etc.  The next step was seeding some data each time a new build is executed.  (See http://thedatafarm.com/blog/data-access/seeding-a-database-with-ef4-code-first/ for how to do this.)  Now we start in on the gotchas!

1) While doing this I had SQL Manager open so I could execute some quick queries to see the data after creation.  When I ran my project I started getting some weird errors and spent about 2 hours tweaking my seed code to figure out what was causing it.  Then I had one of those <say “Doh!” voice=”Homer SImpson” /> moments.

• If you have a SQL window open on the database then Code First bombs as the DB is locked and can’t be dropped.
• Even if you close the window but the SPID is still spun up in SQL you will get errors.
• If you have the DB (or any part) actively selected in the tree within SQL Mangler, again, you get errors.

So please, avoid my pain and check your database to see if it is locked!  Code First does not work on a locked database.  It has to be able to drop the DB and recreate it (as I understand it so far).