Mapping a foreign key with a custom column name

Question

I'm using Entity Framework 4.3 code-first with Oracle. I'm getting the following error:

System.InvalidOperationException : The ForeignKeyAttribute on property 'WidgetSequence' on type 'WidgetDistributor.WidgetEntity' is not valid. The foreign key name 'WIDGETSEQUENCE_ID' was not found on the dependent type 'WidgetDistributor.WidgetEntity'. The Name value should be a comma separated list of foreign key property names.

My entities are like this:

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [ForeignKey("WIDGETSEQUENCE_ID")]
    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }
}

My code seems correct. What have I done wrong, here?

Answer 1

There is a table called Users and it has a primary key called UserID.

There is another table called Directory, and it has a column called UserID which is defined as a foreign key to the Users table.

I'm able to use the ForeignKey annotation to map the foreign key like this:

[ForeignKey("xyzzy")]
public int? UserID { get; set; }  // This is a column in the table
public virtual User xyzzy { get; set; } // This is my instance of User

Answer 2

If you don't want to use fluent syntax, there are three other ways of implementing the reference using data annotations (Personally I prefer data annotations as they seem easier to read and are written just above the property they are affecting):

1.1) Use ForeignKey (with an associated property) - version 1

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [Column("WIDGETSEQUENCE_ID")]
    public int WidgetSequenceId { get; set; }

    [ForeignKey("WidgetSequenceId")] //Has to be a property name, not table column name
    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }
}

1.2) Use ForeignKey (with an associated property) - version 2

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [ForeignKey("Sequence")] //Has to be a property name, not table column name
    [Column("WIDGETSEQUENCE_ID")]
    public int WidgetSequenceId { get; set; }

    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }
}

2) You can also use the InversePropertyAttribute.

[Table("WIDGETENTITIES")]
public class WidgetEntity {

    [Column("WIDGETENTITY_ID")]
    public int Id { get; set; }

    [InverseProperty("WidgetEntities")]
    public WidgetSequence Sequence { get; set; }

    // and other properties that map correctly
}

[Table("WIDGETSEQUENCES")]
public class WidgetSequence { 

    [Column("WIDGETSEQUENCE_ID")]
    public int Id { get; set; }

    [Column("NUMBER")]
    public int Number { get; set; }

    public virtual List<WidgetEntity> WidgetEntities { get; set; }
}

Answer 3

ForeignKey attibute expects a property name in your class as the argument but you given the column name. Use fluent mappings.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    modelBuilder.Entity<WidgetEntity>()
     .HasRequired(w => w.Sequence)
     .WithMany()
     .Map(m => m.MapKey("WIDGETSEQUENCE_ID"));
}