Exception when code first tried to create database

Question

I've created a new ASP.NET MVC 4 application, and would like it to use code first. However it doesn't seem to initially create the database file if it doesn't exist already. If I delete the .mdf file from the App_Data folder, then I get the following exception when the app tries to access the database:

System.Data.SqlClient.SqlException: Cannot attach the file '<path-to-db-file>.mdf' as database '<my-db-file-name>'.

If I run it in the app in the debugger, then I can see that the exception is occurring in the InitializeSimpleMembershipAttribute::OnActionExecuting method when calling LazyInitializer.EnsureInitialized. The caught exception is:

[System.Reflection.TargetInvocationException]   {"Exception has been thrown by the target of an invocation."}   System.Reflection.TargetInvocationException

With an inner exception of:

[System.InvalidOperationException]  {"The ASP.NET Simple Membership database could not be initialized. For more information, please see http://go.microsoft.com/fwlink/?LinkId=256588"} System.InvalidOperationException

Which then has the first exception I mentioned above as the inner exception of that.

Any ideas what I'm doing wrong?

Update

I've just tried it with a brand new MVC4 app. I can replicate it by doing the following:

1. Create the MVC app in the VS wizard.
2. Run the app for the first time and go to the login page (note the mdf file now gets generated).
3. Delete the mdf file, and go back to the login page. The exception is now thrown.

Solution 1

Got into the same problem and found my solution here. all you have to do is stop LocalDb by opening the VS developer command prompt and enter (without quotes):

"sqllocaldb.exe stop v11.0"

"sqllocaldb.exe delete v11.0"

Next time EF will regenerate the file as well as the db.

Solution 2

If you dig into your application's InitializeSimpleMembershipAttribute class you'll see the following overridden method of the ActionFilterAttribute class from which it inherits:

    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        // Ensure ASP.NET Simple Membership is initialized only once per app start
        LazyInitializer.EnsureInitialized(ref _initializer, ref _isInitialized, ref _initializerLock);
    }

Notice that comment. The application is checking once per run to ensure that Simple Membership has been properly initialized. Look at the private variables at the top of the class:

    private static SimpleMembershipInitializer _initializer;
    private static object _initializerLock = new object();
    private static bool _isInitialized;

They are all static and they are all passed as refs. The important one here for your situation is _isInitialized. What LazyInitializer.EnsureInitialized does is checks the _isInitialized flag and if it is false it initializes the ref target it's passed, in this case the _initializer of type SimpleMembershipInitializer. At that point it sets the flag to true and moves on. If LazyInitializer.EnsureInitialized sees that the flag is true it does nothing but return the target. Since that flag is a static variable, it persists its value for the life of the application which means that after that first initialization EnsureInitialized will always simply return the target even if the database file no longer exists. In other words, if you delete the .mdf file after initialization the application will not know and exceptions will be thrown when the app tries to read or write from the database. In order to resolve this you have to restart the application, which means killing the dev server if that's what you're using or restarting the application in IIS.

So that is the problem that you are facing, but I suspect that many of the folks running across this question are having a similar but different issue where they receive an error message similar to this one when attempting to log in:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\path\to\your\project\App_Data\dbfile.mdf'. CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

This is very easy to solve to I'll cover that quickly here as well.

By default Visual Studio 2012 uses a pared down version of SQL Server Express called LocalDB. LocalDB will spin up a a child process of the application, and if you're running your application on Visual Studio's development server (eg http://localhost:[port] is what shows up in your browser) then you are running both the application and LocalDB under your user account, not under SYSTEM or NETWORKSERVICE. All you have to do to fix this is assign Modify permissions to your user for the App_Data folder of your project. Try the login again and the .mdf file should be created successfully.

If you're curious, you can learn more about LocalDB here.

Solution 3

You can handle Code First database initialization in the Application_Start method of Global.asax file in root folder of your project, like the following:

    protected void Application_Start()
    {
        Database.SetInitializer<MyDBContext>(null);
    }

If you pass null to SetInitializer it won't create or alter your database tables, you should do it manually.

The reason why the database is not re-generated is that Application_Start is fired only once during the application lifetime.

Solution 4

The MVC-generated code works with a database connection string named "DefaultConnection". If you used a different name in Web.config, you need to refer to this name in:

1. InitializeSimpleMembershipAttribute.SimpleMembershipInitializer.ctor() in InitializeSimpleMembershipAttribute.cs).
2. UsersContext.ctor() in AccountModel.cs

(or just search for "DefaultConnection" in your project).

Solution 5

You should never delete autocreated .mdf files in Explorer, only through the SQL Management tools or in the Object Explorer.

The issue you are getting (and can replicate in the steps you've provided) is that the database is still registered in LocalDb.

View more solutions