RowVersion being added to select when service called internally

ServiceStack Auto Query
1

I have a simple GET service defined, which generates a different and incorrect SQL when I use it internally from C#. I am using MSSQL 2022 and ServiceStack 8.8.1 under MS Windows.

-- Correct: only uses AttachedId param
-- Called using Swagger

-- OrmLiteUtils_PrintSql
SQL: SELECT "Id", "AttachedId", "AttachedSource", "RichContent", "Closed", "DataEventRv", "DataEventId", "DataEventOn", "DataEventBy"
FROM "Attachment"."Note"
WHERE "Attachment"."Note"."AttachedId" = @0
PARAMS: @0=2ce5b8c5-cc9e-f011-a108-30138b721afe

-- Incorrect: should not be using DataEventRv param
-- Called internally in C# code

        using var db = autoQuery.GetDb<Note>(Request);
        var find = new GetNote { AttachedId = request.AttachedId };
	var q = autoQuery.CreateQuery(find, Request);
        var result = autoQuery.Execute(find, q, Request);

-- OrmLiteUtils_PrintSql
SQL: SELECT "Id", "AttachedId", "AttachedSource", "RichContent", "Closed", "DataEventRv", "DataEventId", "DataEventOn", "DataEventBy"
FROM "Attachment"."Note"
WHERE "Attachment"."Note"."AttachedId" = @0 AND "Attachment"."Note"."DataEventRv" = @1
PARAMS: @0=2ce5b8c5-cc9e-f011-a108-30138b721afe, @1=318001

using var db = autoQuery.GetDb<Note>(Request);
var find = new GetNote { AttachedId = request.AttachedId };
var q = autoQuery.CreateQuery(find, Request);
var result = autoQuery.Execute(find, q, Request);

The relevant definitions are:

// ServiceModel
[Route("/api/attachment/note", "GET")]
public class GetNote : QueryDb<Note>, IGet
{
    public Guid? Id { get; init; }
    public Guid? AttachedId { get; init; }
}

// Type
[Schema("Attachment")]
[DataContract]
public record Note : MriAuditBase
{
    [AutoId]
    [DataMember, Required]
    public Guid Id { get; init; }

    [DataMember, Required]
    public Guid AttachedId { get; init; }

    [DataMember, Required]
    public string AttachedSource { get; init; } = null!;

    [DataMember, Required]
    public string RichContent { get; set; } = null!;

    [IgnoreOnInsert, IgnoreOnUpdate]
    [DataMember, Required]
    public bool Closed { get; init; }

    [Alias("DataEventRv")]
    [DataMember, Required]
    public ulong RowVersion { get; init; }
}

// Audit relevant columns in tables
public interface IAudit
{
    byte DataEventId { get; set; }
    DateTime DataEventOn { get; set; }
    Guid DataEventBy { get; set; }
}

//Use as base record for Types persisted in audited database tables
public abstract record MriAuditBase : IAudit
{
    [Required] public byte DataEventId { get; set; }
    [Required] public DateTime DataEventOn { get; set; }
    [Required] public Guid DataEventBy { get; set; }
}

public enum DataEventType : byte
{
    Created  = 1,
    Modified = 2,
    Deleted  = 3
}

Any ideas why the DataEventRv is being used a select parameter?

2

The RowVersion column is how OrmLite/AutoQuery implements Optimistic Concurrency. It shouldn’t be aliased.

3

The RowVersion alias has worked for years in ServiceStack and it indicates optimistic concurrency problems correctly for updates and deletes.

I would not expect the RowVersion to be relevant for reading data, so I do not understand why it is being used in the AutoQuery SQL when I call the service using C#. But, not used when calling the service via swagger.

Any ideas would be appreciated, as this is now a major issue for my application.

4

Why is 318001 being queried? That filter has to come from somewhere.

5

I have no idea what is causing the 318001 to be added as a parameter when I call the GetNote using C#.

6

Something is causing it, try debugging it and see.

7

I resolved the issue, by changing the following C# line in the above C# snippet:

// adds RowVersion as query param
var q = autoQuery.CreateQuery(find, Request);

# RowVersion not used as query param
var q = autoQuery.CreateQuery(find, new(), Request, db);
1 Like
8

In that case I’m assuming the parameter was set on the HTTP Request.

Yeah you’ll typically want to pass the db connection so the query is executed with the same DB connection it was created with (needed when request is configured to use a different db connection).