EF6.1.1 Code First One-to-Zero/One Relationship

Question

I am trying to define these tables.

_tbl1_
id      (PK)

_tbl2_
id      (PK)
tbl1_id (FK)

_tbl3_
id      (PK)
tbl1_id (FK)

...where I can navigate from Tbl1 to Tbl2 or Tbl3

or back from Tbl2 or Tbl3 to Tbl1.

While the association from Tbl2 or Tbl3 to Tbl1 are required, a record in Tbl2 or Tbl3 may not exist; so Tbl1 records should be able to exist with our both or one of Tbl2 and Tbl3.

The design is this way to have optional relationships to two completely different entities from the perspective of Tbl1.

class Tbl1
{
    [Key]
    public int Id { get; set; }
    public Tbl2 Tbl2 { get; set; }
    public Tbl2 Tbl3 { get; set; }
}

class Tbl2
{
    [Key]
    public int Id { get; set; }
    [ForeignKey("Tbl1")]
    public int Tbl1Id { get; set; }
    public Tbl1 Tbl1 { get; set; }
}

class Tbl3
{
    [Key]
    public int Id { get; set; }
    [ForeignKey("Tbl1")]
    public int Tbl1Id { get; set; }
    public Tbl1 Tbl1 { get; set; }
}

and fluent...

modelBuilder.Entity<Tbl2>()
    .HasRequired(e => e.Tbl1)
    .WithOptional(e => e.Tbl2);

modelBuilder.Entity<Tbl3>()
    .HasRequired(e => e.Tbl1)
    .WithOptional(e => e.Tbl3);

Using the above I get an error of:

Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be '*'.

Which seems to imply that the foreign key must be the record key as well.

So, can I do this? Can I not?.

I am happy to implement with either attributes or fluent api or both.

Have a look at [this](http://stackoverflow.com/questions/14701378/implementing-zero-or-one-to-zero-or-one-relationship-in-ef-code-first-by-fluent) question. Maybe it would help. Would the `.WithOptionalPrincipal(...)' clause help? — Beartums, Jan 21 '15 at 06:47
Yes I saw that one earlier. That forces the tables to have int Ids to each other instead of just relying on the FK relationship. It feels 'yuk' so I want to avoid it. — Matt, Jan 21 '15 at 06:51

score 2 · Answer 1 · answered Jan 21 '15 at 08:21

2

I think your ForeignKey attribute needs to refer to a column and be placed above a navigation property. e.g.:

[ForeignKey("Tbl1Id")]
public virtual Tbl1 tbl1 {get; set;}

Even if you define a navigation property on Tbl1 to Tbl2 and Tbl3, the foreign key annotation should be defined in the dependent classes (tbl2 and tbl3) not the principal class.

I'm not sure if this will solve the overall problem, but maybe it will help.

Good luck

answered Jan 21 '15 at 08:21

Beartums

1,340
1
10
15

1

The FK-attribute *can* be placed above the navigation property and reference the id-property, **OR**, it *can* be placed above the id-property referencing the navigation property. – Allmighty Jan 21 '15 at 08:30

score 2 · Accepted Answer · edited May 23 '17 at 11:54

Just as in other questions of a similar vein; One to One or Zero can be accomplished by using the same property for the entity key and the foreign key to the principal entity on the dependent entity.

I don't see another way, and since creating a one to one or zero in SQL would require the foreign key to have a uniqueness constraint, the end result is pretty much the same.

EF6.1.1 Code First One-to-Zero/One Relationship

2 Answers2