OrmLite SqlLite StringConverter behavior change 5.4 => 5.6

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:

https://github.com/ServiceStack/ServiceStack.OrmLite/releases/tag/v5.4

And added your example converted to a unit test:

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:

Looking into this further, it looks like the issue is due to the SQLite provider used.

This always failed on .NET Framework but did pass on .NET Core because v5.4 used the Microsoft.Data.SQLite provider for .NET Core:

Whereas it switched to use the official multi-platform System.Data.SQLite.Core from SQLite:

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.

I’ve created a new ServiceStack.OrmLite.Sqlite.Data NuGet package on MyGet that uses Microsoft.Data.Sqlite. You can get this from MyGet:

https://docs.servicestack.net/myget