Temporal Table i Entity Framework

AddDescriptionToProduct.cs

public partial class AddDescriptionToProduct : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Products", "Description", c => c.String());
        //AddColumn("dbo.ProductsHistory", "Description", c => c.String());
    }
    
    public override void Down()
    {
        //DropColumn("dbo.ProductsHistory", "Description");
        DropColumn("dbo.Products", "Description");
    }
}

AddHistoryToProduct.cs

public partial class AddHistoryToProduct : DbMigration
{
    public override void Up()
    {
        Sql(TemporalTableQueryBuilder.GetCreateSql("dbo.Products"));
    }
    
    public override void Down()
    {
        Sql(TemporalTableQueryBuilder.GetDropSql("dbo.Products"));
    }
}

AddHistoryToProduct2.cs

public partial class AddHistoryToProduct2 : DbMigration
{
    public override void Up()
    {
        //DropPrimaryKey("dbo.Products");
        //CreateTable(
        //    "dbo.ProductsHistory",
        //    c => new
        //        {
        //            Id = c.Int(nullable: false, identity: true),
        //            ValidFrom = c.DateTime(nullable: false),
        //            ValidTo = c.DateTime(nullable: false),
        //            Name = c.String(),
        //            CategoryId = c.Int(nullable: false),
        //            IsActive = c.Boolean(nullable: false),
        //        })
        //    .PrimaryKey(t => new { t.Id, t.ValidFrom, t.ValidTo })
        //    .ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: true)
        //    .Index(t => t.CategoryId);
        
        //AddColumn("dbo.Products", "ValidFrom", c => c.DateTime(nullable: false));
        //AddColumn("dbo.Products", "ValidTo", c => c.DateTime(nullable: false));
        //AddPrimaryKey("dbo.Products", new[] { "Id", "ValidFrom", "ValidTo" });
    }
    
    public override void Down()
    {
        //DropForeignKey("dbo.ProductsHistory", "CategoryId", "dbo.Categories");
        //DropIndex("dbo.ProductsHistory", new[] { "CategoryId" });
        //DropPrimaryKey("dbo.Products");
        //DropColumn("dbo.Products", "ValidTo");
        //DropColumn("dbo.Products", "ValidFrom");
        //DropTable("dbo.ProductsHistory");
        //AddPrimaryKey("dbo.Products", "Id");
    }
}

AddProductAndCategory.cs

public partial class AddProductAndCategory : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Categories",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    IsActive = c.Boolean(nullable: false),
                })
            .PrimaryKey(t => t.Id);
        
        CreateTable(
            "dbo.Products",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    CategoryId = c.Int(nullable: false),
                    IsActive = c.Boolean(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: true)
            .Index(t => t.CategoryId);
        
    }
    
    public override void Down()
    {
        DropForeignKey("dbo.Products", "CategoryId", "dbo.Categories");
        DropIndex("dbo.Products", new[] { "CategoryId" });
        DropTable("dbo.Products");
        DropTable("dbo.Categories");
    }
}

BaseModel.cs

public class BaseModel
{
    public BaseModel()
    {
        IsActive = true;
    }

    public int Id { get; set; }

    public bool IsActive { get; set; }
}

BaseProduct.cs

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

    public int CategoryId { get; set; }

    public virtual Category Category { get; set; }

    public DateTime ValidFrom { get; set; }

    public DateTime ValidTo { get; set; }
}

BaseProductConfiguration.cs

public class BaseProductConfiguration : EntityTypeConfiguration<BaseProduct>
{
    public BaseProductConfiguration()
    {
        HasKey(p => new { p.Id, p.ValidFrom, p.ValidTo });
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(p => p.ValidFrom).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(p => p.ValidTo).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

Category.cs

public class Category : BaseModel
{
    public string Name { get; set; }

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

Product.cs

public class Product : BaseModel
{
    public string Name { get; set; }

    public int CategoryId { get; set; }

    public virtual Category Category { get; set; }

    public DateTime ValidFrom { get; set; }

    public DateTime ValidTo { get; set; }
}

Product2.cs

public class Product : BaseProduct
{
}

ProductConfiguration.cs

public class ProductConfiguration : EntityTypeConfiguration<Product>
{
    public ProductConfiguration()
    {
        Map(m => { m.ToTable("dbo.Products"); m.MapInheritedProperties(); });
    }
}

ProductHistory.cs

public class ProductHistory : BaseProduct
{
}

ProductHistoryConfiguration.cs

public class ProductHistoryConfiguration : EntityTypeConfiguration<ProductHistory>
{
    public ProductHistoryConfiguration()
    {
        Map(m => { m.ToTable("dbo.ProductsHistory"); m.MapInheritedProperties(); });
    }
}

Program1.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        AddAndUpdateProductWithoutHistory();
    }
                                                                                                              
    private static void AddAndUpdateProductWithoutHistory()
    {
        using (DataContext db = new DataContext())
        {
            var category = db.Categories.FirstOrDefault();

            var product = new Product()
            {
                Name = "Product",
                Category = category
            };

            db.Products.Add(product);
            db.SaveChanges();

            product.Name = "New Product";

            db.SaveChanges();
        }
    }
}

Program2.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        //AddAndUpdateProductWithoutHistory();

