Databases
StackOverflow2013
Postsdbo
POHow to correctly map foreign keys in a one-to-zero-or-one relationship using Fluent API?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to correctly map foreign keys in a one-to-zero-or-one relationship using Fluent API?
    text
    copied!<p>I have the following entities:</p> <pre><code>public class Foo { public int MyId1 { get; set; } public int MyId2 { get; set; } public int MyId3 { get; set; } public Bar Bar { get; set; } } </code></pre> <p>and</p> <pre><code>public class Bar { public int MyId1 { get; set; } public int YourId2 { get; set; } public int MyId3 { get; set; } public Foo Foo { get; set; } } </code></pre> <p>and the mappings:</p> <pre><code>// Foo Mapping this.HasKey(t =&gt; new { t.MyId1, t.MyId2, t.MyId3 }); this.Property(t =&gt; t.MyId1).HasColumnName("my_id1"); this.Property(t =&gt; t.MyId2).HasColumnName("my_id2"); this.Property(t =&gt; t.MyId3).HasColumnName("my_id3"); // Bar Mapping this.HasKey(t =&gt; new { t.MyId1, t.MyId3, t.YourId2 }); // Notice different order this.Property(t =&gt; t.MyId1).HasColumnName("my_id1"); this.Property(t =&gt; t.YourId2).HasColumnName("your_id2"); this.Property(t =&gt; t.MyId3).HasColumnName("my_id3"); this.HasRequired(t =&gt; t.Foo) .WithOptional(t =&gt; t.Bar); </code></pre> <p>When I do a select on Foo, the sql query produced looks something like this:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>this.HasRequired(t =&gt; t.Foo) .WithOptional(t =&gt; t.Bar) .Map(m =&gt; { m.MapKey("my_id1", "your_id2", "my_id3"); } ); </code></pre> <p>But this gives me the following error:</p> <pre><code>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. </code></pre> <p>Any idea how to solve this problem?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload