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?

score 66 · Answer 1 · answered Nov 14 '12 at 22:28

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; }
}

Thanks for this! The documentation and some other SO answers suggests that the foreign key attribute can be used where the inverse property really should be. — Carl, Nov 04 '13 at 13:37
@Carl, and those suggestions you found are not the correct way. The FK attribute goes above the column, not the inverse property. — vapcguy, Aug 05 '14 at 23:47
@RichardPierre Thanks for reply. However, I cannot reference another column except from Id column with InverseProperty. What about this problem? What is wrong with it? http://stackoverflow.com/questions/29327772/how-to-define-matching-column-in-inverseproperty?noredirect=1#comment46851150_29327772 — Jack, Mar 29 '15 at 19:12
> [ForeignKey("WidgetSequenceId")] //Has to be a property name, not table column name -------------------------------------------------------------- this isn't even the property name it is Id-----------------------------------------------1.2 Still not the property name...!? — lanierhall, Apr 01 '15 at 20:19
This is a much better answer and should be marked as such imo as OP is already using data annotations — ataraxia, Aug 18 '19 at 20:53
InverseProperty worked. I ran into this when entity framework kept trying to rename a column to reflect the name of an abstract base class (which I didn't want). The column attribute didn't fix it, but specifying the inverse property as well did. — DVK, Sep 16 '19 at 18:10

score 39 · Accepted Answer · answered Jun 22 '12 at 00:44

39

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"));
}

answered Jun 22 '12 at 00:44

Eranga

32,181
5
97
96

Thanks for reply. What about this problem? Could you have a look at please? Thanks in advance... http://stackoverflow.com/questions/29333787/how-to-create-lookup-table-and-define-relationships – Jack Mar 29 '15 at 19:42

score 3 · Answer 3 · answered Nov 07 '17 at 16:17

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

Mapping a foreign key with a custom column name

3 Answers3

Linked