How to correctly map foreign keys in a one-to-zero-or-one relationship using Fluent API?

Question

I have the following entities:

public class Foo
{
  public int MyId1 { get; set; }
  public int MyId2 { get; set; }
  public int MyId3 { get; set; }
  public Bar Bar { get; set; }
}

and

public class Bar
{
  public int MyId1 { get; set; }
  public int YourId2 { get; set; }
  public int MyId3 { get; set; }
  public Foo Foo { get; set; }
}

and the mappings:

// Foo Mapping
this.HasKey(t => new { t.MyId1, t.MyId2, t.MyId3 });
this.Property(t => t.MyId1).HasColumnName("my_id1");
this.Property(t => t.MyId2).HasColumnName("my_id2");
this.Property(t => t.MyId3).HasColumnName("my_id3");

// Bar Mapping
this.HasKey(t => new { t.MyId1, t.MyId3, t.YourId2 }); // Notice different order
this.Property(t => t.MyId1).HasColumnName("my_id1");
this.Property(t => t.YourId2).HasColumnName("your_id2");
this.Property(t => t.MyId3).HasColumnName("my_id3");
this.HasRequired(t => t.Foo)
  .WithOptional(t => t.Bar);

When I do a select on Foo, the sql query produced looks something like this:

select *
from Foo foo
left outer join Bar bar
  on foo.my_id1 = bar.Foo_MyId1 
    and foo.my_id2 = bar.Foo_MyId2 
    and foo.my_id3 = bar.Foo_MyId3

Which is obviously giving me SQL errors. I'm guessing this is because it's trying to infer the foreign key columns from the relationship. So I tried specifying the actual FK column names in the mapping:

this.HasRequired(t => t.Foo)
  .WithOptional(t => t.Bar)
  .Map(m =>
    {
      m.MapKey("my_id1", "your_id2", "my_id3");
    }
  );

But this gives me the following error:

Unhandled Exception: System.Data.Entity.ModelConfiguration.ModelValidationException:
One or more validation errors were detected during model generation:

my_id1: Name: Each property name in a type must be unique. Property name 'my_id1' is already defined.
your_id2: Name: Each property name in a type must be unique. Property name 'your_id2' is already defined.
my_id3: Name: Each property name in a type must be unique. Property name 'my_id3' is already defined.

Any idea how to solve this problem?

Moho · Answer 1 · 2013-10-17T04:47:57.413

1

.Map(...) is used to specify the name of a foreign key when you don't define the foreign key in your POCO. In your case, you define properties in your POCOs that represent the FK so you get this duplicate name error.

I haven't been able to identify how to do what you're asking (which is to specify the FK fields using fluent api for a one to one relationship) but it can be done using data annotations using the [ForeignKey( "NavPropertyName"), Column(Order = #)] attributes

edited Oct 17 '13 at 04:47

answered Oct 16 '13 at 22:38

Moho

15,457
1
30
31

Thanks for your answer, but in this case, m doesn't have MapLeftKey nor MapRightKey, only MapKey and ToTable. I'm using EF 6.0-rc. I think those are only available when used after WithMany. Any other idea? – Yuyo Oct 17 '13 at 03:35
Interesting. Using [ForeignKey("my_id1, your_id2, my_id3")] in Bar's Foo property seems to have done the trick. Now, there surely must be a way to do this using the Fluent API! – Yuyo Oct 17 '13 at 14:13
OK, I found something else that's interesting. So I'm working with a legacy database and if you notice in the code above, Bar's PK is in a different order than Foo's PK. If I try this with tables that have the same order PKs, HasRequired(...).WithOptional(...) works without the need to use the ForeignKey attribute. – Yuyo Oct 17 '13 at 14:31
It can be done - see the answer here: http://stackoverflow.com/questions/18719890/one-to-zero-or-one-with-hasforeignkey – hillstuk Apr 10 '15 at 15:09

How to correctly map foreign keys in a one-to-zero-or-one relationship using Fluent API?

1 Answers1