EntityFramework, Azure ElasticScale, and Table Per Type (TPT) Inheritance

Question

If given a table structure using TPH in Entity Framework.

class ContactLink {
    Guid Contact_Link_ID { get; set;} //pk
    Guid Tenant_ID { get; set;} //fk
    Guid Contact_ID { get; set;} //fk
}

class ContactLinkCustomer : ContactLink {
    Guid Contact_Link_ID { get; set;} //fk
    Guid Customer_ID { get; set;} //fk
}

How should I configure the elastic scale schema info for split merge operations since Entity framework does not include the base class properties in the derived class's table? Specifically Tenant_ID, which is my point map shard key.

SchemaInfo schemaInfo = new SchemaInfo();
schemaInfo.Add(new ShardedTableInfo("dbo", "ContactLinkCustomer", ???));
smm.GetSchemaInfoCollection().Add("ShardName", schemaInfo);

Update: ContactLink is not abstract.

Update 2: I should note that ContactLink is also in my DbContext and is queried independently from ContactLinkCustomer.

Update 3: I am not using TPH, we are actually using TPT. Which is what caused multiple tables instead of the single table with a discriminator.

Answer 1

The below works for me, with the caveat that there is no database-level constraint that keeps the Tenant_ID in sync so they can get out of sync if any code modifies these tables directly through T-SQL (not through EF).

[Table("ContactLink")] // TPT inheritance
class ContactLink
{
    public Guid Contact_Link_ID { get; set; } //pk
    public Guid Tenant_ID { get; set; } //fk
    public Guid Contact_ID { get; set; } //fk
}

[Table("ContactLinkCustomer")] // TPT inheritance
internal class ContactLinkCustomer : ContactLink
{
    // Dummy property to trick EF into creating it as a column for sharding purposes
    // Callers should just directly use the base Tenant_ID property
    // It would be nice if we could set this to be public/protected, but then EF
    // won't create it as a column. Maybe there is a workaround for this?
    [Column("Tenant_ID")]
    public Guid Tenant_ID_ContactLinkCustomer
    {
        get { return base.Tenant_ID; }
        set { base.Tenant_ID = value; }
    }

    public Guid Contact_Link_ID { get; set; } //fk
    public Guid Customer_ID { get; set; } //fk
}

Additional classes that I used for testing are below.

class Program
{
    static void Main(string[] args)
    {
        string connStr = "Server=(local);Database=EfShardingTpt;Integrated Security=true";

        using (MyDbContext myDbContext = new MyDbContext(connStr))
        {
            // Drop and recreate database
            Database.SetInitializer(new DropCreateDatabaseAlways<MyDbContext>());
        }

        // Create ContactLinkCustomer
        using (MyDbContext myDbContext = new MyDbContext(connStr))
        {
            ContactLinkCustomer clc = new ContactLinkCustomer
            {
                Contact_ID = Guid.Empty,
                Contact_Link_ID = Guid.Empty,
                Customer_ID = Guid.Empty,
                Tenant_ID = Guid.Parse("00000000-0000-0000-0000-100000000000")
            };

            myDbContext.ContactLinkCustomers.Add(clc);
            myDbContext.SaveChanges();
        }

        WriteTenantIds(connStr);

        // Update through subtype
        using (MyDbContext myDbContext = new MyDbContext(connStr))
        {
            ContactLinkCustomer clc = myDbContext.ContactLinkCustomers.First();
            clc.Tenant_ID = Guid.Parse("00000000-0000-0000-0000-200000000000");
            myDbContext.SaveChanges();
        }

        WriteTenantIds(connStr);

        // Update through supertype
        using (MyDbContext myDbContext = new MyDbContext(connStr))
        {
            ContactLink cl = myDbContext.ContactLinks.First();
            cl.Tenant_ID = Guid.Parse("00000000-0000-0000-0000-300000000000");
            myDbContext.SaveChanges();
        }

        WriteTenantIds(connStr);
    }

    private static void WriteTenantIds(string connectionString)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = "SELECT Tenant_ID FROM ContactLink";
            Guid contactLinkTenantId = (Guid) cmd.ExecuteScalar();

            cmd.CommandText = "SELECT Tenant_ID FROM ContactLinkCustomer";
            Guid contactLinkCustomerTenantId = (Guid)cmd.ExecuteScalar();

            Console.WriteLine("{0} {1}", contactLinkTenantId, contactLinkCustomerTenantId);
        }
    }
}

class MyDbContext : DbContext
{
    public MyDbContext(string connectionString) : base(connectionString)
    {
    }

    public virtual DbSet<ContactLink> ContactLinks { get; set; }
    public virtual DbSet<ContactLinkCustomer> ContactLinkCustomers { get; set; }

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

        modelBuilder.Entity<ContactLink>()
            .HasKey(e => e.Contact_Link_ID);

        modelBuilder.Entity<ContactLinkCustomer>()
            .HasKey(e => e.Contact_Link_ID);
    }
}

Console output:

00000000-0000-0000-0000-100000000000 00000000-0000-0000-0000-100000000000
00000000-0000-0000-0000-200000000000 00000000-0000-0000-0000-200000000000
00000000-0000-0000-0000-300000000000 00000000-0000-0000-0000-300000000000

---

There also might be some kind of mapping-based solution possible. I tried the below but it doesn't work. Perhaps with some more experimentation it could work, but the above solution seems good enough for me so I didn't explore it further.

modelBuilder.Entity<ContactLinkCustomer>()
            .Map(m =>
            {
                m.Properties(e => e.Tenant_ID);
                m.ToTable("ContactLinkCustomer");
            });

Unhandled Exception: System.NotSupportedException: The type 'ContactLinkCustomer' cannot be mapped as defined because it maps inherited properties from types th
at use entity splitting or another form of inheritance. Either choose a different inheritance mapping strategy so as to not map inherited properties, or change
all types in the hierarchy to map inherited properties and to not use splitting.

Answer 2

If you are using TPH and both ContactLink and ContactLinkCustomer are in the same hierarchy, then EF should have created a single denormalized table with all columns from both classes. In that case ContactLink would be the sharded table with Tenant_ID as the sharding key.

However if you really plan to work with multiple tables, then you must include the Tenant_ID column in the table for ContactLinkCustomer, and shard it on Tenant_ID. The current versions of the Elastic Scale libraries and tools require the sharding key to be present in all sharded tables that participate in Split-Merge.