CODEWITHSUNDEEP
sqlasp.net-mvc-3entity-framework-4.1
Tripping
Tripping

Reputation: 919

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 - asp.net 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));
            context.SaveChanges();
    }

Thanks

Upvotes: 5

Views: 13093

Answers (5)

Mark
Mark

Reputation: 599

I am using MVC 4 code first with Entity Frame. I moved my membership to a different database, added Tripping's code to Application_Start(), am not using SSMS, moved the code up to the beginning of the start method. Still got the error just like (a different) Mark says.

Resolved by following https://stackoverflow.com/a/7007818/2332919 to add Pooling=false to my connection string, which got me going again, but at what cost?

Upvotes: 0

Tripping
Tripping

Reputation: 919

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();         
db.Database.Initialize(true);

Upvotes: 5

StanK
StanK

Reputation: 4770

I've had this problem before when I have connected to a SqlExpress database using SQL Server Management studio, and have a couple of queries open when I've started my project.

If this is what you are doing - try disconnecting from the database in SSMS before starting your project.

Upvotes: 7

Garrett Fogerlie
Garrett Fogerlie

Reputation: 4458

What does the controller that return db.UserAddress.SingleOrDefault(a => a.UserId == userid); looks like.

I would try, "return db.UserAddress.SingleOrDefault(a => a.UserId == userid).ToList()"

And see it that fixes the problem.

Upvotes: 0

Mark Oreta
Mark Oreta

Reputation: 10416

Do you have your Initializer in your Application_Start() in your Global.asax so its the first thing to happen?

protected void Application_Start() {
    Database.SetInitializer(new NPLHWebsiteInitializer());
}

Upvotes: 0

Related Questions