Click here to Skip to main content
15,888,527 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I'm trying to create a dynamic MySQL "EntityFramework Core" connection object, that is responsible for connection junction tables.
This is my interface for specifying a junction table:
public interface IJunctionTable<T1,T2>
  {
    int ID { get; set; }
    int CompositeKeyA { get; set; }
    int CompositeKeyB { get; set; }

    T1 DataModelRelationA { get; set; }
    T2 DataModelRelationB { get; set; }
  }

This is my interface for specifying that a table has a relation to a junction table:
public interface IJunctionRelationModel<TJunctionTable> where TJunctionTable : class, new()
 {
   ICollection<TJunctionTable> NavigationCollection { get; set; }
 }

Here is the code I want to change so that it's able to fit all junction tables. This code works but is hard coded to use table "MediaDataModel" and "PerformerDataModel". Is it posible to change this architecture to something dynamically ?
C#
public class MySQLJunctionConnection<T> : DbContext where T : class, IJunctionTable<MediaDataModel, PerformerDataModel>
  {
    public DbSet<T> DataModel { get; private set; }
    public DbContextOptionsBuilder Connection { get; private set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      this.Connection = optionsBuilder;
      this.Connection.UseMySql(@"server=mysqlexample.com;database=mydatabase;uid=user1;password=mypassword;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      // Create a composite key (Treats combined properties of junction table as one primary key)
      modelBuilder.Entity<T>().HasKey(pk => new { pk.CompositeKeyA, pk.CompositeKeyB });
      //modelBuilder.Entity<T>().HasKey(pk => new { pk.CompositeKeyA, pk.CompositeKeyB });

      // Configure MediaDataModel's side ("one-to-many" relationship) of current "many-to-many" relationship
      modelBuilder.Entity<T>().HasOne(a => a.DataModelRelationA).WithMany(a => (IEnumerable<T>)a.NavigationCollection).OnDelete(DeleteBehavior.Cascade).HasForeignKey(m => m.CompositeKeyA);

      // Configure PerformerDataModel's side ("one-to-many" relationship) of current "many-to-many" relationship
      modelBuilder.Entity<T>().HasOne(mp => mp.DataModelRelationB).WithMany(p => (IEnumerable<T>)p.NavigationCollection).OnDelete(DeleteBehavior.Cascade).HasForeignKey(p => p.CompositeKeyB);
    }
  }


What I have tried:

I've tried alot of different things.
I've tried making the tables generic T1 and T2, but then the modelBuilder complaines that it is an interface and not a reference type.
Posted
Updated 8-Sep-19 8:35am

1 solution

If you can't figure out how to model something in SQL, don't expect EF to be able to. At some point EF is better at "database first" than "code first".

Create a few (test) relationships in SQL that work, see what EF does with it, THEN create the generic code based on the pattern.
 
Share this answer
 
Comments
Member 11380736 8-Sep-19 15:37pm    
It works perfect when the generic Entity type is hardcoded like here "MediaPerformerJunctionDataModel". But I would like it to be dynamic <t> or <t1,t2>

modelBuilder.Entity<mediaperformerjunctiondatamodel>().HasKey(pk => new { pk.CompositeKeyA, pk.CompositeKeyB });
modelBuilder.Entity<mediaperformerjunctiondatamodel>().HasOne(a => a.DataModelRelationA).WithMany(a => a.NavigationCollection).OnDelete(DeleteBehavior.Cascade).HasForeignKey(m => m.CompositeKeyA);
modelBuilder.Entity<mediaperformerjunctiondatamodel>().HasOne(mp => mp.DataModelRelationB).WithMany(p => p.NavigationCollection).OnDelete(DeleteBehavior.Cascade).HasForeignKey(p => p.CompositeKeyB);

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900