Interceptory w Entity Framework

AddOrder.cs

public partial class AddOrder : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Orders",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Number = c.String(),
                    IsActive = c.Boolean(nullable: false),
                })
            .PrimaryKey(t => t.Id);
        
        CreateTable(
            "dbo.OrderBaseProducts",
            c => new
                {
                    Order_Id = c.Int(nullable: false),
                    BaseProduct_Id = c.Int(nullable: false),
                    //BaseProduct_ValidFrom = c.DateTime(nullable: false),
                    //BaseProduct_ValidTo = c.DateTime(nullable: false),
                })
            //.PrimaryKey(t => new { t.Order_Id, t.BaseProduct_Id, t.BaseProduct_ValidFrom, t.BaseProduct_ValidTo })
            .PrimaryKey(t => new { t.Order_Id, t.BaseProduct_Id })
            .ForeignKey("dbo.Orders", t => t.Order_Id, cascadeDelete: true)
            .Index(t => t.Order_Id);
        
    }
    
    public override void Down()
    {
        DropForeignKey("dbo.OrderBaseProducts", "Order_Id", "dbo.Orders");
        DropIndex("dbo.OrderBaseProducts", new[] { "Order_Id" });
        DropTable("dbo.OrderBaseProducts");
        DropTable("dbo.Orders");
    }
}

BaseProduct.cs

public abstract class BaseProduct : BaseModel
{
    public string Name { get; set; }

    public int CategoryId { get; set; }

    public virtual Category Category { get; set; }

    public string Description { get; set; }

    public DateTime ValidFrom { get; set; }

    public DateTime ValidTo { get; set; }

    public virtual ICollection<Order> Orders { get; set; }
}

DataContext.cs

public class DataContext : DbContext
{
    static DataContext()
    {
        DbInterception.Add(new TemporalTableCommandTreeInterceptor());
    }
}

Delete1.sql

DELETE [dbo].[OrderBaseProducts]
WHERE  (((([Order_Id] = 5 /* @0 - [Order_Id] */)
          AND ([BaseProduct_Id] = 5 /* @1 - [BaseProduct_Id] */))
         AND ([BaseProduct_ValidFrom] = '2019-03-11T04:59:03' /* @2 - [BaseProduct_ValidFrom] */))
        AND ([BaseProduct_ValidTo] = '2019-03-11T04:59:04' /* @3 - [BaseProduct_ValidTo] */))

Delete2.sql

