Feature Request: Define Backing Store seperate from POCO

As an aside:

Search engines are chaos when attempting to understand how to work with EF. First you will find information for a variety of out dated versions; second, it appears to be incoherent and incomplete; finally, there doesn't seem to be a best practices documentation.

On to my request: I've been working with SSDT and my own custom datalayer and poco framework. I hit a roadblock with SSDT when it came to Security Policies and RECONFIGURE statements. So, now I am looking into EF - specifically code first -, simply because I want to ensure that my code and my data model are in sync.

Along my way, I have encountered a few challenges. It is likely that I may not understand the intended development cycle for SQL and EF. I would like to be able to define my backing store and then consume that store via my poco and data layer (aka current EF).

Here is my scenario:
1) Multi-Tennant with shared DB and shared SCHEMA.
--Just a constraint, no limitation from EF that I can see 2) Custom USER/GROUP security --Just a constraint, no limitation from EF that I can see 3) Row Level Security via security policies --No way to define these without manually adding Sql() statements in the DbMigration --No way to define the consumed UDF to support the policy without the Sql statments 4) Base table to manage ACL, all Nouns are 1:1 with the base table --No simple way to define this. yes, this would be TPT, however, the underlying Instead of Triggers will take care of the base table, so I need to simply create the base table and assign foreign keys to the derived tables. --No way to implement breaking changes to these tables without custom Sql() statments (e.g. Clone table, drop FK's, drop table, drop schema bound dependers, rebuild table, populate from clone, rebuild FK's, rebuild schema bound dependers) 5) Instead of Triggers for Create, Update, Delete for all Nouns derived from Base table --No way to define this without Sql() statements. BTW, these statements can be quite large, I suppose I could store them as a resource string. --No way to know if a table is missing one 6) Async Queue for long running tasks, low priority transactions, auditing --No way to define a broker or agent without Sql() statements 7) Stored Procedures for non BCP transactions --No way to define stored procedures without Sql() statements. --Able to bind crud to stored procedures --No way to define custom verbs and bind to stored procedures (e.g. lock/unlock account). 8) Versioning, Archival and Purge Mechanisms strewn about --Just a constraint, no limitation from EF that I can see 9) Optimized schema bound views --No way to define these --No way to ensure they exist --No provision to drop schema bound views / functions before alter table statements without custom Sql() statements --No way to set dependency order, without manually tracking 10) Use of after action triggers for async signaling

What would be perfect is if I could define the backing store (say via the dbContext) and then, while defining my poco classes, validate the backing store against the poco.

examples

[DbContextSchemaEnum]
public enum Schema{
  dbo=1,
  CRM=2,
  Security=9000,
}

create a base table.

[BaseTable("Items", Schema=Schema.DBO)]
public abstract class Item{}

create a derived table.

[DerivedTable("Customers", Schema=Schema.CRM, BaseTable = "MyCompany.MyLibrary.BaseTable"]
[InsteadOfInsert(true),InsteadOfUpdate(true),InsteadOfDelete(true)]
public class Customers:Items{}

create a UDF

class fnIsMemberOf:DbFunctionBuilder
{
  public override string Name{get;set;}
  public override Schema Schema{get;set;}
  public override bool IsSchemaBound{get;set;}
  public override Metadata.Edm.PrimitiveTypeKind ReturnType{get;set;}
  Public override List<Parameter> InputParameters{get;set;}
  Public override List<Directive> Directives{get;set;}
  public override List<type> Dependancies{get{
    if(useLinq)
     return GetLinqDependancies();
     return _dependancies;
   }}
  public string Body{get{
    if(UseLinq)
      return  GetLinq();
      return GetSQL();
  }}
}
create a View
namespace Views {
  [View("vwEmployees", Schema=Schema.CRM, Builder=TypeOf(EmployeesViewBuilder))]
  public class Employees{}
}

Create a Security Policy

class OrderFilter:DbSecurityPolicy{
  public override string Name{get{return "OrderFilterPolicy";}}
  public override Schema Schema{get{return Schema.Security;};}
  Public override PolicyState PolicyState{get{return PolicyState.On;}}
  Public override SchemaBinding SchemaBinding{get{return SchemaBinding.On;}}
  Public override ForReplication{get{return true;}}
  Public override List<PolicyPredicate> Predicates{Get{
      List<PolicyPredicate> result = new List<PolicyPredicate>();
      result.Add(Predicates.Create("AddOrderFilterPredicate");
      result.Add(Predicates.Create("AddBlockTennantAccessOnOrdersPredicate");
   }}
  public override List<type> Dependancies{get{
    if(useLinq)
     return GetLinqDependancies();
     return _dependancies;
   }}
  public string Body{get{
    if(UseLinq)
      return  GetLinq();
      return GetSQL();
  }}
}

Do it all in Fluent

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Items>()
    .IsBaseTable().ToTable("Items", Schema=Schema.dbo)
    .HasSecurityPolicy((new TennantSecurityPolicy()).Enable());

    modelBuilder.Entity<Customer>()
    .IsDerivedTable(BaseTable="Items").ToTable("Customers", Schema=Schema.CRM)
    .InnerJoin("Items", Schema=Schema.dbo).On(new[]{Id, Id}) //.And()
    .HasSecurityPolicy((new ActiveCustomerPolicy()).Enable());

   modelBuilder.Entity<Order>()
    .IsDerivedTable(BaseTable="Items").ToTable("Orders", Schema=Schema.CRM)
    .InnerJoin("Items", Schema=Schema.dbo).On(new[]{Id, Id})
    .InsteadOfInsert(new OrderInsteadOfInsert()) 
    .InsteadOfUpdate(new OrderInsteadOfUpdate())
    .InsteadOfDelete(new OrderInsteadOfDelete())
    .HasSecurityPolicy((new OrderFilter()).Enable())
    .HasSecurityPolicy((new OrderFilter2()).Disable());
    .AddTrigger("trgPrintInvoice",Schema = Schema.CRM, SQL=Resource.SQL_TRG_PRINTINVOICE);

    modelBuilder.Functions.Add(new fnIsMemberOf());
    modelBuilder.Functions.Add(new DbFunctionBuilder("fn_FormatDisplayName", Schema=Schema.dbo)
     ).Returns(TypeOf(string)).BodySQL(@"Declare @Result nvarchar(64); set @result=@Lastname+','+@FirstName; return @result;").InputParamaters.Add(new[]{"@LastName","Nvarchar(25)","@FirstName","nvarchar(25)"});

    modelBuilder.Entity<Views.Employee>()
    .IsView("vwEmployees", Schema=Schema.CRM, IsSchemaBound=false)
    .RecomplieOnMigration(true)
    .LinqQuery(v=> from I in modelBuilder.Entity<Item>
                               join e in modelBuilder.Entity<Employee>
                               on i.Id equals e.Id 
                               where i.ItemType.equals("CRM_Employee")
                                Select new{
                                       Id = i.Id,
                                       FirstName = e.Firstname,
                                       Lastname = e.LastName,
                                       HireDate = e.HireDate
                                 });
   modelBuilder.ServiceBrokers
      .EnableBrokers()
      .AddQueue("QueueName", Procedure = new ProcedureBuilder(...))
      .AddContract("//Contract//Name")
      .AddMessageType("//Message//Type)
      .AddService("//Service//Name").ForQueue("QueueName");

}

dotnet / efcore

Feature Request: Define Backing Store seperate from POCO #10657