SqlException: Invalid object name 'Products'-CodePudding

I am trying to create a database-first approach ASP.NET Core 6 MVC web app.

I decided to use Microsoft's AdventureWorks sample database for this.

In short, I am trying to get some information from a table called Production.Product.

Here is the code:

Product Class:

 public class Product
 {
    public int ProductID { get; set; }
    public string Name { get; set; }
    public string ProductNumber { get; set; }
    // More properties.
 }

Context:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Product> Products { get; set; }
}

I add the AppDbContext in the Program class as every developer would, nothing special.

For testing purposes I use the HomeController to get the data.

public class HomeController : Controller
{
    private readonly AppDbContext _context;

    public HomeController(AppDbContext context)
    {
        _context = context;
    }

    // I have the view created.
    public IActionResult GetProducts()
    {
        var model = _context.Products.ToList();
        return View(model);
    }
}

And when I go to the GetProducts view, I am greeted with this error:

An unhandled exception occurred while processing the request.

SqlException: Invalid object name 'Products'. Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action wrapCloseInAction)

I doubled check the connection string. Tried renaming the Product class to Production_Product.

CodePudding user response：

In short, I am trying to get some information from a table called Production.Product.And when I go to the GetProducts view, I am greeted with this error:"SqlException: Invalid object name 'Products'". I doubled check the connection string. Tried renaming the Product class to Production_Product.

Well, based on your description, I have successfully reproduce your issue. As you can see below:

Why the error for:

Generally the exception telling us, AppDbContext property Products doesn't matched with the AdventureWorks database schema or table name. Let have a look on the blow screenshot:

As you can see in the database schema table name consturction followed format Production.Product. Thus, while your entity executing query its searching by DbSet<Product> Products but Product obviously doesn't exists or matched as a result we ended with with the error.

How to resolve:

We can solve the error following couple of ways. Here I am completely agreed with

This would also resolve your issue accordingly.

Output:

Note: The takeaways are, your database table name should be matched with your entity model which defined in AppDbContext. On top of that, you could resolve the issue following either way has been described above.