Complex relationship between entities with Entity Framework

Question

Maybe it is a duplicate but I couldn't find any topic like this.

I am using Entity Framework and have two tables in my database:

public class A
{
    public virtual B B1 { get; set; }
    public virtual B B2 { get; set; }
}

public class B
{
    public virtual A A1 { get; set; }
}

And there is no relation between B1 and A1 or B2 and A1. It is like 3 one way relation. How can you do that in Entity Framework?

I am getting this error:

An error occurred while saving entities that do not expose foreign key properties for their relationships

Does anyone know how to handle this ?

Thanks in advance

So in traditional SQL terms (which EF is for) it's a many-to-many relationship between B1 and B2 with A1 being the join table? So you will need the keys for B1, B2 and A1 should have those keys within it. — cjb110
– cjb110, Commented Jun 26, 2019 at 14:27
Each navigation property needs a corresponding column or columns that make up the PK of the table being referenced. — juharr
– juharr, Commented Jun 26, 2019 at 14:29
Maybe I wasn't clear, but A1, B1 and B2 are not related. If I set B1, it shouldn't try to set A1 in the database but I think that's what Entity Framework tries to do. — lema
– lema, Commented Jun 26, 2019 at 14:34
This also feels like an XY Problem. What data are you modeling that results in this particular set of relationships? — juharr
– juharr, Commented Jun 26, 2019 at 14:47
Yeah so EF expects Navigation properties in both directions. It sounds like you should have 3 A properties in B (the one it reference and the other two that reference it) and then 3 B properies in A (the two it references and the other one that references it). — juharr
– juharr, Commented Jun 26, 2019 at 14:51

Gert Arnold · Accepted Answer · 2019-06-28 21:26:50Z

Since you don't indicate in which EF version you're using let's look at the two current versions, EF 6.2.0 and EF-core 2.2.4.

With EF6, it's easy. The mapping...

modelBuilder.Entity<A>().HasRequired(a => a.B1).WithMany();
modelBuilder.Entity<A>().HasRequired(a => a.B2).WithMany().WillCascadeOnDelete(false);
modelBuilder.Entity<B>().HasRequired(b => b.A1).WithMany().WillCascadeOnDelete(false);

...produces the following database model (ignoring indexes):

CREATE TABLE [dbo].[A] (
    [ID] [int] NOT NULL IDENTITY,
    [B1_ID] [int] NOT NULL,
    [B2_ID] [int] NOT NULL,
    CONSTRAINT [PK_dbo.A] PRIMARY KEY ([ID])
)
CREATE TABLE [dbo].[B] (
    [ID] [int] NOT NULL IDENTITY,
    [A1_ID] [int] NOT NULL,
    CONSTRAINT [PK_dbo.B] PRIMARY KEY ([ID])
)

....in which the fields with _ are foreign keys, one of which can have cascaded delete.

With ef-core, it's not so straightforward, even buggy, seemingly. The first impulse is the EF6 equivalent:

modelBuilder.Entity<A>().HasOne(a => a.B1).WithMany();
modelBuilder.Entity<A>().HasOne(a => a.B2).WithMany();
modelBuilder.Entity<B>().HasOne(b => b.A1).WithMany();

But the generated model isn't what one would expect:

  CREATE TABLE [B] (
      [ID] int NOT NULL IDENTITY,
      [A1ID] int NULL,
      CONSTRAINT [PK_B] PRIMARY KEY ([ID])
  );
  CREATE TABLE [A] (
      [ID] int NOT NULL,
      [B1ID] int NULL,
      CONSTRAINT [PK_A] PRIMARY KEY ([ID]),
      CONSTRAINT [FK_A_B_B1ID] FOREIGN KEY ([B1ID]) REFERENCES [B] ([ID]) ON DELETE NO ACTION,
      CONSTRAINT [FK_A_B_ID] FOREIGN KEY ([ID]) REFERENCES [B] ([ID]) ON DELETE CASCADE
  );
  ALTER TABLE [B] ADD CONSTRAINT [FK_B_A_A1ID] FOREIGN KEY ([A1ID]) REFERENCES [A] ([ID]) ON DELETE NO ACTION;

