I'm using Temporal Tables on postgresql (https://github.com/arkhipov/temporal_tables) and C# with dapper.

I'm storing an entity together with its changes. Here's an example entity, with an Id and two values. It was created in 2006 and underwent two simultaneous changes in 2007

| ID | IntValue | StrValue  | sys_period               |
|  1 |   0      |   NULL    | [2006-08-08, 2007-02-27) |
|  1 |   1      |    "foo"  | [2007-02-27, )           |

Here's my setup for storing that kind of data

I have a table with the current entity state:

CREATE TABLE public.SomeEntity
    Id i        integer     PRIMARY KEY,
    IntValue    integer     NULL,
    StrValue    text        NULL,
    sys_period  tstzrange   NOT NULL    DEFAULT tstzrange(current_timestamp, null)

CREATE TABLE public.SomeEntity_History (LIKE public.SomeEntity);

CREATE TRIGGER versioning_trigger
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period','public.SomeEntity_History ', true);

CREATE VIEW SomeEntity_With_History AS
    SELECT * FROM SomeEntity
    SELECT * FROM SomeEntity_History;

SELECT * FROM SomeEntity_With_History WHERE Id = 1 now gives the above table

So, I have a list of the state of the entity at certain times (which I'm going to call 'history'), but how do I see changes?

I see a change as something like this model (in C#):

public class EntityChange
    public DateTime Timestamp { get; set; }
    public PropertyChange[] Changes { get; set; }

public class PropertyChange
    public string PropertyName { get; set; }
    public object OldValue { get; set; }
    public object NewValue { get; set; }
    public Type Type { get; set; }

I have a way to change History into Changes

It's a query in SQL and some mapping, type conversion and nesting in C#. But it seems messy. Is it any good?

public async Task<IEnumerable<EntityChange>> GetArticleChangesAsync(int articleId)
    var propertyChanges = await _context.GetConnection().QueryAsync<PropertyChangeQueryItem>(
        @"SELECT PropertyName, NewValue, OldValue, TypeName, Timestamp
        IntValue, LAG(IntValue) OVER previous AS old_IntValue,
        StrValue, LAG(StrValue) OVER previous AS old_StrValue,
        LOWER(sys_period) AS timestamp
    FROM someentity_with_history
    WHERE id = @id
    WINDOW previous AS (PARTITION BY id ORDER BY sys_period ASC)
) AS rows
    ('IntValue', CAST (IntValue AS text), CAST (old_IntValue AS text), @intType),
    ('StrValue', StrValue, old_StrValue, @stringType),
) AS entityChanges(PropertyName, NewValue, OldValue, TypeName)
                    intType = typeof(int?).FullName,
                    stringType = typeof(string).FullName

    return PropertyChangeQueryItem.DeNormalize(propertyChanges);

internal class PropertyChangeQueryItem
    public DateTime Timestamp { get; set; }
    public string PropertyName { get; set; }
    public string NewValue { get; set; }
    public string OldValue { get; set; }
    public string TypeName { get; set; }

    public static IEnumerable<EntityChange> DeNormalize(IEnumerable<PropertyChangeQueryItem> items)
        var groups = items.GroupBy(ic => ic.Timestamp);
        var entityChanges = groups.Select(grp => new EntityChange
            Timestamp = grp.Key,
            Changes = grp.Select(i =>
                    var type = GetType(i.TypeName);
                    return new PropertyChange
                        PropertyName = i.PropertyName,
                        NewValue = DeStringify(i.NewValue, type),
                        OldValue = DeStringify(i.OldValue, type),
                        Type = type

        return entityChanges;

    private static Type GetType(string typeName)
        var type = Type.GetType(typeName);
        return Nullable.GetUnderlyingType(type) ?? type;

    private static object DeStringify(string value, Type type)
        return value == null ? null : Convert.ChangeType(value, type);

So what's happening?

I match each row of history with its preceeding row using 'LAG' with 'previous'. Then, I cut apart these rows so that I have one row for each column, using CROSS JOIN LATERAL. Then I just choose the rows where the OldValue is different from the new value.

Since I can't be specific about what type I'd like "OldValue" and "NewValue" to be, I have to convert everything to a string. I include the C# type information at that point so I can recover it back into the actual type.

So that query ends up transforming my entity history into this:

| PropertyName | NewValue | OldValue | TypeName         | Timestamp  |
|  "IntValue"  |   1      |   0      | System.int32     | 2007-02-27 |
|  "StrValue"  |   "foo"  |   NULL   | System.string    | 2007-02-27 |

Then I use the C# to shuffle that into a nested class model and unstringify the types in the DeNormalize method. In the above example table that would result in one entity change at 2007-02-27 with two property changes:

    DateTime: 2007-02-27
    Changes: [
            PropertyName: "IntValue",
            OldValue: 0,
            NewValue: 1,
            Type: typeof(int)
            PropertyName: "StrValue",
            OldValue: null,
            NewValue: "foo",
            Type: typeof(string)

So, this works. It doesn't return unnecessary data to C# from sql as it does most of the heavy lifting in SQL. But having to specify every column name twice in the SQL code and having to leave C# type hints and then un-stringify everything is messy. And I'm not sure if the query could be more elegant. What do you think?

  • 1
    \$\begingroup\$ We require concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code,... are outside the scope of this site. Please follow the tour, and read "What topics can I ask about here?", "How do I ask a good question?" and "What types of questions should I avoid asking?". \$\endgroup\$
    – BCdotWEB
    Commented Jul 7, 2020 at 9:18
  • 3
    \$\begingroup\$ Is there a reason you're not calling a stored procedure from C#? The question is a little bit confusing, it is primarily an SQL question but includes C# as well. \$\endgroup\$
    – pacmaninbw
    Commented Jul 7, 2020 at 12:28
  • 1
    \$\begingroup\$ I consider this on-topic. The only risky bit is the title prefix, which I will delete. \$\endgroup\$
    – Reinderien
    Commented Jul 7, 2020 at 15:08
  • \$\begingroup\$ @Reinderien Can you answer it then? It's accumulating close votes. \$\endgroup\$
    – Mast
    Commented Jul 9, 2020 at 19:10
  • 2
    \$\begingroup\$ with the row proceeding it - you probably mean "preceding it". \$\endgroup\$
    – Reinderien
    Commented Jul 9, 2020 at 22:59

1 Answer 1


Externalize your DML

Given the length of the query written in GetArticleChangesAsync, I would expect that either

  • it be moved to a stored procedure (common but IMO overkill); or
  • moved to a view (my usual preference given read-only queries like this).

There are several reasons for this, including:

  • If you have a DBA, they will have more control over the fine-grained details of the query
  • The query is by its nature more closely coupled to the database design than the application design
  • It will be often easier to update a view than rebuild and redeploy the entire application
  • \$\begingroup\$ Thanks +1. Interesting answer. Made me think. Probably not for me though. I think reasons 1 and 3 are good points, but more suitable for environments where time-to-deploy is slow or deployment downtime is a problem. I'm not comfortable with letting SQL changes, even efficiency ones, happen without tests running. I also have a fully reproducible environment. So, I'd need to write migrations for DB changes, which are a hassle compared to changing a method. And I'd need to worry about versioning and compatibility between old and new running instances as they swap over. \$\endgroup\$
    – Nathan
    Commented Jul 10, 2020 at 10:39
  • 1
    \$\begingroup\$ It's fair, but it sounds like you have a different problem: DB migrations should not be a hassle, and if they are you need to revisit your migration procedure. \$\endgroup\$
    – Reinderien
    Commented Jul 10, 2020 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.