Hi again.
I would like to revisit an issue that I discovered earlier with Dapper/RowVersion (WHERE IN params for custom / raw sql fail?), as it’s looking more serious.
Specifically, the use of ulong for Ormlite RowVersion functionality means I am subsequently unable to use any of the embedded Dapper functionality to perform complex queries (I am particular interested in the QueryMulti logic, as per the attached demo), because of a resulting error:
System.InvalidOperationException: 'Error parsing column 2 (RowVersion=System.Byte[] - Object)'
Inner Exception: InvalidCastException: Object must implement IConvertible.
You suggested that perhaps including the RowVersion column explicitly in my query params rather than generically might get around the issue
(select t.RowVersion vs select t.*) but unfortunately it seems that that isn’t the case.
I looked at the SqlServer2012OrmLiteDialectProvider and considered trying to implement my own SqlRowVersion converter using a rowversion of type byte[] rather than ulong, but ulong is baked in quite heavily to the logic, so I doubt that’s a good/possible approach?
So basically, it seems that we am stuck either:
a) able to use Ormlite with RowVersion / optimistic concurrency checking functionality but lose the ability/flexibility of Dapper-based custom queries (which is likely to be a big problem going forward), or
b) regain Dapper/custom SQL by ditching the RowVersion capability (which is also going to be technically/politically awkward for this project).
Does anyone have any suggestions / workarounds? As it is, we’re kind of stopped without a clear way forward.
Thanks in advance
Sample Demo:
using System;
using System.Collections.Generic;
using System.Linq;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Dapper;
using ServiceStack.OrmLite.SqlServer;
namespace ConsoleApp1
{
internal class Program
{
private static void Main(string[] args)
{
var connectionString = "Server=localhost;Database=debug.NUKEABLE;User Id=sa; Password=mypass;";
var fac = new OrmLiteConnectionFactory(connectionString, SqlServer2012OrmLiteDialectProvider.Instance);
using (var db = fac.OpenDbConnection())
{
db.DropTable<ChildTbl>(); db.DropTable<ParentTbl>();
db.CreateTable<ParentTbl>(); db.CreateTable<ChildTbl>();
var parentTbl = new ParentTbl { ParentId = Guid.NewGuid(), Name = "a",
Children = new List<ChildTbl>
{
new ChildTbl { ChildId = Guid.NewGuid(), Name = "a", StartDate = DateTime.Today.AddDays(3) },
new ChildTbl { ChildId = Guid.NewGuid(), Name = "b", StartDate = DateTime.Today.AddDays(2) },
new ChildTbl { ChildId = Guid.NewGuid(), Name = "c", StartDate = DateTime.Today.AddDays(1) }
} };
db.Save(parentTbl, true);
var complexMultiQueryThatIWantToUseWithDapperQueryMulti = $@"
select P.ParentId, P.Name, P.RowVersion, C.ChildId, C.ParentId, C.Name, C.StartDate, C.RowVersion
From ChildTbl C
join ParentTbl P on P.ParentId = C.ParentId
INNER JOIN
(
select C.ParentId, Min(C.StartDate) as MinStartDate
from ChildTbl C
Group BY C.ParentId
) as GRP
ON GRP.MinStartDate = C.StartDate and GRP.ParentId = C.ParentId";
using (var multi = db.QueryMultiple(complexMultiQueryThatIWantToUseWithDapperQueryMulti))
{
// This line will crash because of inability to parse RowVersion
var results = multi.Read<ParentTbl, ChildTbl,
Tuple<ParentTbl, ChildTbl>>(Tuple.Create,
splitOn: $"{nameof(ParentTbl.ParentId)}, {nameof(ChildTbl.ChildId)}"
).ToList();
foreach (var res in results)
{
// do stuff but we don't get this far
}
}
// As a further example, using Dapper but without rowversion column WILL work
var thisDapperQryWorks = db.Query<ParentTbl>("select ParentId, Name from [ParentTbl]");
// But any time RowVersion as ulong is include... no joy
var thisDapperQueryFails = db.Query<ParentTbl>("select ParentId, Name, RowVersion from [ParentTbl]");
Console.WriteLine("hit a key to end test");
Console.ReadLine();
}
}
}
public class ParentTbl
{
[PrimaryKey]
public Guid ParentId { get; set; }
public string Name { get; set; }
// Use of ulong makes embedded Dapper functionality unavailable
[RowVersion]
public ulong RowVersion { get; set; }
[Reference]
public List<ChildTbl> Children { get; set; } = new List<ChildTbl>();
}
public class ChildTbl
{
[PrimaryKey]
public Guid ChildId { get; set; }
[References(typeof(ParentTbl))]
public Guid ParentId { get; set; }
public string Name { get; set; }
// Use of ulong makes embedded Dapper functionality unavailable
[RowVersion]
public ulong RowVersion { get; set; }
public DateTime StartDate { get; set; }
}
}