One of the A-B associations is interpreted as 1:1. In my opinion that's a bug. The WithMany instruction shouldn't leave any room for interpretation. Two seemingly identical mappings produce quite different database relationships. That can't be right.

That said, it's easy (but shouldn't be necessary) to get EF on the right track by naming the FK columns:

modelBuilder.Entity<A>().HasOne(a => a.B1).WithMany().HasForeignKey("B1_ID")
    .IsRequired();
modelBuilder.Entity<A>().HasOne(a => a.B2).WithMany().HasForeignKey("B2_ID")
    .IsRequired().OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<B>().HasOne(b => b.A1).WithMany().HasForeignKey("A1_ID")
    .IsRequired().OnDelete(DeleteBehavior.Restrict);

Producing (ignoring indexes):

  CREATE TABLE [B] (
      [ID] int NOT NULL IDENTITY,
      [A1_ID] int NOT NULL,
      CONSTRAINT [PK_B] PRIMARY KEY ([ID])
  );
  CREATE TABLE [A] (
      [ID] int NOT NULL IDENTITY,
      [B1_ID] int NOT NULL,
      [B2_ID] int NOT NULL,
      CONSTRAINT [PK_A] PRIMARY KEY ([ID]),
      CONSTRAINT [FK_A_B_B1_ID] FOREIGN KEY ([B1_ID]) REFERENCES [B] ([ID]) ON DELETE CASCADE,
      CONSTRAINT [FK_A_B_B2_ID] FOREIGN KEY ([B2_ID]) REFERENCES [B] ([ID]) ON DELETE NO ACTION
  );
  ALTER TABLE [B] ADD CONSTRAINT [FK_B_A_A1_ID] FOREIGN KEY ([A1_ID]) REFERENCES [A] ([ID]) ON DELETE NO ACTION;

Note that the foreign key fields must be set required explicitly (if they are). Well, that's just an implementation detail.

Steve Py · Accepted Answer · 2019-06-26 23:46:03Z

If the A1 table contains the B1_Id and the B2_Id pointing at the same B table, but you expect a B record to only ever be associated to an A once, then as far as I know about how mapping goes, that is not possible. There would be nothing stopping you from associating the same B record as B1 or B2 on different A records, so how would B's A reference ever resolve legally? Entities reflect data state, so if it's legal/illegal from a data schema, it's the same for the entity. Having B Ids on A forms a many to 1, or can form a 1 to one, but to share 2x FKs to B on A, you would need the DB to support alternate FKs for B, which it doesn't.

You can have A hold the IDs for 2 records in B, but B cannot map a single reference back to A, it has to fake it.

public class A
{
   public int AId { get; set; }

   public virtual B B1 { get; set; }
   public virtual B B2 { get; set; }
}

public class B
{
   public int BId { get; set; }

   public virtual ICollection<A> RawA1 { get; private set; } = new List<A>();
   public virtual ICollection<A> RawA2 { get; private set; } = new List<A>();

   [NotMapped]
   public A A
   {
      get { return RawA1.SingleOrDefault() ?? RawA2.SingleOrDefault(); }
   }
}

The caveat being you can't use B.A in any Linq expression going to EF because as far as EF is concerned, it doesn't know about that property.

The alternative is using a 1-to-many where the AId lives on B, and then dealing with restricting the allowable operations against the collection on the A side. The issue there would be that the B1 & B2 order would not be reliable unless explicitly determinable by properties on the B record. A can expose a B1 and B2 unmapped property from the collection, but would have to determine which of the two elements to consider for each, or simply expose the collection. Ultimately it is your business logic that would have to control the fact that an A should only ever have 2 B references since a database cannot enforce that, nor a 2 to 1 relationship where B could get back to A.

Collectives™ on Stack Overflow

Complex relationship between entities with Entity Framework

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related