        int productId = 0;

        productId = AddAndUpdateWithHistory();
    }
    
    private static int AddAndUpdateWithHistory()
    {
        int productId;
        using (DataContext db = new DataContext())
        {
            var category = db.Categories.FirstOrDefault();

            var product = new Product()
            {
                Name = "Product",
                Category = category
            };

            db.Products.Add(product);
            db.SaveChanges();

            Thread.Sleep(1 * 1000);

            productId = product.Id;

            product.Name = "New Product";

            db.SaveChanges();
        }

        return productId;
    }
}

Program3.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        //AddAndUpdateProductWithoutHistory();

        int productId = 0;

        productId = AddAndUpdateWithHistory();

        ShowHistoryUsingSql(productId);
    }
                                                                                                              
    private static void ShowHistoryUsingSql(int productId)
    {
        Console.WriteLine("ShowHistoryUsingSql:");

        using (DataContext db = new DataContext())
        {
            var query = $"SELECT * FROM dbo.Products FOR SYSTEM_TIME ALL WHERE Id = {productId}";

            var products =
                db.Database.SqlQuery<Product>(query)
                    .ToList();

            foreach (var item in products)
            {
                Console.WriteLine($"{item.Name}");
            }
        }
    }
}

Program4.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        //AddAndUpdateProductWithoutHistory();

        int productId = 0;

        productId = AddAndUpdateWithHistory();

        ShowHistoryUsingSql(productId);

        GetProductById(productId);
    }
                                                                                                              
    private static void GetProductById(int productId)
    {
        Console.WriteLine("GetProductById:");

        using (DataContext db = new DataContext())
        {
            BaseProduct product = db.Products.OfType<Product>().FirstOrDefault(p => p.Id == productId);

            Console.WriteLine($"{product.Name}, {product.ValidFrom}, {product.ValidTo}");
        }
    }
}

Program5.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        //AddAndUpdateProductWithoutHistory();

        int productId = 0;

        productId = AddAndUpdateWithHistory();

        ShowHistoryUsingSql(productId);

        GetProductById(productId);

        ShowHistoryUsingLinq(productId);
    }
                                                                                                              
    private static void ShowHistoryUsingLinq(int productId)
    {
        Console.WriteLine("ShowHistoryUsingLinq:");

        using (DataContext db = new DataContext())
        {
            var products = db.Products.Where(p => p.Id == productId)
                .OrderByDescending(p => p.ValidFrom);

            foreach (var item in products)
            {
                Console.WriteLine($"{item.Name}, {item.ValidFrom}, {item.ValidTo}");
            }
        }
    }
}

Program6.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        //AddAndUpdateProductWithoutHistory();

        int productId = 0;

        productId = AddAndUpdateWithHistory();

        ShowHistoryUsingSql(productId);

        GetProductById(productId);

        ShowHistoryUsingLinq(productId);

        ShowProductInCategoryLazyLoadingProblem();
    }
                                                                                                              
    private static void ShowProductInCategoryLazyLoadingProblem()
    {
        Console.WriteLine("ShowProductInCategoryLazyLoadingProblem:");

        using (DataContext db = new DataContext())
        {
            var category = db.Categories.FirstOrDefault();

            foreach (var item in category.Products)
            {
                Console.WriteLine($"{item.Name}, {item.ValidFrom}, {item.ValidTo}");
            }
        }
    }
}

Program7.cs

class Program
{
    static void Main(string[] args)
    {
        HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();

        //AddAndUpdateProductWithoutHistory();

        int productId = 0;

        productId = AddAndUpdateWithHistory();

        ShowHistoryUsingSql(productId);

        GetProductById(productId);

        ShowHistoryUsingLinq(productId);

        ShowProductInCategoryLazyLoadingProblem();

        ShowProductInCategoryLinq();
    }
                                                                                                              
