SqlException (0x80131904): Invalid object name 'dbo.Categories'

Question

I am getting the exception above when I run an application. The application is using asp.net mvc 3 / C#. I made an mdf file and added it under App_Data folder in Visual Web Developer Express. I added connection strings to the web.config folder but when I run and browse to /store, I get the error above with the line var categories = storeDB.Categories.ToList(); highlighted. My database contains 6 tables and one of them is Category.

Controller:

EventCalendarEntities storeDB = new EventCalendarEntities();

public ActionResult Index()  
{  
    var categories = storeDB.Category.ToList();  
    return View(categories);  
}    

Connection strings in web.config file:

<connectionStrings>
   <add name="EventCalendarEntities"
        connectionString="data source=.\SQLEXPRESS;
        Integrated Security=SSPI;
        AttachDBFilename=|DataDirectory|\MvcEventCalendar.mdf;
        User Instance=true"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Answer 1

This usually means a simple configuration issue:

• perhaps there genuinely is no such table
• perhaps the table is there, but there is no dbo scheme (it might be in Fred.Categories)
• perhaps the db is case-sensitive (which is fine), and the table is actually dbo.CATEGORIES

Any of these will cause the above exception. In particular, you state:

My database contains 6 tables and one of them is Category.

Now to a machine, Category != Categories

Answer 2

Try using model builder class.It is the way to configure or explicitly define the mapping between table and model class.

In your entity/context class try adding this code

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       base.OnModelCreating(modelBuilder);
       modelBuilder.Entity<Category>().ToTable("Category");
    }

Its a method.Make sure ur using all the including statements.

Answer 3

Since this was still top search hit on the exception in April of 2018 and it led me to a solution, let me tack this on for a specific situation...

Our application is based on ABP and ABP.Zero, and we already have a pattern that fit Marc's answer. While I bet explicit mapping in the OnModelCreating method (a la Dhananjay's answer) would have worked perfectly, it seemed like ABP's mapping was working perfectly up to this point and I didn't want to break the pattern.

My solution was to add a table attribute to the entity class, and this settled EF's confusion.

using System;
using Abp.Domain.Entities;
using System.ComponentModel.DataAnnotations.Schema;

namespace Discovery.History
{

    [Table("HistoryRecords")]
    public class HistoryRecord : Entity<int>
    {
        public int ResearcherCount { get; set; }
        public DateTime DateSubmitted { get; set; }
        public string Comments { get; set; }
    }
}

Answer 4

What you really want to do to fix this is in you Context class you should have a method called OnModelCreating... make sure it has this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}

Answer 5

Proven,tested & verified for table with name category or any SQL keywords named table use ToTable to instruct specific table name

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
     modelBuilder.Entity<category>().ToTable("category");
 }

Answer 6

If you have a Class for mapping properties and keys without this.Map.(a table in db necessary to mapping), EntityFramework expect you have one table named like Category but convert in plural, so "Categories".. To resolve you can add this.Map(in correct table existing in your DB).