DELETE [dbo].[OrderBaseProducts]
WHERE  (((([Order_Id] = 5 /* @0 - [Order_Id] */)
          AND ([BaseProduct_Id] = 5 /* @1 - [BaseProduct_Id] */))

Insert1.sql

INSERT [dbo].[OrderBaseProducts]
       ([Order_Id],
        [BaseProduct_Id],
        [BaseProduct_ValidFrom],
        [BaseProduct_ValidTo])
VALUES (3 /* @0 - [Order_Id] */,
        1 /* @1 - [BaseProduct_Id] */,
        '2019-03-10T06:14:18' /* @2 - [BaseProduct_ValidFrom] */,
        '9999-12-31T23:59:59' /* @3 - [BaseProduct_ValidTo] */)

Insert2.sql

INSERT [dbo].[OrderBaseProducts]
       ([Order_Id],
        [BaseProduct_Id])
VALUES (3 /* @0 - [Order_Id] */,
        1 /* @1 - [BaseProduct_Id] */)

Order.cs

public class Order : BaseModel
{
    public string Number { get; set; }

    public virtual ICollection<BaseProduct> Products { get; set; }
}

Program.cs

class Program
{
    static void Main(string[] args)
    {
        int productId = 0;

        productId = AddAndUpdateWithHistory();

        int orderId = AddProductsToOrder();

        RemoveProductFromOrder(orderId, productId);
    }

    private static int AddProductsToOrder()
    {
        using (DataContext db = new DataContext())
        {
            var products = db.Products.OfType<Product>();

            var order = new Order()
            {
                Number = "order number",
                Products = products.Cast<BaseProduct>().ToList()
            };

            db.Orders.Add(order);
            db.SaveChanges();

            return order.Id;
        }
    }

    private static void RemoveProductFromOrder(int orderId, int productId)
    {
        using (DataContext db = new DataContext())
        {
            var order = db.Orders.FirstOrDefault(o => o.Id == orderId);

            var product = order.Products.FirstOrDefault(p => p.Id == productId);

            order.Products.Remove(product);

            db.SaveChanges();
        }
    }
}

Select1.sql

SELECT CASE
         WHEN ([UnionAll1].[C1] = 1) THEN '1X0X'
         ELSE '1X1X'
       END                       AS [C1],
       [UnionAll1].[Id]          AS [C2],
       [UnionAll1].[ValidFrom]   AS [C3],
       [UnionAll1].[ValidTo]     AS [C4],
       [UnionAll1].[Name]        AS [C5],
       [UnionAll1].[CategoryId]  AS [C6],
       [UnionAll1].[Description] AS [C7],
       [UnionAll1].[IsActive]    AS [C8]
FROM   [dbo].[OrderBaseProducts] AS [Extent1]
       INNER JOIN (SELECT [Extent2].[Id]          AS [Id],
                          [Extent2].[ValidFrom]   AS [ValidFrom],
                          [Extent2].[ValidTo]     AS [ValidTo],
                          [Extent2].[Name]        AS [Name],
                          [Extent2].[CategoryId]  AS [CategoryId],
                          [Extent2].[Description] AS [Description],
                          [Extent2].[IsActive]    AS [IsActive],
                          cast(0 as bit)          AS [C1]
                   FROM   [dbo].[ProductsHistory] AS [Extent2]
                   UNION ALL


                   SELECT [Extent3].[Id]          AS [Id],
                          [Extent3].[ValidFrom]   AS [ValidFrom],
                          [Extent3].[ValidTo]     AS [ValidTo],
                          [Extent3].[Name]        AS [Name],
                          [Extent3].[CategoryId]  AS [CategoryId],
                          [Extent3].[Description] AS [Description],
                          [Extent3].[IsActive]    AS [IsActive],
                          cast(1 as bit)          AS [C1]
                   FROM   [dbo].[Products] AS [Extent3]) AS [UnionAll1]
         ON ([Extent1].[BaseProduct_Id] = [UnionAll1].[Id])
            AND ([Extent1].[BaseProduct_ValidFrom] = [UnionAll1].[ValidFrom])
            AND ([Extent1].[BaseProduct_ValidTo] = [UnionAll1].[ValidTo])
WHERE  [Extent1].[Order_Id] = 4 /* @EntityKeyValue1 - [Order_Id] */

Select2.sql

SELECT CASE
         WHEN ([UnionAll1].[C1] = 1) THEN '1X0X'
         ELSE '1X1X'
       END                       AS [C1],
       [UnionAll1].[Id]          AS [C2],
       [UnionAll1].[ValidFrom]   AS [C3],
       [UnionAll1].[ValidTo]     AS [C4],
       [UnionAll1].[Name]        AS [C5],
       [UnionAll1].[CategoryId]  AS [C6],
       [UnionAll1].[Description] AS [C7],
       [UnionAll1].[IsActive]    AS [C8]
FROM   [dbo].[OrderBaseProducts] AS [Extent1]
       INNER JOIN (SELECT [Extent2].[Id]          AS [Id],
                          [Extent2].[ValidFrom]   AS [ValidFrom],
                          [Extent2].[ValidTo]     AS [ValidTo],
                          [Extent2].[Name]        AS [Name],
                          [Extent2].[CategoryId]  AS [CategoryId],
                          [Extent2].[Description] AS [Description],
                          [Extent2].[IsActive]    AS [IsActive],
                          cast(0 as bit)          AS [C1]
                   FROM   [dbo].[ProductsHistory] AS [Extent2]
                   UNION ALL


                   SELECT [Extent3].[Id]          AS [Id],
                          [Extent3].[ValidFrom]   AS [ValidFrom],
                          [Extent3].[ValidTo]     AS [ValidTo],
                          [Extent3].[Name]        AS [Name],
                          [Extent3].[CategoryId]  AS [CategoryId],
                          [Extent3].[Description] AS [Description],
                          [Extent3].[IsActive]    AS [IsActive],
                          cast(1 as bit)          AS [C1]
                   FROM   [dbo].[Products] AS [Extent3]) AS [UnionAll1]
         ON [Extent1].[BaseProduct_Id] = [UnionAll1].[Id]
WHERE  [Extent1].[Order_Id] = 5 /* @EntityKeyValue1 - [Order_Id] */

TemporalTableCommandTreeInterceptor1.cs

internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
{
    private static readonly List<string> _namesToIgnore = new List<string> { "ValidFrom", "ValidTo" };

    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
    {
        if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
        {
            var insertCommand = interceptionContext.Result as DbInsertCommandTree;
            if (insertCommand != null)
            {
                var newCommand = HandleInsertCommand(insertCommand);

                interceptionContext.Result = newCommand;
            }

            var updateCommand = interceptionContext.Result as DbUpdateCommandTree;
            if (updateCommand != null)
            {
                var newCommand = HandleUpdateCommand(updateCommand);

                interceptionContext.Result = newCommand;
            }
        }
    }

    private static DbUpdateCommandTree HandleUpdateCommand(DbUpdateCommandTree updateCommand)
        {
            var newSetClauses = GenerateSetClauses(updateCommand.SetClauses);

            var newCommand = new DbUpdateCommandTree(
                updateCommand.MetadataWorkspace,
                updateCommand.DataSpace,
                updateCommand.Target,
                updateCommand.Predicate,
                newSetClauses,
                updateCommand.Returning);
            return newCommand;
        }

    private static DbInsertCommandTree HandleInsertCommand(DbInsertCommandTree insertCommand)
        {
            var newSetClauses = GenerateSetClauses(insertCommand.SetClauses);

            var newCommand = new DbInsertCommandTree(
                insertCommand.MetadataWorkspace,
                insertCommand.DataSpace,
                insertCommand.Target,
                newSetClauses,
                insertCommand.Returning);

            return newCommand;
        }

    private static ReadOnlyCollection<DbModificationClause> GenerateSetClauses(IList<DbModificationClause> modificationClauses)
        {
            var props = new List<DbModificationClause>(modificationClauses);
            props = props.Where(_ => IgnoreProperty(_) == false).ToList();

            var newSetClauses = new ReadOnlyCollection<DbModificationClause>(props);
            return newSetClauses;
        }

    private static bool IgnoreProperty(DbModificationClause clause)
        {
            string propertyName = (((clause as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)
                ?.Name;

            if (propertyName == null)
            {
                return false;
            }

            return _namesToIgnore.Any(n => propertyName.Contains(n));
        }
}

TemporalTableCommandTreeInterceptor2.cs

internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
{
    private static readonly List<string> _namesToIgnore = new List<string> { "ValidFrom", "ValidTo" };

    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
        {
            if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
            {
                var queryCommand = interceptionContext.Result as DbQueryCommandTree;
                if (queryCommand != null)
                {
                    interceptionContext.Result = HandleQueryCommand(queryCommand);
                }
            }
        }

    private static DbCommandTree HandleQueryCommand(DbQueryCommandTree queryCommand)
        {
            var newQuery = queryCommand.Query.Accept(new QueryVisitor());
            return new DbQueryCommandTree(
                queryCommand.MetadataWorkspace,
                queryCommand.DataSpace,
                newQuery);
        }

    private class QueryVisitor : DefaultExpressionVisitor
        {
            public override DbExpression Visit(DbJoinExpression expression)
            {
                var visitor = new TemporalTableVisitor();

                visitor.ProcessExpression(expression.JoinCondition);

                if (visitor.IsTemporalExpression)
                {
                    return DbExpressionBuilder.InnerJoin(expression.Left, expression.Right, visitor.Expression);
                }

               return base.Visit(expression);
            }
        }

    private class TemporalTableVisitor
    {
        private List<DbExpression> _expressions = new List<DbExpression>();

        public DbExpression Expression
        {
            get
            {
                if (_expressions.Count == 0)
                {
                    return null;
                }

                if (_expressions.Count == 1)
                {
                    return _expressions[0];
                }

                return DbExpressionBuilder.And(_expressions[0], _expressions[1]);
            }
        }

        private bool _isTemporalExpression = false;

        public bool IsTemporalExpression
        {
            get { return _isTemporalExpression && Expression != null; }
        }

        public void ProcessExpression(DbExpression expression)
        {
            if (expression.ExpressionKind == DbExpressionKind.And)
            {
                var endExpression = expression as DbAndExpression;
                ProcessExpression(endExpression.Left);
                ProcessExpression(endExpression.Right);
            }

            if (expression.ExpressionKind == DbExpressionKind.Equals)
            {
                bool temporalComparision = false;

                var equalExpression = expression as DbComparisonExpression;

                var left = equalExpression.Left as DbPropertyExpression;

                if (left != null)
                {
                    temporalComparision = _namesToIgnore.Any(n => left.Property.Name.Contains(n));
                }

                var right = equalExpression.Right as DbPropertyExpression;

                if (right != null)
                {
                    temporalComparision = temporalComparision | _namesToIgnore.Any(n => right.Property.Name.Contains(n));
                }

                if (temporalComparision == false)
                {
                    _expressions.Add(expression);
                }
                else
                {
                    _isTemporalExpression = true;
                }
            }
        }
    }
}

TemporalTableCommandTreeInterceptor3.cs

internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
{
    private static readonly List<string> _namesToIgnore = new List<string> { "ValidFrom", "ValidTo" };

    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
        {
            if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
            {
                var deleteCommand = interceptionContext.Result as DbDeleteCommandTree;
                if (deleteCommand != null)
                {
                    interceptionContext.Result = HandleDeleteCommand(deleteCommand);
                }
            }
        }

    private static DbCommandTree HandleDeleteCommand(DbDeleteCommandTree deleteCommand)
        {
            var predicate = deleteCommand.Predicate.Accept(new DeleteVisitor());

            return new DbDeleteCommandTree(deleteCommand.MetadataWorkspace,
                deleteCommand.DataSpace,
                deleteCommand.Target,
                predicate);
        }

    private class DeleteVisitor : DefaultExpressionVisitor
        {
            public override DbExpression Visit(DbAndExpression expression)
            {
                var visitor = new TemporalTableVisitor();

                visitor.ProcessExpression(expression);

                if (visitor.IsTemporalExpression)
                {
                    return visitor.Expression;
                }

                return base.Visit(expression);
            }
        }    
}