EF6 One-To-One relationship with separate ID property

Question

We need to define a One-To-One relationship between following entities:

public class Foo
{
    [Key, Column("Foo_ID")]
    public int Id { get; set; }

    public Bar Bar { get; set; }
}

public class Bar
{
    [Key, Column("Bar_ID")]
    public int Id { get; set; }

    [Column("Bar_Foo_ID")]
    public int? FooId { get; set; }

    public Foo Foo { get; set; }   
}

We need to migrate a legacy software and have both of them running side by side for now. So we can't change any relations. Unfortunately there are no foreign keys defined in the database.

modelBuilder.Entity<Foo>()
            .HasOptional(a => a.Bar)
            .WithRequired(x => x.Foo)
            .WillCascadeOnDelete(true);

When we query Foo and Include(x => x.Bar) it creates a SQL query with LEFT OUTER JOIN bars ON Foo_ID = Bar_ID which is wrong.

We need to change this to a LEFT OUTER JOIN bars on Foo_ID = Bar_Foo_ID, but I'm not sure how Entity Framework supports that as we don't have foreign keys in our DB and Bar_ID as PrimaryKey.

I know that there can be potentially more than one Bars for one Foo, but is there a way to enforce a One-To-One relationship?

When a Foo is created, a Bar is always created for it.

one-to-one relationship (even any relationship) WITHOUT foreign key?! I do not know whether it is possible to do this. But you can read this, may be it will help http://www.entityframeworktutorial.net/code-first/configure-one-to-one-relationship-in-code-first.aspx And another thing, if `Bar` **is always created for** `Foo`, why you want `LEFT JOIN`?! — Hemid Abbasov, Jan 18 '18 at 08:38
Have been there already, but this doesn't work unfortunately. I got the ```LEFT OUTER JOIN``` from the SQL Profiler. The query was generated by LINQ2Entities. — Stefan Schmid, Jan 18 '18 at 08:44
If you want exact One-To-One relationship (not One-To-Zero_or_one) read this https://stackoverflow.com/questions/10292355/how-do-i-create-a-real-one-to-one-relationship-in-sql-server — Hemid Abbasov, Jan 18 '18 at 08:47
OK I was wrong, what I want to achieve is a One-To-Zero-Or-One relationship which is "de facto" never Zero as our code ensures that a record is created upon creation of the parent entity. — Stefan Schmid, Jan 18 '18 at 08:50
@grek40 seems to have a reasonable workaround, although if that's not suitable you could always consider *not* mapping the navigation property and using `join` or `Join` to manually join the record in yourself. Beyond that I have a hazy recollection of `HasOne` being useful here. — ta.speot.is, Jan 18 '18 at 11:13

score 4 · Accepted Answer · answered Jan 18 '18 at 11:07

For legacy data, you can use the following:

1 : 0-1 relationships are basically a special form of 1 : many relationships.

So you can configure the relation as 1:many in entity framework and potentially add a unique index to the foreign key property to enforce your constraint of max. one related entry.

public class Foo
{
    [Key, Column("Foo_ID")]
    public int Id { get; set; }

    // this can't be a single reference property unfortunately... but it will only contain 0 or 1 object when working with it.
    public ICollection<Bar> Bars { get; set; }
}

public class Bar
{
    [Key, Column("Bar_ID")]
    public int Id { get; set; }

    [Index(IsUnique = true)]
    [Column("Bar_Foo_ID")]
    public int? FooId { get; set; }

    public Foo Foo { get; set; }   
}

modelBuilder.Entity<Foo>()
            .HasMany(a => a.Bars)
            .WithRequired(x => x.Foo)
            .HasForeignKey(x => x.FooId)
            .WillCascadeOnDelete(true);

score 1 · Answer 2 · answered Jan 18 '18 at 09:07

1

The way I understand it, you have an existing column called Bar_Foo_ID in Bar table which has to be used as FK to the PK Bar_ID column in Bar table.

It's possible, but EF6 has limited support for such relationship, in particular does not support explicit FK property. So you have to remove the FooId property from the model:

public class Bar
{
    [Key, Column("Bar_ID")]
    public int Id { get; set; }

    public Foo Foo { get; set; }   
}

and use the following fluent configuration:

modelBuilder.Entity<Foo>()
    .HasOptional(a => a.Bar)
    .WithOptionalPrincipal(x => x.Foo)
    .Map(m => m.MapKey("Bar_Foo_ID"))
    .WillCascadeOnDelete(true);

MapKey is to specify the FK column name. WithOptionalPrincipal is to specify that it's nullable (while WithRequired would be for non nullable).

Note that EF6 treats cascade delete differently for optional relationships. Instead of deleting the associated records, it disassociates them by setting the FK to null. In case you want to actually delete the associated Bar when deleting the Foo, you have to do that manually with code.

answered Jan 18 '18 at 09:07

Ivan Stoev

195,425
15
312
343

`Bar_Foo_ID` is FK to the PK `Foo_ID` of table `Foo`, but without FK defined in database. Let's say, it is "not existing" FK – Hemid Abbasov Jan 18 '18 at 09:18
It doesn't really matter in case you are mapping to existing database. It just provides the necessary information to EF, for instance, how to join tables, which was the question. With this setup, you should get the desired `LEFT JOIN`. – Ivan Stoev Jan 18 '18 at 09:29
Thanks for your answer, but how do I create a ```Bar``` entity after removing ```FooId``` from it? When I create a ```Foo``` entity, I always create a ```Bar``` entity, setting its ```FooId``` with the ID of the previously created ```Foo``` entity. – Stefan Schmid Jan 18 '18 at 09:37
You will set `bar.Foo` property instead. Or `foo.Bar` if you wish. In other words, use *navigation properties*. There is no other way, because of the EF6 limitation mentioned in the answer. – Ivan Stoev Jan 18 '18 at 10:25

EF6 One-To-One relationship with separate ID property

2 Answers2

Linked