locked
Invalid column name 'CategoryCategoryID' with ScottGu's Existing Database Example RRS feed

  • Question

  • Hi!  I've been learning EF Code First lately and am having a problem I can't find referenced anywhere else.  For navigation properties I have established, EF is using a duplicated column name (like 'CategoryCategoryID').  I ran into this on a ASP.NET MVC project I have been working on for some time (converting it to Code First from EF 4.0).  So I took a few minutes to go back to a new project, based on ScottGu's example he posted for Code First CTP4:  http://weblogs.asp.net/scottgu/archive/2010/08/03/using-ef-code-first-with-an-existing-database.aspx

    I had assumed I was doing something wrong, so I was a little shocked to get the same result with his sample application.  The "Invalid column name 'CategoryCategoryID'" error is what I get against an existing, fresh install of the Northwind sample database (running on SQL 2008 R2 Dev edition).  I then switched to an instance of SQLEXPRESS on the same machine, which did not have the Northwind sample database.  Therefore, as expected, the sample app created the database (at least the two tables that are used in the sample app).  Lo and behold, the "CategoryID" column was created with the "CategoryCategoryID" name (in the Products table).  And the sample app of course works with that!

    Any ideas?  About the only differences I can find between ScottGu's sample and my code are that 1) I am using CTP5, not CTP4, and 2) I did my app in MVC, not WebForms.  I can't see why either of those items would matter, unless something really changed between CTP4 and CTP5.  I can post some code if anyone thinks it will help, but it is essentially ScottGu's sample app at the above Url.

    Thanks!

    Tuesday, February 22, 2011 1:27 AM

Answers

  • One way to solve this is to add a new FK property to your Product entity:

    public class Product
    {
      public int ProductID { get; set; }    
      public string ProductName { get; set; }
      public Decimal? UnitPrice { get; set; }
      public bool Discontinued { get; set; }
      public int CategoryId { get; set; }
    
      public virtual Category Category { get; set; }
    }
    

    Or you can keep your association as Independent (not recommended) and configure the FK column name through fluent API:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Product>()
            .HasRequired(p => p.Category)
            .WithMany()
            .IsIndependent()
            .Map(c => c.MapKey(p => p.CategoryID, "CategoryId"));
    }
    

    • Proposed as answer by Morteza Manavi Wednesday, February 23, 2011 3:04 PM
    • Marked as answer by Archaeopterryx Thursday, February 24, 2011 12:54 AM
    Wednesday, February 23, 2011 3:03 PM

All replies

  • One way to solve this is to add a new FK property to your Product entity:

    public class Product
    {
      public int ProductID { get; set; }    
      public string ProductName { get; set; }
      public Decimal? UnitPrice { get; set; }
      public bool Discontinued { get; set; }
      public int CategoryId { get; set; }
    
      public virtual Category Category { get; set; }
    }
    

    Or you can keep your association as Independent (not recommended) and configure the FK column name through fluent API:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Product>()
            .HasRequired(p => p.Category)
            .WithMany()
            .IsIndependent()
            .Map(c => c.MapKey(p => p.CategoryID, "CategoryId"));
    }
    

    • Proposed as answer by Morteza Manavi Wednesday, February 23, 2011 3:04 PM
    • Marked as answer by Archaeopterryx Thursday, February 24, 2011 12:54 AM
    Wednesday, February 23, 2011 3:03 PM
  • Hi,

    Just to add to Morteza's answer... yes the FK naming convention changed between CTP4 and CTP5.

    ~Rowan

    Wednesday, February 23, 2011 11:59 PM
    Moderator
  • Thank you, Rowan and Morteza.  It is good to know for certain.
    Thursday, February 24, 2011 1:07 AM
  • Thanks Rowan for the clarification, that's what I was wondering too and you just saved me from going back to CTP4 to confirm this :)

    Thanks, 

    Morteza

     

    Thursday, February 24, 2011 1:35 AM
  • I will probably run into this thread one day googling for an answer myself. so i guess i can share what i found about the convention

    For foreign key, Code First uses the following convention

     

    Code First will infer that any property named ‘<navigation property name><primary key property name>’ (i.e. CategoryCategoryId), ‘<principal class name><primary key property name>’ (i.e. CategoryCategoryId) or ‘<primary key property name>’ (i.e. CategoryId), with the same data type as the primary key, represents a foreign key for the relationship. If multiple matches are found then precedence is given in the order listed above. Foreign key detection will not be case sensitive.

     


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Thursday, February 24, 2011 8:27 AM