CODEWITHSUNDEEP
c#databaseentity-frameworkcode-firstef-code-first
Amadiere
Amadiere

Reputation: 11416

EF4 Code-First causes InvalidOperationException

I'm having an issue when trying to run my project each time it builds. It seems the initializer runs, but when it comes to the first query - it dies with the following InvalidOperationException.

This operation requires a connection to the 'master' database. Unable to create a
connection to the 'master' database because the original database connection has
been opened and credentials have been removed from the connection string. Supply
an unopened connection.

For reference, I'm using the EF Code First CTP4, imported directly with NuGet. Connecting to a SQL Server 2008 R2

What I want to happen is to re-create the database if there are any model amendments and seed it with a few values for the lookup table. Both of these things seem to be supported* out of the box.

My setup is like so:

Global.asax

 protected void Application_Start()
 {
    Database.SetInitializer<CoreDB>(new CoreDBInitialiser());
    // et al...
 }

CoreDB.cs

public class CoreDB : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Login> Logins { get; set; }
    public DbSet<Permission> Permissions { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<RolePermission> RolePermissions { get; set; }
    public DbSet<UserRole> UserRoles { get; set; }
    public DbSet<Setting> Settings { get; set; }
}

public class CoreDBInitialiser : RecreateDatabaseIfModelChanges<CoreDB>
{
    protected override void Seed(CoreDB context)
    {
        var settings = new List<Setting>
        {
            new Setting
            {
                SettingName = "ExampleSetting",
                SettingValue = "This is a sample setting value",
            }
        };

        settings.ForEach(d => context.Settings.Add(d));
    }
}

When it runs, it dies on a line similar to this, which is basically the first query it comes across after creating the database.

User data = (from u in _data.Users where u.Username == userName  select u).SingleOrDefault();

Things I don't think it is:

<add name="CoreDB" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=TheDatabase;User Id=TheUsername;Password=ThePassword;" providerName="System.Data.SqlClient" />

What to do?

Ideally, I'd like to "not think about the database schema" much. I'd like it to be as it seemed to be in Scott Gu's excellent blog post (and follow up for working with existing databases) where things just worked out and disappeared away. For the most part this is true. It seems to be an issue with the connection not being closed at some point, but I can't find out how to rectify this issue.

A few forum / SO posts do imply the issue I'm having is basically because the initializers aren't working exactly as planned and the connection might be left open. The solution in other places appears to be to simply "don't create your own initializer", which isn't the greatest solution - but unless anyone has any ideas, I'll probably have to do until CTP5 maybe.

*yep, I know it's a CTP, so "supported" is probably not the word :)

Upvotes: 28

Views: 17635

Answers (7)

Erik Bergstedt
Erik Bergstedt

Reputation: 912

I had the wrong password. Strangely worded exception.

Upvotes: 0

akshay_mendki
akshay_mendki

Reputation: 31

Add this to your connection string. It worked for me.

Integrated Security=True;Persist Security Info=True;

Upvotes: 3

Ben Anderson
Ben Anderson

Reputation: 7343

As requested I am posting my comment as an answer.

My solution was very similar to bizon where the Trusted_Connection and Persist Security Info needed correction but I accomplished it through visual studio properties by going to:

Server Explorer -> Modify Connection -> Advanced -> Then check both Persist Security Info and TrustServerCertificate as True, and it formatted the connection string correctly

screenshot of visual studio gui

Upvotes: 6

ChandlerQ
ChandlerQ

Reputation: 1438

Just run into this problem while I follow this under VS2012 and EF6. My solution is quite simple:

In the Connection Properties dialog which pops up when choosing "Reverse Engineer Code First", check "Save my password".

Problem solved, but I don't know the details about it...

Upvotes: 5

user1308816
user1308816

Reputation: 31

Never posted before, learning Entity Framework using MVC, using 2010 and a dev database server running SQL Server 2005 for the database. I'm mainly a windows app guy.

First part of tutorial went fine, it created the three tables thus far, then I encountered the error above after adding some more classes and the database strucure needed to change.

My fix was pretty brutal, I went onto the dev server and went into my login and I am a sysadmin on my own server, not surprising. So I went into usermapping and selected master and just ticked every box. It now works.

I don't know why this has worked or what the issue is, but hopefully someone who knows more than me can build on this 'bad fix' and make it better for others who have this issue.

Upvotes: 0

bizon
bizon

Reputation: 2414

I know that it is to late for answer, but this post is high on Goolge and answer may be useful for someone. The problem is missing credentials. To prevent this you need to change your connection string to have:

Trusted_Connection=False;Persist Security Info=True

Based on this article

Upvotes: 49

Amadiere
Amadiere

Reputation: 11416

I was unable to get this to work as I wanted. This answer is a bodge / opt-out, so be aware when making your decision on what to do.

I have reverted away from Code-First for the meanwhile until it's at a more stable version. For anyone who is having this problem with the CTP4 as well, then you can get around it by losing your initializer and passing NULL into the SetInitializer method.

Database.SetInitializer<CoreDB>(null);

This of course means that you need to maintain your database each time you make a change, or change it for a single run and then change it back after it creates the DB.

Upvotes: 0

Related Questions