TransactionLog entity does not contain the entityId

[thoraj]

Since the transaction log can become quite long, it should be possible to query for transactions by the entity id.

Is there a reason EntityId is not part of the TransactionLog type?

Are there other ways of filtering (in the db query) on entitytype (the id is in the serialized json, but that is not queryable).

[gnaeus]

@thoraj, we can determine and extract primary key from entity during TransactionLog creation.
The problem is that primary key can be composite:

public class MyEntity
{
    [Key]
    public int IntKey { get; set; }

    [Key]
    public string StringKey { get; set; }
}

And since we store all TransactionLog rows in single table, in general case, the primary key for entity is JSON again. So we do't have any benefit if we store this key in separate field.

But we can query EntityJson field with Full Text Search (SQL Server):

CREATE FULLTEXT INDEX ON dbo.TransactionLogs (
    EntityJson LANGUAGE 1033 -- English
)
KEY INDEX PK_dbo.TransactionLogs
WITH STOPLIST = OFF;

EF Core:

public List<TransactionLog> GetMyEntityLogs(int intKey, string stringKey)
{
    return context.TransactionLogs
        .FromSql($@"
            SELECT * FROM dbo.TransactionLogs
            WHERE EntityType = '{typeof(MyEntity).AssemblyQualifiedName}'
              AND CONTAINS(EntityJson, '""{nameof(MyEntity.IntKey)}"":{intKey},')
              AND CONTAINS(EntityJson, '""{nameof(MyEntity.StringKey)}"":""{stringKey}""')")
        .ToList();
}

And with this pattern you can search not only by primary keys, but also by any other field.

[thoraj]

Thanks for clarifying.

I see that if the transactionlog shall be a single table (row per transaction) we still have to handle json somehow when handling the entity key.

It still feels a little dirty to use a full text search on the entire EntityJson. Keeping a (json) copy of the key in a separate column would be a bit cleaner.

For one it would make quering by entitykey simpler for postgres (which has support for json queries).

[gnaeus]

@thoraj, data for primary key already exists in EntityJson. So in Postgres we don't need Full Text Search. We can use such index and query:

CREATE INDEX IX_MyEntities ON TransactionLogs (((EntityJson ->> 'IntKey')::int))
WHERE TableName = 'MyEntities';

SELECT * FROM TransactionLogs
WHERE TableName = 'MyEntities'
  AND (EntityJson ->> 'IntKey')::int = @intKey;

In SQL Server and MySQL we can use computed columns:

ALTER TABLE dbo.TransactionLogs
ADD MyEntityKey AS (
  CASE
    WHEN TableName = 'MyEntities'
    THEN CONVERT(int, JSON_VALUE(EntityJson, '$.IntKey'))
    ELSE NULL
  END
);

CREATE INDEX IX_MyEntities ON dbo.TransactionLogs (MyEntityKey)
WHERE TableName = 'MyEntities';

SELECT * FROM dbo.TransactionLogs
WHERE TableName = 'MyEntities'
  AND MyEntityKey = @intKey;