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.

Im not 100% sure but try running this as admin instead, its using the localdb with your current user so it shouldn't have a permissions issue. You can also connect to (LocalDb)\v11.0 via SSMS which will let you look at the actual permissions — undefined
– undefined, Commented Jun 20, 2012 at 1:13
Hm.. If I try to connect to localdb in SSMS it gives me this error: "Property Login was not set" — ledgeJumper
– ledgeJumper, Commented Jun 20, 2012 at 1:17
You could try rebuilding your local db as outlined here social.msdn.microsoft.com/Forums/eu/LightSwitchDev11Beta/thread/… — undefined
– undefined, Commented Jun 20, 2012 at 2:43
sweet, I was looking around for how to do that. I had to step away from the project last night, but i will give this a go tonight. — ledgeJumper
– ledgeJumper, Commented Jun 20, 2012 at 13:32

sazh · Accepted Answer · 2012-06-20 01:18:35Z

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.

Eric J. · Accepted Answer · 2012-06-20 00:31:05Z

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); } }

I definitely liking this idea, I am working on implementing it now.
In my actual code, I have a List<string> of DDL commands and execute each of them in a loop. Makes it easy to keep all DDL in one place.
Check my rather long edit to see how this all panned out for me. Not sure what was causing the original issue, will have to investigate, but i for sure like your answer here to give me finer control over generating the db. Thanks!

Collectives™ on Stack Overflow

Cannot open generated database from EF Code first

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related