Implement Row Level security column in inherited tables

Question

I'm using Entity Framework with a code-first approach to generate the database. I have introduced row-Level security in SQL Server.

I want to create 3 tables where all the common properties go to ItemVersion and document related properties to DocumentVersion and form related things to FormVersion so and so. I'm supposed to introduce row level security column TenantId in all 3 tables.

interface ITenantSecurityPolicy
{
    int TenantId { get; set; }
}

public class ItemVersion : ITenantSecurityPolicy
{
    public int Id { get; set; }
    public int CreatedPersonId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int TenantId { get; set; }
}

[Table("DocumentVersions")]
public class DocumentVersion : ItemVersion
{
    [StringLength(100)]
    public string DocumentVersionNumber { get; set; }
    public int FileId { get; set; }
}

[Table("FormVersions")]
public class FormVersion : ItemVersion
{
    public string DesignerContentId { get; set; }
}

When I generate the database, it does not create the TenantId in child classes - the column TenantId is created only in the ItemVersion table.

How do I handle this situation? Are there any workaround or suggestions?

Normally, EF should create tables for the two subtypes only, including all columns of the base type. Have you any additional mappings? — Gert Arnold, Jan 05 '18 at 12:16
Yes. I'm using Table splitting using Data Annotation. Note that DocumentVersion and FomVersion have `[Table("FormVersions")]` annotations. — Aruna, Jan 05 '18 at 12:37
Yes, I know, but the model *as you show it* shouldn't create an `ItemVersion` table. — Gert Arnold, Jan 05 '18 at 12:42
Actually, it does. As an additional thing what I have is ` public virtual DbSet BusinessItemVersions { get; set; } public virtual DbSet DocumentVersions { get; set; } public virtual DbSet FormVersions { get; set; }` in my context class. — Aruna, Jan 05 '18 at 12:46
So that's what tells EF to create `(Business)ItemVersion` table. Which means that you have a TPT inheritance scheme now. I think you shouldn't map the base class, so EF will map all subtypes independently. — Gert Arnold, Jan 05 '18 at 12:49

score 0 · Answer 1 · answered Jan 05 '18 at 11:09

The simplest solution is also probably the right relational design for a multi-tenant schema: make the TenantId the leading column in the primary key.

public class ItemVersion : ITenantSecurityPolicy
{
    [Key,Column(Order=0)]
    public int TenantId { get; set; }
    [Key,Column(Order=1),DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int CreatedPersonId { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }

}

Implement Row Level security column in inherited tables

1 Answers1