Entity Framework optional:optional relationship

Question

I am trying to set up an Entity Framework model with an optional:optional relationship:

In my situation, sometimes an AdditionalData record exists that points to a BaseTable record, but sometimes BaseTable or AdditionalData records exist without any linkage. The foreign key to the BaseTable (if it exists) is on the AdditionalData table.

I want to be able to navigate back and forth between BaseTable and any AdditionalDatas that might be connected.

BaseTable  0..1 ----- 0..1 AdditionalData1
                 \
                  --- 0..1 AdditionalData2

public class BaseTable {
    public int Id { get; set; }
    public virtual AdditionalType1 AdditionalType1 { get; set; }
    public virtual AdditionalType2 AdditionalType2 { get; set; }
}                  

public class AdditionalType1 {
    public int Id { get; set; }
    public int? BaseTableId { get; set; }
    public virtual BaseTable BaseTable { get; set; }
}

public class AdditionalType2 {
    public int Id { get; set; }
    public int? BaseTableId { get; set; }
    public virtual BaseTable BaseTable { get; set; }
}

How do I make this work? I got as far as:

modelBuilder.Entity<AdditionalType1>()
    .HasOptional(zmt => zmt.BaseTable)
    .WithOptionalDependent(zm => zm.AdditionalType1)
    .Map(a => a.MapKey("BaseTableId"));
modelBuilder.Entity<AdditionalType2>()
    .HasOptional(zmt => zmt.BaseTable)
    .WithOptionalDependent(zm => zm.AdditionalType2)
    .Map(a => a.MapKey("BaseTableId"));

but it tells me this:

error: (1564,6) : error 0019: Each property name in a type must be unique. Property name 'BaseTableId' was already defined.

I don't know exactly what that refers to, and not sure how to fix.

EDIT: If I remove the Map/MapKey clauses as suggested here (https://stackoverflow.com/a/8016308/237091) I get this error instead, when a query runs that uses it:

Invalid column name 'BaseTable_Id' as it maps itself to BaseTable_Id automatically instead of my BaseTableId field.

'Sometimes a BaseTable exists' Either you want to make rows in the base table for users or you have a very bad understanding of how databases work. It's not like Schrodinger's cat, whether your database has a Table has to be determined at design time. — MrFox, Jun 27 '13 at 14:49
@Mr Fox: Edited for clarification -- I was talking about records, not the actual existence of the table. — Scott Stafford, Jun 27 '13 at 14:55

score 5 · Accepted Answer · edited May 23 '17 at 12:34

5

It looks like you're trying to set up a 1:0..1 relationship from your AdditionalType objects (which I may have misinterpreted completely.

N.B. I think you would have to hold a BaseTableId on your BaseTable for this to work (or define a primary key):

If BaseTableId is the foreign key in this instance, I think this may be what you're after:

modelBuilder.Entity<AdditionalType1>()
    .HasOptional(zmt => zmt.BaseTable)
    .WithMany()
    .HasForeignKey(a => a.BaseTableId);

Which is what I've used previously, but can't admit to understanding it fully (the .WithMany() trips me up); it's a slight workaround listed in article from this answer: Entity Framework 0..1 to 0 relation)

Apologies if I've missed the point entirely.

edited May 23 '17 at 12:34

Community

1
1

answered Jun 27 '13 at 15:06

Chris

8,268
3
33
46

1

yes use this approach. this is 1:Many BUT you arent forced to put many entries in ;-) 1:1 declaration would require base table and Additional to have the same primary key. And one to marked as Primary. So will need to add a custom check or add a unique index to guarantee 1:1 – phil soady Jun 27 '13 at 15:09
Sorry, just added Id prooperty. I intended there to be a PK Id field on all of them, but not a Shared Primary Key for legacy reasons. – Scott Stafford Jun 27 '13 at 15:16
I'm a little sad that I have to pretend it's one-to-many to trick EF into working, but it does work. Thanks! – Scott Stafford Jun 27 '13 at 19:57
It does feel a little strange, but I think (and seems to be mentioned) that it might be something addressed in later versions of EF. Ta! – Chris Jun 27 '13 at 20:24

Entity Framework optional:optional relationship

1 Answers1

Linked