    private static void ShowProductInCategoryLinq()
    {
        Console.WriteLine("ShowProductInCategoryLinq:");

        using (DataContext db = new DataContext())
        {
            var category = db.Categories.FirstOrDefault();

            foreach (var item in db.Products.OfType<Product>().Where(p => p.CategoryId == category.Id))
            {
                Console.WriteLine($"{item.Name}, {item.ValidFrom}, {item.ValidTo}");
            }
        }
    }
}

query1.sql

SELECT [Limit1].[C1]          AS [C1],
       [Limit1].[Id]          AS [Id],
       [Limit1].[ValidFrom]   AS [ValidFrom],
       [Limit1].[ValidTo]     AS [ValidTo],
       [Limit1].[Name]        AS [Name],
       [Limit1].[CategoryId]  AS [CategoryId],
       [Limit1].[Description] AS [Description],
       [Limit1].[IsActive]    AS [IsActive]
FROM   (SELECT TOP (1) [Extent1].[Id]          AS [Id],
                       [Extent1].[ValidFrom]   AS [ValidFrom],
                       [Extent1].[ValidTo]     AS [ValidTo],
                       [Extent1].[Name]        AS [Name],
                       [Extent1].[CategoryId]  AS [CategoryId],
                       [Extent1].[Description] AS [Description],
                       [Extent1].[IsActive]    AS [IsActive],
                       '0X0X'                  AS [C1]
        FROM   [dbo].[Products] AS [Extent1]
        WHERE  [Extent1].[Id] = 1003 /* @p__linq__0 */) AS [Limit1]

query2.sql

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


                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(1 as bit)          AS [C1]
                FROM   [dbo].[Products] AS [Extent2]) AS [UnionAll1]
        WHERE  [UnionAll1].[Id] = 1003 /* @p__linq__0 */) AS [Project3]
ORDER  BY [Project3].[C2] DESC

query3.sql

SELECT CASE
         WHEN ([UnionAll1].[C1] = 1) THEN '0X0X'
         ELSE '0X1X'
       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   (SELECT [Extent1].[Id]          AS [Id],
               [Extent1].[ValidFrom]   AS [ValidFrom],
               [Extent1].[ValidTo]     AS [ValidTo],
               [Extent1].[Name]        AS [Name],
               [Extent1].[CategoryId]  AS [CategoryId],
               [Extent1].[Description] AS [Description],
               [Extent1].[IsActive]    AS [IsActive],
               cast(0 as bit)          AS [C1]
        FROM   [dbo].[ProductsHistory] AS [Extent1]
        UNION ALL


        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(1 as bit)          AS [C1]
        FROM   [dbo].[Products] AS [Extent2]) AS [UnionAll1]
WHERE  [UnionAll1].[CategoryId] = 1 /* @EntityKeyValue1 */

query4.sql

SELECT '0X0X'                  AS [C1],
       [Extent1].[Id]          AS [Id],
       [Extent1].[ValidFrom]   AS [ValidFrom],
       [Extent1].[ValidTo]     AS [ValidTo],
       [Extent1].[Name]        AS [Name],
       [Extent1].[CategoryId]  AS [CategoryId],
       [Extent1].[Description] AS [Description],
       [Extent1].[IsActive]    AS [IsActive]
FROM   [dbo].[Products] AS [Extent1]
WHERE  [Extent1].[CategoryId] = 1 /* @p__linq__0 - [CategoryId] */

TemporalTableQueryBuilder.cs

    public class TemporalTableQueryBuilder
    {
        private const string CreateFormat =
            @"
ALTER TABLE {0}
    ADD ValidFrom datetime2(0)
		GENERATED ALWAYS AS ROW START NOT NULL
        DEFAULT SYSUTCDATETIME(),
    ValidTo datetime2(0)
		GENERATED ALWAYS AS ROW END NOT NULL
        DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
GO

ALTER TABLE {0}
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {0}History))
GO";

        private const string DropFormat =
            @"ALTER TABLE {0} SET ( SYSTEM_VERSIONING = OFF )
GO

ALTER TABLE {0} DROP PERIOD FOR SYSTEM_TIME
GO

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'ALTER TABLE {0} DROP CONSTRAINT ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('{0}')
    AND (c.name = N'ValidFrom' OR c.name = N'ValidTo')
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

ALTER TABLE {0} DROP COLUMN ValidFrom
GO

ALTER TABLE {0} DROP COLUMN ValidTo
GO

DROP TABLE {0}History
GO";

        public static string GetCreateSql(string tableName)
        {
            return string.Format(CreateFormat, tableName);
        }

        public static string GetDropSql(string tableName)
        {
            return string.Format(DropFormat, tableName);
        }
    }