.Net Visual Studio create SQL Server can't see DBContext class

Question

This is from a tutorial. I have two models, Restaurant and RestaurantReview.

Restaurant is like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace OdeToFood.Models
{
    public class Restaurant
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public ICollection<RestaurantReview> Reviews { get; set; }
    }
}

And RestaurantReview is like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace OdeToFood.Models
{
    public class RestaurantReview
    {
        public int Id { get; set; }
        public int Rating { get; set; }
        public string Body { get; set; }
        public int RestaurantId { get; set; }
    }
}

Now I've created the class OdeToFoodDB:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace OdeToFood.Models
{
    public class OdeToFoodDB : DbContext
    {
        public DbSet<Restaurant> Restaurants { get; set; }
        public DbSet<RestaurantReview> Reviews { get; set; }
    }
}

Now I am trying to create a Microsoft SQL Server database. I went to Server Explorer -> add Connection and chose Microsoft SQL Server. According to the tutorial one of the possible database names should be OdeToFood.Models.OdeToFoodDB. But it is not available as a choice:

When I try to type OdeToFood.Models.OdeToFoodDB in it says

The database OdeToFood.Models.OdeToFoodDB does not exist or you do not have permission to see it.

I'm using Microsoft Visual Studio 2013 and Microsoft .NET framework 4.5.5

Update in response to Ivan:

I have created a _db in the HomeController like this:

namespace OdeToFood.Controllers
{
    public class HomeController : Controller
    {
        OdeToFoodDB _db = new OdeToFoodDB();

        public ActionResult Index()
        {
            var model = _db.Restaurants.ToList();

            return View(model);
        }
}

Update 2: Here is my connection strings section of web.config:

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-OdeToFood-20160318150024;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-OdeToFood-20160318150024.mdf" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

Do you create the database? It seems like you create a model. And what you do is to make database connection, it is not to create a database — Ian, Apr 01 '16 at 14:36
@Pogrindis Good suggestion, but I just tried running VS as Administrator, and I am getting the same result. — jimboweb, Apr 01 '16 at 14:40
@Ian I am still learning how to do this, but according to the tutorial on Pluralsight just by creating a class extending DBContext tells Visual Studio to create the DB. He does it the same way in the tutorial and it creates the DB. — jimboweb, Apr 01 '16 at 14:43
The link is: https://app.pluralsight.com/player?course=mvc4-building&author=scott-allen&name=mvc4-building-m4-data-i&clip=3&mode=live but you have to be a pluralsight member to see it. — jimboweb, Apr 01 '16 at 14:46
You need to run some code accessing your dbcontext, e.g. `var db = new OdeToFoodDB(); var r = db.Restaurants.ToList();`. Database will be created for you. See https://msdn.microsoft.com/en-us/data/jj193542.aspx — Ivan Stoev, Apr 01 '16 at 14:47
Thanks Ivan, I do have this code in my HomeController like this: 'namespace OdeToFood.Controllers { public class HomeController : Controller { OdeToFoodDB _db = new OdeToFoodDB(); public ActionResult Index() { var model = _db.Restaurants.ToList(); return View(model); }' — jimboweb, Apr 01 '16 at 14:54
Sorry, you can't read that in the comments. I've updated the question so you can see how I created the DB, again according to the tutorial. — jimboweb, Apr 01 '16 at 14:57
@jimboweb I look at the tutorial, it shows that you got to change the connection string. since you do not change the connection string, the database name which you look for (`OdeToFood.Models.OdeToFoodDB`) does not exist - because it is not in the connection string. But the connection string `DefaultConnection` should contain a `connectionString` with initial catalog like: `aspnet-projectName-20121010102515` which likely is the database initial catalog default name when auto-created by the project template — Ian, Apr 01 '16 at 15:02

Ian · Accepted Answer · 2016-04-01T15:26:13.853

2

In your video, it is shown that the connectionString name was initially look like:

aspnet-OdeToFood-20121010102515

And I see similar items in your dropdown list. That being said, either you have created the database without you aware of it (but not having name as you expected - because you never change your connection string) or you haven't created the database because you haven't created the constructor which makes use of the connection string:

public class OdeToFoodDB : DbContext
{
    public OdeToFoodDB : base("name=DefaultConnection")
    {

    }

    public DbSet<Restaurant> Restaurants { get; set; }
    public DbSet<RestaurantReview> Reviews { get; set; }
}

to automatically create the database.

Update:

Given your web.config, now change your:

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-OdeToFood-20160318150024;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-OdeToFood-20160318150024.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>

into:

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=OdeToFoodDB;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\OdeToFoodDB.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>

Then you should see OdeToFoodDB.

edited Apr 01 '16 at 15:26

answered Apr 01 '16 at 15:00

Ian

30,182
19
69
107

Hi Ian, thank you for the suggestion. I did try adding the constructor as you suggested, but the database is still not available. Perhaps I did not change the connection string as you stated. How do I change the connection string? – jimboweb Apr 01 '16 at 15:08
open your .config file (likely the name is Web.config) in your project folder. Then look for ``. Look at one of its ` – Ian Apr 01 '16 at 15:10
Alternatively, the easiest would be to create database from SQL server and name it as you want... I would got this way actually. Edit: it should look like: ` – Ian Apr 01 '16 at 15:13
I've updated the question so you can see the connectionstrings section of the Web.config file. I guess I should just go ahead and create the database myself and build the tables myself; it's not that many fields. But I'd still like to know why VS isn't doing what it's supposed to. – jimboweb Apr 01 '16 at 15:23
@jimboweb I updated my answer, also, please create the constructor using the connection string name: `public OleToFoodDB : base("name=DefaultConnection")` – Ian Apr 01 '16 at 15:25
Thanks so much for your help, Ian. That does allow me to see the connection. It still doesn't create the tables, but again I guess I can do that by hand. I'm marking this as an answer not because it exactly answers the question but because you gave me so much useful information, which helps me understand the SQL db setup better. – jimboweb Apr 01 '16 at 15:30
@jimboweb hmm.. that's strange... I am not really sure why it is so.. (side note: actually if you find my answer helpful but not fully addressing your concerns, it would be more suitable to give upvote than answer accepted since it may give wrong signal to the subsequent visitors... But well, that is only if the question (among millions others) will be visited by some others in the future too...) probably you want to figure out the issue more and post some updates in the future... glad that my answer can be of little help to you! – Ian Apr 01 '16 at 15:35
Hmmm...okay, it just started working, not sure why. Anyway, I gave you credit for 'solving' the problem. Hopefully it won't happen again. – jimboweb Apr 02 '16 at 02:43

score 1 · Answer 2 · answered Apr 01 '16 at 15:11

1

The database doesn't exist on the server. Try to create a database named OdeToFood.Models.OdeToFoodDB from sql server management. than connect to it.

answered Apr 01 '16 at 15:11

MNF

687
9
13

Thanks, Ferhi, I can of course create the database myself. But it does not have the data in it, it's just an empty DB. According to the tutorial I'm using a db should be created that has the two tables based on the classes I created – jimboweb Apr 01 '16 at 15:21

score 0 · Answer 3 · answered Jul 18 '17 at 06:12

The reason that the database wasn't show up for me was because (LocalDb)v11.0 doesn't seem to be supported with sql server.

To check if your model loads:

If you got the following error, then this other SO post will be helpful to you!

The database cannot be opened because it is version 782. This server supports version 706 and earlier. A downgrade path is not supported

.Net Visual Studio create SQL Server can't see DBContext class

3 Answers3