EF “Code-First” Table Mappings for Existing Database

If you are using the MvcScaffolding package to scaffold the tables from an existing database and it throws an exception on the following line of the index.cshtml page. (The exception would show up in a different location on other data access pages).

@foreach (var item in Model) {

If the exception is of type System.Data.EntityCommandExecutionException where the inner exception is something to the effect of “Invalid object name ‘dbo.Ranks” where the actual database table name is the singular form like dbo.Rank, you probably need to explicitly define the table schema mappings in the OnModelCreating event of the context class because the existing database table names don’t match the default Entity Framework Convention over Configuration settings.

namespace LPM.Models

{

    public class LPMContext : DbContext

    {

        public DbSet<LPM.Status> Status { get; set; }

        public DbSet<LPM.Rank> Ranks { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            modelBuilder.Entity<Rank>().ToTable("Rank");

        }

    }

}

If you use SQL Profiler to trace the T-SQL statement issued by the application, you can see it was attempting to retrieve data from the [dbo].[Ranks] table which doesn’t exist. In my database it is [dbo].[Rank].

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[RankNo] AS [RankNo],
[Extent1].[RankDescription] AS [RankDescription]
FROM [dbo].[Ranks] AS [Extent1]

This stumped me initially because the first table I used as a test was the “Status” table which by convention has the same name in the singular and plural form. So no exceptions where thrown and I could list, insert, update, and delete the records. After I updated the model from the database and added the Rank table, exceptions started flying until I added the modelBuilder.Entity<Rank>().ToTable(“Rank”) line in the OnModelCreating event.

Scott Guthrie’s Entity Framework 4 “Code-First”: Custom Database Schema Mapping has additional examples with more complex mapping scenarios.

photo credit: aaron13251 / CC BY 2.0