CODEWITHSUNDEEP
entity-framework-4.1ef-code-firstfluent-interface
chiccodoro
chiccodoro

Reputation: 14716

Map many:optional relationships from multiple entity types EF CodeFirst

We have 3 entities that relate to each other as such:

What is the best way to model this using DbModelBuilder?

If we try:

modelBuilder.Entity<Foo>().HasMany(t => t.Attachments).WithOptional()
    .Map(m => m.MapKey("FOO_ID")).WillCascadeOnDelete(true);
modelBuilder.Entity<Bar>().HasMany(t => t.Attachments).WithOptional()
    .Map(m => m.MapKey("BAR_ID")).WillCascadeOnDelete(true);

This is not a very clean data model: * The Attachments table will have two FK columns, one for each possible parent entities ('FOO_ID', 'BAR_ID'). If we increase the number of entities that need attachments, it bloats the table. * We need to make the backward reference optional although of course an attachment is always attached to an entity. It just needn't necessarily be a Foo, and it needn't be a Bar. But an attachment with all FK's set to NULL will be allowed by the schema although it is not valid from a business view. * In theory one Attachment could be linked to both a Foo and a Bar, which is not valid.

Instead we might map the relationships to junction tables 'T_FOO_ATTACHMENT' and 'T_BAR_ATTACHMENT': * T_FOO_ATTACHMENT has a required foreign key to Foo (FOO_ID) and a required FK to Attachment (ATTACHMENT_ID). * The same holds for T_BAR_ATTACHMENT

Downsides: * An attachment could still be linked to a Foo and a Bar. * An attachment could even be linked to multiple Foo's, which is not valid either. Ideally we could use the fluent syntax to add DB constraints that prevent that.

Upvotes: 2

Views: 444

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

This is not a very clean data model: The Attachments table will have two FK columns, one for each possible parent entities ('FOO_ID', 'BAR_ID').

But it is absolutely correct from relational perspective - if you want to have relation with another table you need a new foreign key.

But an attachment with all FK's set to NULL will be allowed by the schema although it is not valid from a business view. In theory one Attachment could be linked to both a Foo and a Bar, which is not valid.

That is your business logic. With your current model you must enforce it in your application.

What you are looking for is some kind of named relations where the Attachment doesn't have only FK but also the name of related table. That is something you will not achieve on relational level - it is data level (again business logic) and it cannot be mapped with EF.

Upvotes: 2

Related Questions