The upgrade from 5.4 to 5.6 seems to have broken our Sqlite database implementation due to a change in the behavior in the OrmLite.Converters.StringConverter class.
We use an Sqlite in-memory database for unit testing in a .NET Core 2.2 project using the ServiceStack.OrmLite.Sqlite.Core nuget package.
Sqlite does not have a Guid datatype, so Guids are stored in the database as a binary blob. When retrieving this data with an OrmLite query and casting the Guid to a string, we used a custom converter derived from OrmLite.Converters.StringConverter, which would recognize the datatype as a byte array, which we would then convert to a Guid and return the Guid.ToString().
After upgrading to 5.6, this no longer works. The Guid data from the database is now passed into the converter as a string instead of a binary, looking something like â|eX��jG��Q�����â.
Is this change intended, and if it is, how can we get this previous behavior in 5.6?
Here is a unit test showing our problem. This passes when using ServiceStack.OrmLite.Sqlite.Core 5.4, and fails when using ServiceStack.OrmLite.Sqlite.Core 5.6.
using System;
using System.Linq;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Converters;
namespace ConverterIssueTest
{
public static class Example
{
[Test]
public static void PerformExample()
{
var dialectProvider = SqliteDialect.Provider;
dialectProvider.RegisterConverter<string>(new MySqliteStringConverter());
var factory = new OrmLiteConnectionFactory(":memory:", dialectProvider);
var uuid = Guid.NewGuid();
using (var db = factory.Open())
{
db.CreateTable(false, typeof(DbPoco));
db.Insert(new DbPoco { Id = 1, UniqueId = uuid });
var result = db.Single<Dto>(db.From<DbPoco>().Where(poco => poco.UniqueId == uuid));
Assert.That(result.UniqueId, Is.EqualTo(uuid.ToString()));
}
}
}
/// <summary>
/// ORM data object for the item.
/// </summary>
[Alias("location")]
public class DbPoco
{
[Alias("poco_id"), PrimaryKey, AutoIncrement]
public int Id { get; set; }
[Alias("poco_uuid")]
public Guid UniqueId { get; set; } = Guid.NewGuid();
}
/// <summary>
/// Example Dto with Guid as a string.
/// </summary>
public class Dto
{
public string Id { get; set; }
public string UniqueId { get; set; }
}
/// <summary>
/// This converter ensures that GUIDs can be converted from the byte array format that SQLite is
/// storing them in to a correct string format.
/// </summary>
public class MySqliteStringConverter : StringConverter
{
public override object FromDbValue(Type fieldType, object value)
{
object result;
if (value is byte[] byteaValue)
{
var res = new Guid(byteaValue).ToString();
result = res;
}
else
{
result = base.FromDbValue(fieldType, value);
}
return result;
}
}
}
The problem is trying to coerce the Guid into a string in SQLite. So if your DTO was a Guid:
public class Dto
{
public string Id { get; set; }
public Guid UniqueId { get; set; }
}
It will work because it will be able to use the SqliteGuidConverter.cs where it will use reader.GetGuid(columnIndex) to fetch the Guid which only SQLite needs to do because it doesnât let you call the generic and optimal batched GetValues() or generic GetValue() API to fetch Guid values as it will return it in a corrupted string (Iâm assuming is a UTF-8 binary string value).
Because of SQLiteâs behavior I donât see how this couldâve worked so Iâve downloaded the v5.4 release:
using System;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite.Converters;
namespace ServiceStack.OrmLite.Tests.Issues
{
public class StringConverterGuidIssue : OrmLiteTestBase
{
[Alias("location")]
public class DbPoco
{
[Alias("poco_id"), PrimaryKey, AutoIncrement]
public int Id { get; set; }
[Alias("poco_uuid")]
public Guid UniqueId { get; set; } = Guid.NewGuid();
}
/// <summary>
/// Example Dto with Guid as a string.
/// </summary>
public class Dto
{
public string Id { get; set; }
public string UniqueId { get; set; }
}
public class MySqliteStringConverter : StringConverter
{
public override object FromDbValue(Type fieldType, object value)
{
object result;
if (value is byte[] byteValue)
{
var res = new Guid(byteValue).ToString();
result = res;
}
else
{
result = base.FromDbValue(fieldType, value);
}
return result;
}
}
[Test]
public void Does_convert_Guid_with_Custom_String_Converter()
{
var dialectProvider = SqliteDialect.Provider;
dialectProvider.RegisterConverter<string>(new MySqliteStringConverter());
var dbFactory = new OrmLiteConnectionFactory(":memory:", dialectProvider);
var uuid = Guid.NewGuid();
using (var db = dbFactory.Open())
{
db.CreateTable(false, typeof(DbPoco));
db.Insert(new DbPoco { Id = 1, UniqueId = uuid });
var result = db.Single<Dto>(db.From<DbPoco>().Where(poco => poco.UniqueId == uuid));
Assert.That(result.UniqueId, Is.EqualTo(uuid.ToString()));
// Assert.That(result.UniqueId, Is.EqualTo(uuid));
}
}
}
}
And it has the same behavior as v5.6.
To resolve this issue youâll need to change reading SQLite Guid values as a Guid so OrmLite uses the SqliteGuidConverter.cs to retrieve Guid values with reader.GetGuid(). Otherwise in your custom string converter youâll need to override GetValue() and use some custom heuristic to detect Guid values like reader.GetName(i) == "UniqueId" and use reader.GetGuid() to fetch Guid values:
So this issue is inherent in the underlying SQLite ADO.NET provider.
I can look into changing the .NET Core only ServiceStack.OrmLite.Sqlite.Core NuGet package to use Microsoft.Data.SQLite as I prefer the same ServiceStack.OrmLite.Sqlite NuGet Package used the same driver for both .NET Core/NET FX for consistent implementation.
This thread explains the behavior and issue with Guids between the different SQLite providers. Are you using the ServiceStack.OrmLite.Sqlite.Data provider which uses the Microsoft.Data.SQLiteADO.NET provider? If you can confirm its an issue with this provider please provide a stand-alone repro on GitHub that repros the issue.
I had both the old and new providers in our solution. This clearly was causing a conflict. After removing the old provider and rerunning our tests, we exposed a different problem. It âappearsâ the Guid values are being handled properly with OrmLiteâs native APIs. However, when we use Dapperâs QueryMultipleAsync, the results are always empty. This works as expected when using the MySql provider (used for production).
Before I dig deeper into the problem, are you aware of any conflict with the new provider and the Dapper extensions?
The different SQLite providers have different behaviors, which needs to be coded differently to handle them, so itâs likely Dappers single implementation doesnât handle supporting both providers.
The issue is only with the different SQLite providers, other RDBMSâs doesnât have the same issues SQLite has with Guids.
We are going to remove the few Dapper queries we have and replace them with native OrmLite counterparts. Given our preliminary testing, that should solve the problem.