Optimistic Concurrency RowVersion Property Results in Unexpected SQL

ServiceStack.OrmLite
1

We are introducing the RowVersion property on our classes to implement optimistic concurrency using a PostgreSql database. We would like for this property to also be added to our data transfer objects to maintain which version the client is working with when they make a PUT request. Using OrmLite and the CaptureSqlFilter I can see the special handling of the RowVersion property, such that Db.SingleById<MakeModel>(id) produces SQL with xmin AS "RowVersion".

Where we are seeing unexpected results is while attempting to join two tables together. In this case OrmLite generates SQL where it expects the RowVersion to be a column on the table.

The following is some example code I created to try and demonstrate the scenario I am describing. Here the OrmLite in the Get method of the VehicleService is producing the SQL below where makemodel.row_version is being selected. What I had anticipated to see is makemodel.xmin as RowVersion.

If you can help me identify if there’s something I’m missing or if there’s another way we should be using OrmLite to get the result we are after I’d appreciate the help.

[Alias("makemodel")]
public class MakeModel
{
  [Alias("id"), PrimaryKey]
  public int Id { get; set; }

  [Alias("manufacturer")]
  public string Manufacturer { get; set; }

  [Alias("model")]
  public string Model { get; set; }

  [References(typeof(PerformanceCategory))]
  [Alias("performance_category_id")]
  public string PerformanceCategoryId { get; set; }

  public ulong RowVersion { get; set; }
}

[Alias("performance_category")]
public class PerformanceCategory
{
  [Alias("id"), PrimaryKey]
  public int Id { get; set; }

  [Alias("category")]
  public string Category { get; set;}

  [Alias("description")]
  public string Description { get; set; }
}

[Route("/shop/vehicle/{Id}", "GET")]
[DataContract]
public class VehicleGetDto : DataTransferObject, IReturn<VehicleDto>
{
  [DataMember(Name = "id")]
  public string Id { get; set; }
}

[Route("/shop/vehicle/{Id}", "PUT")]
[DataContract]
public class VehiclePutDto : DataTransferObject, IReturn<VehicleDto>
{
  [DataMember(Name = "id")]
  public string Id { get; set; }

  [DataMember(Name = "manufacturer")]
  public string Manufacturer { get; set; }

  [DataMember(Name = "row-version")]
  public ulong RowVersion { get; set; }
}

[DataContract]
public class VehicleDto : DataTransferObject
{
  [DataMember(Name = "id")]
  public string Id { get; set; }

  [DataMember(Name = "manufacturer")]
  public string Manufacturer { get; set; }

  [DataMember(Name = "model")]
  public string Model { get; set; }

  [DataMember(Name = "performance_category_id")]
  public string PerformanceCategoryId { get; set; }

  [DataMember(Name = "category")]
  public string Category { get; set;}

  [DataMember(Name = "row-version")]
  public ulong RowVersion { get; set; }
}

public class VehicleService : ServiceBase
{
  public VehicleDto Get(VehicleGetDto dto) {
    return db.Single<VehicleDto>(db.From<MakeModel, PerformanceCategory>().Where(v => v.Id == id));
  }

  public VehicleDto Put(VehiclePutDto dto) {
    VehicleDto updatedVehicle;
    ...
    return updatedVehicle;
  }
}


SELECT "makemodel"."id" AS "id", "makemodel"."manufacturer" AS "manufacturer", "makemodel"."model" AS "model", "makemodel"."performance_category_id" AS "performance_category_id", "performance_category"."category" as "category", "makemodel"."row_version" FROM "makemodel" INNER JOIN "performance_category" ON("performance_category"."id" = "makemodel"."performance_category_id")WHERE ("MakeModel"."id" = :0)

Thank you,

Chad

2

Why is PerformanceCategoryId a string and a foreign key to the PerformanceCategory table with an int Id? Don’t make it a FK reference unless the types match.

Anyway this issue looks like it’s already resolved where the following code:

using (var db = OpenDbConnection())
{
    var id = 1;

    db.DropTable<MakeModel>();
    db.DropTable<PerformanceCategory>();
    db.CreateTable<PerformanceCategory>();
    db.CreateTable<MakeModel>();

    db.Insert(new PerformanceCategory { Id = 1, Category = "category" });
    db.Insert(new MakeModel { Id = 1, Manufacturer = "manufacturer", Model = "model", PerformanceCategoryId = 1 });

    var row = db.Single<VehicleDto>(db.From<MakeModel, PerformanceCategory>().Where(v => v.Id == id));

    db.GetLastSql().Print();

    Assert.That(row.Id, Is.EqualTo("1"));
    Assert.That(row.Category, Is.EqualTo("category"));
}

Correctly produces the following SQL:

SELECT "makemodel"."id" AS "id", "makemodel"."manufacturer" AS "manufacturer", "makemodel"."model" AS "model", "makemodel"."performance_category_id" AS "performance_category_id", "performance_category"."category" AS "category", makemodel."xmin" AS "RowVersion" 
FROM "makemodel" INNER JOIN "performance_category" ON
("performance_category"."id" = "makemodel"."performance_category_id")
WHERE ("makemodel"."id" = :0)

I’m not sure when it was fixed but if you’re not using the latest v4.5.12 try upgrading to that, otherwise it would be fixed with the current v4.5.13 that’s available on MyGet.