Is it possible for EF Core 3.0 to use FromSql inside ModelBuilder rather than on DbSet<>

entity-framework-core

Question

I am mapping EF onto a legacy DB, and at the moment have to create views in the legacy DB to do that.

Rather than modify the existing schema at all, I'd like to use the new HasNoKey existing FromSql methods to map my entities onto SQL defined within my application.

i.e.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<Names>(entity =>
  {
    entity.HasNoKey();
    entity.FromSql("{SELECT_STATEMENT_FROM_MY_VIEW}");
    ... OR ...
    entity.ToView("{SELECT_STATEMENT_FROM_MY_VIEW_NOT_VIEW_NAME}")
  });

}

I'm thinking I could do this in the new IInterceptor but this feels a bit hacky to me.

1
0
10/8/2019 10:34:26 AM

Popular Answer

I did in the end use IInterceptor - basically it makes the assumption that in T-SQL, a View is always equivalent (for read-only purposes) to a select and can therefore be directly substituted.

This is the core extensions class that generates registers an interceptor singleton and tracks SQL to substitute by inserting a guid-based "mock" table name using the existing ToView. Then before running the command text it replaces the non-existent view with the associated SQL:

public static class DbContextExtensions
{
    private static readonly SqlViewInterceptor SqlViewInterceptorSingleton = new SqlViewInterceptor();

    public static DbContextOptionsBuilder AddViewToSqlInterceptor(
        this DbContextOptionsBuilder dbContextOptionsBuilder)
    {
        dbContextOptionsBuilder.AddInterceptors(SqlViewInterceptorSingleton);
        return dbContextOptionsBuilder;
    }

    public static EntityTypeBuilder<T> ToSqlView<T>(this EntityTypeBuilder<T> entityTypeBuilder, string sql)
        where T : class
    {
        return entityTypeBuilder.ToView(SqlViewInterceptorSingleton.RegisterSqlForView(sql));
    }

    private class SqlViewInterceptor : DbCommandInterceptor
    {
        static readonly ConcurrentDictionary<string, string> MockTablesToSql = new ConcurrentDictionary<string, string>();

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            foreach (var mockTable in MockTablesToSql.Keys)
            {
                command.CommandText = command.CommandText.Replace(mockTable, MockTablesToSql[mockTable]);
            }

            return base.ReaderExecuting(command, eventData, result);
        }

        public string RegisterSqlForView(string viewSql)
        {
            var mockTableName = Guid.NewGuid().ToString();
            MockTablesToSql.TryAdd($"[{mockTableName}]", $"({viewSql})");
            return mockTableName;
        }
    }
}

Then we can use a standard DbContext in the normal way - must ensure that the interceptor is registered in the OnConfiguring method, and can then use the AddViewToSql extension to register View equivalent read-only SQL:

public class LegacyDbContext : DbContext
{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("data source=.\\sql2017; database=Test; integrated security=true")
            .AddViewToSqlInterceptor();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Arbitrary SQL for parent
        modelBuilder
            .Entity<LegacyEntity>()
            .ToSqlView("SELECT CASE LegacyId WHEN 100 THEN 1 ELSE LegacyId END LegacyId FROM LegacyTable");

        // Arbitrary SQL for child
        modelBuilder
            .Entity<LegacyChild>()
            .ToSqlView("SELECT LegacyParentId LegacyEntityLegacyId, LegacyChildId FROM LegacyChild");
    }

    public DbSet<LegacyEntity> LegacyEntities { get; set; }
}

public class LegacyEntity
{
    [Key]
    public int LegacyId { get; set; }

    public IList<LegacyChild> Children { get; set;  }
}

public class LegacyChild
{
    public int LegacyChildId { get; set; }
}

And here are a few unit tests I wrote to confirm that (for simple cases at least) the behaviour is as expected - EF will still restrict via WHERE clauses and aggregate via SUM in SQL, and it will allow Include to work for parent-child relationships...

[TestFixture]
public class TestDbContext
{
    [SetUp]
    public void SetUp()
    {
        using var ctx = new LegacyDbContext();
        ctx.Database.ExecuteSqlRaw("TRUNCATE TABLE LegacyTable;");
        ctx.Database.ExecuteSqlRaw("TRUNCATE TABLE LegacyChild;");
        for (var i = 1; i < 10; i++)
        {
            ctx.Database.ExecuteSqlRaw($"INSERT INTO LegacyTable (LegacyId) VALUES ({i});");
            ctx.Database.ExecuteSqlRaw($"INSERT INTO LegacyChild (LegacyParentId, LegacyChildId) VALUES ({i}, {i * 2});");
        }
    }

    [Test]
    public void TestLegacyView()
    {
        using var ctx = new LegacyDbContext();
        var filteredRows = ctx.LegacyEntities.Where(x=>x.LegacyId <= 5).ToArray();
        Assert.That(filteredRows.Length, Is.EqualTo(5));
    }

    [Test]
    public void TestLegacyViewScalar()
    {
        using var ctx = new LegacyDbContext();
        var filteredRows = ctx.LegacyEntities.Where(x=>x.LegacyId <= 5).Sum(x=>x.LegacyId);
        Assert.That(filteredRows, Is.EqualTo(15));
    }

    [Test]
    public void TestLegacyChild()
    {
        using var ctx = new LegacyDbContext();
        var filteredRows = ctx.LegacyEntities
            .Include(x=>x.Children)
            .Where(x => x.LegacyId <= 5)
            .ToArray()
            .Sum(x => x.Children.Sum(c=>c.LegacyChildId));

        Assert.That(filteredRows, Is.EqualTo(30));
    }
}
0
10/9/2019 9:08:26 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow