Entity Framework SqlException: Invalid object name

Viewed 4092

I've migrated an old app from EF6 DB First to a context manually generated from the existing DB, and using always the EF6 on ASP.NET 4.7.

The context is defined like the following:

namespace DataModel
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;

    public partial class MyEntities : DbContext
    {
        public MyEntities()
            : base("name=MyEntities")
        {
        }

        public virtual DbSet<Country> Countries { get; set; }        
        public virtual DbSet<SomeNameSpace.Report> RE_Reports { get; set; }
    }
}

The class entity Report is defined like this:

namespace DataModel
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    namespace SomeNameSpace
    {
        public partial class Report
        {
            [Key]
            public int ReportID { get; set; }
            [StringLength(1024)]
            public string Title { get; set; }
        }
    }
}

Everything works fine at compile-time, and the app successfully builds. Once I run it at the first access to Reports entities I get the following error:

Invalid object name 'dbo.Reports'

So Entity Framework translates in the SQL query the table name to Reports, the name of the entity at plural, instead of translating to RE_Reports which is the actual name of the database table.

Why?

Please note that queries using the other entities, Country for example, are correctly working, also at runtime.

The only difference is the additional namespace I've added to the Report table.

2 Answers

Don't know why your code does not work(based on what I've read it should, or maybe current ef core conventions differ from EF6) but as a workaround you can mark the entity with TableAttibute like this:

[Table("RE_Reports")]
public partial class Report

So Entity Framework translates in the SQL query the table name to Reports, the name of the entity at plural, instead of translating to RE_Reports which is the actual name of the database table.

Why?

Because it's a Convention in Entity Framework.

Entity Framework has three options to map your entity name to table name:

  1. Using fluent method in OnModelCreating:

    modelBuilder.Entity<Report>().ToTable("RE_Reports");

  2. Using attribute (as mentioned in Guru Stron answer) on entity class:

    [Table("RE_Reports")]

  3. Using Convention:

    if non of options 1 and 2 was applied by developer, EF by default will infer table name as plural case of entity name (Your case)

Related