Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework multitenant shared data architecture: single column, multiple foreign keys

I have the following data structure:

//property Notification
abstract class BindableBase { }
//base class for all tenant-scoped objects
abstract class TenantModelBase : BindableBase 
{ 
  int TenantId;
} 

abstract class Order : TenantModelBase 
{
   Customer Customer; //works: mapped using TenantId and CustomerId
   Product Product; //again, works with TenantId and ProductId
   string ProductId;
   string CustomerId;
}
class Customer: TenantModelBase 
{
   string CustomerId; 
}

class Product  : TenantModelBase 
{
   string ProductId;
}

class SpecialOrder : Order
{
    OtherClass OtherClass; //this fails!, see below
    string OtherClassId;
}
class SuperSpecialOrder : SpecialOrder {  }

class OtherClass  : TenantModelBase 
{
    string OtherClassId;
}

I get the following error:

The foreign key component 'TenantId' is not a declared property on type 'SpecialOrder'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.

Error occurs using the Fluent Api Configuration:

        config.HasRequired(p => p.OtherClass)
            .WithMany(oc => oc.SpecialOrders)
            .HasForeignKey(p => new {  p.TenantId, p.OtherClassId});

Without the OtherClass reference in SpecialOrder I can create objects freely without problems (including SpecialOrder, SuperSpecialOrder etc).

Anyone have a clue what's going on? I'm lost here :(

Edit I've seen in other questions that people remove the TenantId from tables, this is not an option since primary keys are not unique across tenants and we want to keep the shared data architecture.

I know the workaround is having a second TenantId in the SpecialOrder class, but this does not seem logical to me.

like image 237
Bas Avatar asked Aug 14 '12 09:08

Bas


1 Answers

Are you trying to do a TPT, where there's a separate table for Order/Tenant? If so, I think the other poster is correct, and there's a bug in EF.

If customers/products are your based mapped classes, this might work for you.

What happened to me when I created your database is it tried to map the abstract Order class.

By adding:

modelBuilder.Ignore<Order>();

The builder kept the mapped properties due to the MapInheritedProperties, but didn't create the table so that all the FK's were correctly created.

I'm assuming you wanted separate tables for each of your classes like the related post above, and to not map your abstract table.

Entire Model:

 public abstract class BindableBase { }
   //base class for all tenant-scoped objects
   public abstract class TenantModelBase : BindableBase
   {
      [Key]
      public virtual int TenantId { get; set; }
   }

   public abstract class Order : TenantModelBase
   {
      public Customer Customer { get; set; } //works: mapped using TenantId and CustomerId
      public Product Product { get; set; } //again, works with TenantId and ProductId
      public string ProductId { get; set; }
      public string CustomerId { get; set; }
   }
   public class Customer : TenantModelBase
   {
      [Key]
      public string CustomerId { get; set; }
   }

   public class Product : TenantModelBase
   {
      [Key]
      public string ProductId { get; set; }
   }

   public class SpecialOrder : Order
   {
      [Key]
      public int SpecialOrderId { get; set; }
      public OtherClass OtherClass { get; set; } //this fails!, see below
      public string OtherClassId { get; set; }
   }
   public class SuperSpecialOrder : SpecialOrder { }

   public class OtherClass : TenantModelBase
   {
      public string OtherClassId { get; set; }
      public ICollection<SpecialOrder> SpecialOrders { get; set; }
   }



   public class Model : DbContext
   {
      public DbSet<Customer> Customers { get; set; }
      public DbSet<Product> Products { get; set; }
      public DbSet<SpecialOrder> SpecialOrders { get; set; }
      public DbSet<SuperSpecialOrder> SuperSpecialOrders { get; set; }

      public DbSet<OtherClass> OtherClasses { get; set; }

      protected override void OnModelCreating( DbModelBuilder modelBuilder )
      {
         modelBuilder.Entity<OtherClass>()
            .HasKey( k => new { k.TenantId, k.OtherClassId } );

         modelBuilder.Entity<Customer>()
            .HasKey( k => new { k.TenantId, k.CustomerId } );

         modelBuilder.Entity<Product>()
            .HasKey( k => new { k.TenantId, k.ProductId } );


         modelBuilder.Entity<SpecialOrder>()
            .Map( m =>
                     {
                        m.MapInheritedProperties();
                        m.ToTable( "SpecialOrders" );
                     } );

         modelBuilder.Entity<SpecialOrder>().HasKey( k => new { k.TenantId, k.SpecialOrderId } );

         modelBuilder.Entity<SuperSpecialOrder>()
          .Map( m =>
          {
             m.MapInheritedProperties();
             m.ToTable( "SuperSpecialOrders" );
          } )
          .HasKey( k => new { k.TenantId, k.SpecialOrderId } );

         modelBuilder.Entity<SpecialOrder>()
          .HasRequired( p => p.OtherClass )
          .WithMany( o => o.SpecialOrders )
          .HasForeignKey( p => new { p.TenantId, p.OtherClassId } );

         modelBuilder.Entity<Order>()
            .HasRequired( o => o.Customer )
            .WithMany()
            .HasForeignKey( k => new { k.TenantId, k.CustomerId } );

         modelBuilder.Entity<Order>()
          .HasRequired( o => o.Product )
          .WithMany()
          .HasForeignKey( k => new { k.TenantId, k.ProductId } );

         modelBuilder.Ignore<Order>();


      }
   }

Created Database: enter image description here

Hope this helps.

like image 124
Mark Oreta Avatar answered Oct 20 '22 10:10

Mark Oreta