Entity Framework 6, Invalid Column Name - Incorrect Mapping

Question

I solved this by adding in the ForeignKey("Route") data annotation like this:

[ForeignKey("Route")]
public Guid rnh_rtefk { get; set; }

this is my first attempt at using EF.

I have created a model using Code First from database in my MVC app using EF 6.0.0.0

In my database I have a table of Routes and a table of RunSheetHeader. A route can have many RunSheetHeader and a RunSheetHeader can have one Route. The Routes primary key is Routes.rte_pk and this maps to the foreign key: RunSheetHeader.rnh_rtefk.

The code generated is this:

public partial class Route
{
    public Route()
    {
        RunSheetHeaders = new HashSet<RunSheetHeader>();
    }

    [Key]
    public Guid rte_pk { get; set; }

    [Required]
    [StringLength(50)]
    public string rte_name { get; set; }

    public virtual ICollection<RunSheetHeader> RunSheetHeaders { get; set; }
}

[Table("RunSheetHeader")]
public partial class RunSheetHeader
{
    public RunSheetHeader()
    {
        RunSheetDetails = new HashSet<RunSheetDetail>();
    }

    [Key]
    public Guid rnh_pk { get; set; }

    [Column(TypeName = "date")]
    public DateTime rnh_date { get; set; }

    public Guid rnh_rtefk { get; set; }

    public virtual Route Route { get; set; }

    public virtual ICollection<RunSheetDetail> RunSheetDetails { get; set; }
}

This is from the Context class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Route>()
        .HasMany(e => e.RunSheetHeaders)
        .WithRequired(e => e.Route)
        .HasForeignKey(e => e.rnh_rtefk)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<RunSheetHeader>()
        .HasMany(e => e.RunSheetDetails)
        .WithRequired(e => e.RunSheetHeader)
        .HasForeignKey(e => e.rnd_rnhfk)
        .WillCascadeOnDelete(false);
}

The error I get is:

"Invalid column name 'Route_rte_pk'."

and the SQL shows up in SQL Profiler as:

SELECT 
    1 AS [C1], 
    [Extent1].[rnh_pk] AS [rnh_pk], 
    [Extent1].[rnh_date] AS [rnh_date], 
    [Extent1].[rnh_rtefk] AS [rnh_rtefk], 
    [Extent1].[Route_rte_pk] AS [Route_rte_pk]
    FROM [dbo].[RunSheetHeader] AS [Extent1]

From reading the other answers here regarding similar problems it seems to be a problem with mapping the correct foreign keys, but it looks to me like that has been done correctly. Can anyone spot what I am missing?

Thanks for any help

Answer 1

I found 2 possible solutions to this:

1.

[ForeignKey("Route")]
public Guid rnh_rtefk { get; set; }

or 2.

[ForeignKey("rnh_rtefk")]
public virtual Route Route { get; set; }

Both get rid of the error, but I was advised that the second option is the better one to use.

Answer 2

the following code is running just fine, are you sure your OnModelCreating is well called ?

namespace testef {
    public partial class Route {
        public Route() {
            RunSheetHeaders = new HashSet<RunSheetHeader>();
        }

        [Key]
        public Guid rte_pk { get; set; }

        [Required]
        [StringLength(50)]
        public string rte_name { get; set; }

        public virtual ICollection<RunSheetHeader> RunSheetHeaders { get; set; }
    }

    [Table("RunSheetHeader")]
    public partial class RunSheetHeader {
        public RunSheetHeader() {
            //RunSheetDetails = new HashSet<RunSheetDetail>();
        }

        [Key]
        public Guid rnh_pk { get; set; }

        [Column(TypeName = "date")]
        public DateTime rnh_date { get; set; }

        public Guid rnh_rtefk { get; set; }

        public virtual Route Route { get; set; }

        //public virtual ICollection<RunSheetDetail> RunSheetDetails { get; set; }
    }
    // ---------------

    public class TestEFContext : DbContext {
        public TestEFContext(String cs)
            : base(cs) {
            Database.SetInitializer<TestEFContext>(new DropCreateDatabaseAlways<TestEFContext>());             
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Route>()
                .HasMany(e => e.RunSheetHeaders)
                .WithRequired(e => e.Route)
                .HasForeignKey(e => e.rnh_rtefk)
                .WillCascadeOnDelete(false);

            //modelBuilder.Entity<RunSheetHeader>()
            //    .HasMany(e => e.RunSheetDetails)
            //    .WithRequired(e => e.RunSheetHeader)
            //    .HasForeignKey(e => e.rnd_rnhfk)
            //    .WillCascadeOnDelete(false);
        }

        public DbSet<Route> Routes { get; set; }

    }

    class Program {
        String cs = @"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True";
            using (TestEFContext ctx = new TestEFContext(cs)) {
                Route r = new Route {
                    rte_pk = Guid.NewGuid(),
                    rte_name = "test"
                };

                r.RunSheetHeaders.Add(new RunSheetHeader {
                    rnh_pk = Guid.NewGuid(),
                    rnh_date = DateTime.Now
                });
                ctx.Routes.Add(r);
                ctx.SaveChanges();
                Console.WriteLine(ctx.Routes.Count());    
            }

            using (TestEFContext ctx = new TestEFContext(cs)) {
                foreach (Route r in ctx.Routes) {
                    Console.WriteLine(r.rte_name);
                    foreach (RunSheetHeader rsh in r.RunSheetHeaders) {
                        Console.WriteLine("    {0}", rsh.rnh_date);
                    }
                }
            }
        }
    }
}