Tripping Tripping - 2 years ago 315
SQL Question

Cannot drop database because it is in use + EF Code first

I am new to asp .net mvc3

I am trying to store additional user information over the standard asp .net membership provider. I have created a new entity UserAddress and I sync it with the aspnet_tables using the membership API.

However, now if I make any changes to the UserAddress class - does not drop and recreate the model. I get the error

Cannot drop database because it is in use.

I have searched stackoverflow and google. Basically, my problem is similar to this

Database in use error with Entity Framework 4 Code First

However, I am not sure how to implement what chris suggest as a solution:

"This was happening to me because I was calling Membership methods against the DB and that was creating a conflict. I resolved this by forcing the initializer to run and seed before using the Membership system"

I am pretty sure this is the reason why the database is in use because my code falls over at this point in the data repository:

var userid = Membership.GetUser.ProviderUserKey.ToString();
return db.UserAddress.SingleOrDefault(a => a.UserId == userid);

How can I force the initializer to run before membership system?

Snippet of my code is below:

public class UserAddress
public int UserAddressId { get; set; }
public string UserId { get; set; }
public string FlatNo { get; set; }
public string BuildingName { get; set; }
public string Address1 { get; set; }
public string Address2 { get; set; }
public string PostCode { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public long MobilePhone { get; set; }

public class NPLHWebsiteDb : DbContext
public DbSet<UserAddress> UserAddress { get; set; }

public class NPLHWebsiteInitializer : DropCreateDatabaseIfModelChanges<NPLHWebsiteDb>
protected override void Seed(NPLHWebsiteDb context)
var userAddress = new List<UserAddress>
new UserAddress {
UserAddressId = 1,
UserId = "153",
FlatNo = "6.4",
BuildingName = "Wilton Plaza",
MobilePhone = 9810110123,
userAddress.ForEach(a => context.UserAddress.Add(a));


Answer Source

As suspected the culprit was 'Membership'.

The code uses membership to look up the address for the logged in user. Therefore, if I logged in the website then my Membership tables would get called and SQL would not allow the initalizer to drop and recreate table as it is currently in use (i.e. by Membership).

This is exactly what Chris pointed in stack overflow question I had linked to in my original question

Basically, the solution was to force the database to initalize before Membership is called. I was not sure how to do that. I was trying to do in manually by entering the URL to access the user address page. However, that was not working as it again failed at the line which calls membership tables.

The correct way to do this was to force the initialization in Application start as follows:

System.Data.Entity.Database.SetInitializer(new MyInitializer());          
MyContext db = new MyContext();         
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download