Cannot open generated database from EF Code first

Question

The error I am getting is "Cannot open database "NerdlyDB" requested by the login. The login failed. Login failed for user 'ComputerName\User Name'.

This is my connection string:

<add name="NerdlyDB" connectionString="Data Source=(LocalDb)\v11.0; Integrated Security=SSPI; initial catalog= NerdlyDB" providerName="System.Data.SqlClient"/>

This database was generates by using EF code first approach. I am new to this one, so I will show you what I did in case it is off somewhere:

On of my Entity Classes:

namespace NerdlyThings.Models
{
public class Post
{
    public int ID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string Content { get; set; }
    public DateTime Date { get; set; }
    public string Tags { get; set; }
}
}

DBContext Class

namespace NerdlyThings.Models
{
public class NerdlyDB : DbContext
{
    public DbSet<Post> Posts { get; set; }
    public DbSet<Comment> Comments { get; set; }
    public DbSet<Image> Images { get; set; }
    public DbSet<Quote> Quotes { get; set; }
}
}

I see that the error is obvious an authentication issue, but I don't know where to set it using code first, only via setting up a db in sql server management studio.

*EDIT***

Ok, so I am not by the computer I originally did this on, but I had some time to kill at work so gave this another go by following the simple instructions here

I did this in Visual Studio 2012 RC in an MVC4 internet application template. Works like a dream and I can only assume I have either some strange configuration issue on my other computer, or something got messed up along the way. Anyway here is what I did:

Classes:

namespace CodeFirstBlog.Models
{
public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string BloggerName { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime DateCreated { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public ICollection<Comment> Comments { get; set; }
}

public class Comment
{
    public int Id { get; set; }
    public DateTime DateCreated { get; set; }
    public string Content { get; set; }
    public int PostId { get; set; }
    public Post Post { get; set; }
}
}

DBContext Class:

namespace CodeFirstBlog.Models
{
public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<Comment> Comments { get; set; }
}
}

I set these up and then created a controller like so (Just had it generated my selecting my context and the class):

public class BlogController : Controller
{
    private BlogContext db = new BlogContext();

    //
    // GET: /Blog/

    public ActionResult Index()
    {
        return View(db.Blogs.ToList());
    }

    //
    // GET: /Blog/Details/5

    public ActionResult Details(int id = 0)
    {
        Blog blog = db.Blogs.Find(id);
        if (blog == null)
        {
            return HttpNotFound();
        }
        return View(blog);
    }

    //
    // GET: /Blog/Create

    public ActionResult Create()
    {
        return View();
    }

    //
    // POST: /Blog/Create

    [HttpPost]
    public ActionResult Create(Blog blog)
    {
        if (ModelState.IsValid)
        {
            db.Blogs.Add(blog);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        return View(blog);
    }

    //
    // GET: /Blog/Edit/5

    public ActionResult Edit(int id = 0)
    {
        Blog blog = db.Blogs.Find(id);
        if (blog == null)
        {
            return HttpNotFound();
        }
        return View(blog);
    }

    //
    // POST: /Blog/Edit/5

    [HttpPost]
    public ActionResult Edit(Blog blog)
    {
        if (ModelState.IsValid)
        {
            db.Entry(blog).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index");
        }
        return View(blog);
    }

    //
    // GET: /Blog/Delete/5

    public ActionResult Delete(int id = 0)
    {
        Blog blog = db.Blogs.Find(id);
        if (blog == null)
        {
            return HttpNotFound();
        }
        return View(blog);
    }

    //
    // POST: /Blog/Delete/5

    [HttpPost, ActionName("Delete")]
    public ActionResult DeleteConfirmed(int id)
    {
        Blog blog = db.Blogs.Find(id);
        db.Blogs.Remove(blog);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    protected override void Dispose(bool disposing)
    {
        db.Dispose();
        base.Dispose(disposing);
    }
}

Then I simply ran the application, used the generated views to create a new Blog. Works great. The database was generated in my App_Data folder, I can access it fine and see the generated schema. So problem solved, maybe? From this point I can use your suggested answers to tweak db settings and whatnot, so thank you.

Answer 1

Are you connecting your DBContext class to that connection string somewhere? My guess is that is missing and therefore causing the problem. I've usually seen the connection string specified in the constructor of the DBContext class (see example below):

public class NerdlyDB : DbContext
{
    public NerdlyDB() : base("NerdlyDB")
    {
    }
    ...

This will then look for a <connectionString> with name="NerdlyDB" in your web.config / app.config. See this link for more info on setting up EF with your connection string.

I would also recommend using @Eric J.'s approach in your code. You can see a pretty in depth example of his suggestion (and EF code first in general) at silk.codeplex.com (specifically the MileageStats.Data.SqlCe project)

On a separate note, it looks like you are missing the actual code first model setup. Check out the MileageStatsDbContext.cs file in the Silk project to see an example of how to do that.

Answer 2

The best approach I have found so far is to create my own IDatabaseInitializer to run commands that I need to create indices and create users.

public class MyContext : DbContext
{
    static private Initializer DbInitializer;
    static MyContext()
    {
        DbInitializer = new MyContext.Initializer();
        Database.SetInitializer<MyContext>(DbInitializer);
    }
}
public class Initializer : IDatabaseInitializer<MyContext>
{
    public void InitializeDatabase(MyContext context)
    {
        string ddl = "(Some SQL command to e.g. create an index or create a user)";
        context.Database.ExecuteSqlCommand(ddl);
    }
}