Question : How can we resolve multi-inheritance correctly ?

[Angelinsky7]

Hi,
I would like to understand with the ef core point of view the correct way of mapping a database with multi-inheritance.
I already know that simple inheritance is not totally complete (we only have 'Mapping the Table-Per-Hierarchy (TPH) Inheritance' and missing 'Mapping the Table-Per-Type (TPT) Inheritance' and 'Mapping the Table-Per-Concrete Class (TPC) Inheritance')

Imagine that i have this database structure (and that the question is not about changing it - the way we build ours databases is not going to change !!!) :

         Table E
            |
          Table D
       /          \
    Table B    Table C
       \          /
         Table A (parent)

Each tables share an unique ID : id_table_pk
TableA.id_table_pk is created from a sequence
TableB-E.id_table_pk is set by an insert and have a foreignkey on is parent... (simple database inheritance : Mapping the Table-Per-Type (TPT) Inheritance)

If we take aside the fact that Mapping the Table-Per-Type (TPT) Inheritance is not in the box now (but in a near future)

1. How could i achieve to correctly create the TableD inheritance ? (I know that c# cannot permit a multi inheritance like almost every other language but almost every database has this feature and it seems to me a shame not to use it, no ?)

2. In the meantime, how can i have a workaround to TPT ? I was thinking of not telling ef that there were a relation between each entity and calling myself all the logic to create each parent by "hand". But i discovered that ef does not care about the order of the call that i write but uses is own sort order to make the query... and of course this behaviour leads to constraints violation because most of the time, ef chooses to insert/update/delete in the "wrong" order !! Could it be possible to have a special mode to tell ef "I'm a big boy i know what i'am doing let me choose the order of the query" instead of call SaveChanges() between every operations ?

Something maybe like that :

await _context.Database.BeginCustomTopologicalOrderAsync(new MyTopologicalOrder())

public void DoSomethingClever(String[] someParams){
  using (IDbContextTransaction scope = await _context.Database.BeginTransactionAsync()) {
     using (var custom = await _context.Database.BeginCustomTopologicalOrderAsync(new MyTopologicalOrder())) {
       _context.TableA.Add(newEntityA);
       _context.TableB.Add(newEntityB);
       _context.TableC.Add(newEntityC);
       _context.TableD.Add(newEntityD);
       _context.TableE.Add(newEntityE);

      await _context.SaveChangesAsync();
      scope.Commit();
   }
  }
}

I know is a long question (not only one) but i really think that it's part of something important and that those kind of functionality could benefit everyone by letting us creating and using better database structure.

Thanks again for you amazing works

[ajcvickers]

@Angelinsky7 Can you post what you would like the domain classes to look like?

[Angelinsky7]

hihih... (i know)

1. I really don't know...
2. We maybe could have this :

public class TableA {
        public Int64 Id { get; set; }
        public String PropA1 { get; set; }
        public String PropA2 { get; set; }
    }

    public class TableB {
        public Int64 Id { get; set; }
        public String PropB { get; set; }
    }

    public class TableC {
        public Int64 Id { get; set; }
        public String PropC { get; set; }
    }

    public class TableD {
        public Int64 Id { get; set; }
        public String PropD { get; set; }
    }

    public class TableE {
        public Int64 Id { get; set; }
        public String PropE { get; set; }
    }

    public class View {
        public Int64 Id { get; set; }

        public String PropA1 { get; set; }
        public String PropA2 { get; set; }
        public String PropB { get; set; }
        public String PropC { get; set; }
        public String PropD { get; set; }
        public String PropE { get; set; }
    }

    public class ModelContext : DbContext {
        //.... constructor, etc...

        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<TableA>(entity => {
                entity.HasKey(p => p.Id);
                entity.Property(p => p.Id).HasColumnName("").ValueGeneratedOnAdd().IsRequired(true);
            });

            modelBuilder.Entity<TableB>(entity => {
                entity.HasKey(p => p.Id);
                //HasForeignKey does not exist and should have some properties to link to another table and another(s) indexed property(ies)
                entity.Property(p => p.Id).HasColumnName("").ValueGeneratedNever()
                    .HasForeignKey<TableA>(p => p.Id)
                    .IsRequired(true);
            });
            //Same for TableC
            modelBuilder.Entity<TableD>(entity => {
                entity.HasKey(p => p.Id);
                //Here's we could specify two foreignkey on the same TableD property
                entity.Property(p => p.Id).HasColumnName("").ValueGeneratedNever()
                    .HasForeignKey<TableB>(p => p.Id)
                    .HasForeignKey<TableC>(p => p.Id)
                    .IsRequired(true);
            });
            //Same for TableE

            //Then we could create an Entity 'View' that uses all those table with a join / left join
            /*
             SELECT
                .... some properties
             FROM TableE
                JOIN TableA
                JOIN TableB
                JOIN TableC
                JOIN TableD
                JOIN TableE
            */

            //It could be a view directly into the database or it could be a domain class....

            modelBuilder.Entity<View>(entity => {
                entity.IsReadOnly(true); //maybe ??? I dont't really know how you have decided to handle the 'view isssue'
                entity.HasKey(p => p.Id);
                //We specify a "shadow" property that know how to add the join and that know how to use each field of this join...
                entity.HasShadowOne<TableA>() //or something like that
                    .WithOne()
                    .IsRequired(true)
                    .HasForeignKey(p => p.Id)
                    .HasPrincipalKey(p => p.Id)
                    .HasConstraintName("FK_ACTIVITE_AUDIT")
                    .OnDelete(DeleteBehavior.Restrict)
                    .Property(p => p.PropA1)
                    .Property(p => p.PropA2);
                //The same for every table
            });
        }

I know it's not something clever now, it's just to try to explain how i have solved this by hand... (without using an ORM)...
We actually have some databases built like that :

A lot of small tables
A lot of views to join the table and create the real entities
A lot of packages (oracle) to mimic the INSERT/UPDATE/DELETE of the view and handle this kind of issue

But we are trying to escape oracle and the package/store procedure complexity by using 'ef core' and the first idea was, keeping our tables structures (because we really think that it's good database design), replacing the views by using ef core sql generation and replacing the stored procedure by using call to the ef core api...

Sadly for know, doing it, i need to deactivate/not used almost all the good stuff of ef core...
Thanks again for taking the time to read all of that !!!

[ajcvickers]

@Angelinsky7 There should be no issue mapping those tables and relationships as-is using composition and navigation properties in the normal way. Creating a flat "View" entity on top of that is something that could be done at the application layer, but I think it would need entity splitting (#620) to be implemented before it could be mapped directly to the tables, and in that case I don't think there would also be individual mappings for the tables in the model. Either way, such a model doesn't have any inheritance on the .NET side. Mapping the tables to an inheritance hierarchy on the .NET side would likely need more mapping capabilities in EF Core for TPC/TPT, but exactly what would depend on the shape of the mapped entity/entities.

[Angelinsky7]

@ajcvickers thanks for your answer...
for now i could effectively map each table separately into the .NET side and use them separately.
But then, comes another problem : the order of the operations in the database.
Because, as a ef core user, i cannot control neither the dependencies of the entities or the order of what sql operations is done by ef core, most of the time (it seems random), i'll hit a database constraint error.
The only way to avoid it, it's to open a transaction and call SaveChanges for each changes...but it's not performant at all...
How can we avoid that ?
This is why i was asking for a custom order interface... or something that let the user choose the order of entities, even if ef core wants to order them differently...

For me, either way ef core can completely map the database model (with multiple inheritance for example) or we say "ok, ef core cannot map does functionalities but we can workaround them efficiently and correctly without having the impression that this is a hack".

What do you think ?

[ajcvickers]

@Angelinsky7 If there are FK constraints or unique indexes in the database, and these constraints are mapped into the EF model in the normal way, then EF should order operations such that these constraints are not violated. There are some circular dependencies that may result in EF not being able to find an order for some set of data, but in that case EF should throw saying that no order can be found. So if you are hitting places where this does not happen it would be great if you could file an issue with a runnable project/solution or code listing so that we can attempt to reproduce/fix the issue.

[Angelinsky7]

@ajcvickers thanks again for your answer...
i re-read the documentation and understood that i could do that :

entity.HasOne<TableA>().WithMany().HasForeignKey(p => p.Id).IsRequired(true);
entity.HasOne<TableA>().WithOne().HasForeignKey<TableB>(p => p.Id).IsRequired(true);

So it resolves the issue with the order !!
I just need to figure how to tell ef core to use the "main primary key from tableA" directly (without needing to open a transaction explicitly and make two SaveChanges call) :

TableA tableA = new TableA {
                PropA1 = "p1",
                PropA2 = "p2"
            };

**await _context.SaveChangesAsync(); //I would want to SKIP that call**

            TableB tableB = new TableB {
                Id = **tableA.Id**,
                PropB = "p3"
            };

await _context.SaveChangesAsync();

But that's for another question...
Thanks for your answer, i'll be impatiently waiting for #620....
And sorry for the noise.

[ajcvickers]

@Angelinsky7 EF does key propagation from principals to dependents, but usually this requires navigation properties to be used. The navigation properties then define the graph of entities and EF uses that to flow the key value.