Geschichtsschreibung

CREATE TABLE ITVisions.[MeasuringDataSet]
(
  [ID] [bigint] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  [Value] [decimal](18, 2) NOT NULL,
  [DateTime] [datetime2](7) NOT NULL,
  [Sensor] [int] NOT NULL,
  [Note] [nvarchar](max) NULL,
  [ValidStart] [datetime2](7) GENERATED
    ALWAYS AS ROW START NOT NULL,
  [ValidEnd] [datetime2](7) GENERATED
    ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidStart], [ValidEnd])
)

WITH (SYSTEM_VERSIONING = ON
  (HISTORY_TABLE = ITVision
  s.MeasuringDataSet_History));

CREATE TABLE ITVisions.[MeasuringDataSet2]
(
  [ID] [bigint] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  [Value] [decimal](18, 2) NOT NULL,
  [DateTime] [datetime2](7) NOT NULL,
  [Sensor] [int] NOT NULL,
  [Note] [nvarchar](max) NULL
)

--- Hier könnten schon Datensätze eingefügt werden!

ALTER TABLE ITVisions.[MeasuringDataSet2]
ADD
  [ValidStart] datetime2 GENERATED ALWAYS AS ROW
    START NOT NULL
    DEFAULT SYSUTCDATETIME(),
  [ValidEnd] datetime2 GENERATED ALWAYS AS ROW END 
    NOT NULL
    DEFAULT CAST('9999-12-31 23:59:59.9999999'
      AS datetime2),
  PERIOD FOR SYSTEM_TIME ([ValidStart],[ValidEnd]);

ALTER TABLE ITVisions.[MeasuringDataSet2]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE =
  ITVisions.[MeasuringDataSet2_History]));

using (var ctx = new Context()) {
  CUI.H2("ToList()");
  var alle = ctx.MeasuringDataSet.ToList();
  CUI.H3(alle.Count + " aktuelle Datensätze:");
  Print(ctx, alle);

  CUI.H2("TemporalAll()");
  var alleHistory =
    ctx.MeasuringDataSet.TemporalAll().ToList();
  CUI.H3(alleHistory.Count +
    " aktuelle und alte Datensätze:");
  Print(ctx, alleHistory);

  CUI.H2("SingleOrDefault()");
  var DatensatzAktuell = ctx.MeasuringDataSet
    .SingleOrDefault(x => x.ID == ID);
  Print(ctx, DatensatzAktuell);

  var vorEinemTag = ZeitpunktVorDenAenderungen
    .AddDays(-1); // Zustand vor einem Tag:               
                  // das wird nichts liefern
  CUI.H2("TemporalAsOf: Datensatz zum " +
    vorEinemTag);
  var DatensatzNochAelter = ctx.MeasuringDataSet
    .TemporalAsOf(vorEinemTag.ToUniversalTime())
    .SingleOrDefault(x => x.ID == ID);
  Print(ctx, DatensatzNochAelter);

  CUI.H2("TemporalAsOf: Datensatz zum "
    + ZeitpunktVorDenAenderungen);
    // ToUniversalTime() --> Zeitzone beachten!
  var DatensatzAlt = ctx.MeasuringDataSet
    .TemporalAsOf(ZeitpunktVorDenAenderungen
    .ToUniversalTime()).SingleOrDefault(
      x => x.ID == ID);
  Print(ctx, DatensatzAlt);

  using (var ctx2 = new Context()) {
    CUI.H2("Änderung des am historischen Datensatz
      erzeugen neuen aktuellen Datensatz:");
    Print(ctx2, DatensatzAlt);
    ctx2.Attach(DatensatzAlt);
    Print(ctx2, DatensatzAlt);
    DatensatzAlt.Value--;
    Print(ctx2, DatensatzAlt);
    Console.WriteLine("Anzahl der gespeicherte
      Änderungen: " + ctx2.SaveChanges());
    Print(ctx2, DatensatzAlt);
  }

  CUI.H2($"TemporalBetween: Alle Zustände des
    Datensatzes von {vorEinemTag} bis
    {DateTime.UtcNow.AddDays(1)}:");
  var DatensaetzeInEinemZeitraum =
    ctx.MeasuringDataSet.TemporalBetween(vorEinemTag,
    DateTime.UtcNow.AddDays(1)).Where(
    x => x.ID == ID).OrderBy(x => x.DateTime)
    .ToList();
  CUI.H3(DatensaetzeInEinemZeitraum.Count +
    " Datensätze im Zeitraum:");
  Print(ctx, DatensaetzeInEinemZeitraum);
}

private static void Print(Context ctx,
  List<MeasuringData> alle) {
  foreach (var d in alle)
  {
    Print(ctx, d);
  }
}

private static void Print(Context ctx,
  MeasuringData d) {
  if (d == null) CUI.Warning(
    $"Kein Datensatz gefunden!");
  else Console.WriteLine($"Datensatz #{d.ID}
    {d.DateTime} Wert: {d.Value} State:
    {ctx.Entry(d).State} {ctx.Entry(d)
      .Property("ValidStart").CurrentValue}->
    {ctx.Entry(d).Property("ValidEnd")
      .CurrentValue}");
}