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:

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).

Published Friday, March 25, 2011 9:54 AM by Keith Barrows

Comments

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Saturday, March 26, 2011 4:31 PM by Koistya `Navin

..drop-and-recreate practice is not suitable for devs who need complete control over DB schema anyway.

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Thursday, August 11, 2011 6:34 PM by Richard Weeks

I can't get it to create the database in SQL Server 2008.  What does your connection string look like?

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Wednesday, October 5, 2011 6:17 PM by John

Same as above...I cannot get the EF41 to create the database.

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Thursday, March 1, 2012 2:08 AM by eli manning Jerseys

In this modern and fashionable society, Thanks for the informative article!

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Monday, November 26, 2012 10:14 AM by dcSWLIivfU

aGuTIS Thanks for the article.Thanks Again. Awesome.

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Friday, February 1, 2013 8:15 AM by EcitkvucLXelDVnhA

XgiQK5 I really enjoy the blog post.Really looking forward to read more. Really Great.

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Wednesday, February 27, 2013 10:04 PM by ilcFcsfRntzMJEqWJE

bQytNO I loved your blog article. Much obliged.

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Tuesday, April 16, 2013 3:35 AM by Francis

Excellent goods from you, man. I have understand your stuff previous to and you

are just too magnificent. I actually like what you've acquired here, really like what you are stating and the way in which you say it. You make it enjoyable and you still take care of to keep it smart. I cant wait to read far more from you. This is actually a terrific web site.

# re: Entity Frameworks 4.1 on SQL Server–some gotchas

Saturday, May 25, 2013 3:39 AM by zcHXsJmjpuUeQlkZNnm

YaNljF Really appreciate you sharing this article. Want more.