How to work with Navigation Property and non-standard keys?

Question

Assume I have following database tables with 1:1 mapping

Table dbo.Foo with PrimaryKey FooRowId
Table dbo.Bar with PrimaryKey BarRowId

No foreign keys exist on either table.

Using EF, I defined models as follows.

Modeling Foo table

public class Foo
{
    [Key]
    public long FooRowId { get; set; }

    // Navigation
    public virtual Bar Bar { get; set; }
}

Modeling Bar table

public class Bar
{
    [Key]
    public long BarRowId { get; set; }

    // Navigation
    public virtual Foo Foo { get; set; }
}

This gives me navigation property related error as follows.

Unable to determine the principal end of an association between the types 'MyOrg.Models.Foo' and 'MyOrg.Models.Bar'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

One way to fix is by standardizing property names as follows.

On Foo

public class Foo
{
    [Key]
    [Column("FooRowId")]
    public long FooId { get; set; }

    // Navigation
    public virtual Bar Bar { get; set; }
}

On Bar

public class Bar
{
    [Key]
    [Column("BarRowId")]
    public long BarId { get; set; }

    // Navigation
    public virtual Foo Foo { get; set; }
}

However, requirement states I must keep the original properties FooRowID and BarRowId. Given this constraint, how to make the navigation properties work?

Answer 1

Although Attributes seem easy to use, it limits reuse of your classes in other databases.

Suppose you have created a class BillingAddress according to the standards of your company (country maybe). You want to use this class in two different DbContexts, each to represent their own database. The BillingAddress in database 1 has a primary key in column "MyPrimaryKey", the BillingAddress in database 2 has a primary key in column "Id". You can't solve that using attributes.

The names of columns and tables, the relation between tables is part of the database. Therefore this ought to be described in the DbContext. If you don't use Attributes in the DbSet classes, you'll be able to use the same class in different databases.

So let's write your table design in fluent Api

See:

• Fluent API to configure tables, like table name
• Fluent API to configure properties, like primary key, column names, foreign keys
• Fluent API to configure relations between tables, like one-to-many

Below I give examples for all three categories:

• Configure the table name and the primary key of the table
• Configure one of the properties: column name
• Configure relation between tables, for example one-to-many

Keep in mind: if you use code first, and you stick to the entity framework code first conventions, none of this is needed. As long as you stick to these conventions, Entity Framework will be very capable in detecting primary keys, foreign keys, relations between tables.

But if your tables are different, the following fluent API is needed in your DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     // Configure entity table FOO:
     var entityFoo = modelBuilder.Entity<Foo>();

     // example: configure Name and primary key of foo table
     entityFoo.ToTable("MySpecialFooTable");
     entifyFoo.HasKey(foo => foo.FooRowId);

     // example: configure property Name is in column FooName:
     entityFoo.Property(foo => foo.Name).HasColumnName("MyFooName");

     // example: one-to-many relation between foo and bar
     // every Foo has zero or more Bars in property MyBars,
     // every Bar belongs to exactly one For in property MyFoo
     // using foreign key MyFooId:
     entityFoo.HasMany(foo => foo.MyBars)         // Foo has zero or more Bars in MyBars
              .WithRequired(bar => bar.MyFoo)     // every Bar belongs to one Foo
              .HasForeignKey(bar => bar.MyFooId); // using foreign key MyFooId
}