Not possible to map Guid to columns declared as BLOB with SQLite

[ErikEJ]

The test class (to be located in the SQLIte Functional Tests project) sums up the issue..
It is perfectly legal to declare a column as blob, and store Guids in this column as byte(16)
In fact there is no datatype called UNIQUEIDENTIFIER in sqlite.
Have no idea if this is an issue with EF or the SQLite ADO.Net provider also part of this project, ie the sqlite type map or what.

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq; 
using System.Text;
using System.Threading.Tasks;
using Xunit;

namespace Microsoft.Data.Entity.SQLite.FunctionalTests
{
public class SQLiteGuidDeclaredAsBlobTest
{
    [Fact]
    public void Can_Read_Guid_Key_Column_Created_As_BLOB_Using_AdoNet()
    {
        using (var connection = new Data.SQLite.SQLiteConnection("Filename=:memory:"))
        {
            using (var command = connection.CreateCommand())
            {
                CreateTestTable(connection, "BLOB");

                command.CommandText = "SELECT ArtistId FROM Artist";

                using (var reader = command.ExecuteReader())
                {
                    reader.Read();

                    Assert.Equal(new Guid("dc13b11c-e6fb-449f-a892-5e2a47b05350"), reader.GetGuid(0));
                }
            }
        }
    }

    [Theory]
    [InlineData("UNIQUEIDENTIFIER")]
    [InlineData("BLOB")]
    public void Can_Read_Guid_Key_Column_Created_As_BLOB_Using_EF7(string declaredType)
    {
        using (var db = new ChinookContext())
        {
            using (var connection = db.Database.AsRelational().Connection.DbConnection)
            {
                CreateTestTable(connection as Data.SQLite.SQLiteConnection, declaredType);
                var artist = db.Artists.First();
                Assert.Equal(artist.Name, "ABBA");
            }
        }
    }

    //HACK... just to demonstrate the issue:

    //public virtual T ReadValue<T>(int index)
    //{
    //  if (typeof(T).FullName == "System.Guid" && _valueBuffer[index].GetType().FullName == "System.Byte[]")
    //  {
    //      _valueBuffer[index] = new System.Guid(_valueBuffer[index] as System.Byte[]);
    //  }
    //  return (T)_valueBuffer[index];
    //}

    private static void CreateTestTable(Data.SQLite.SQLiteConnection connection, string declaredType)
    {
        connection.Open();
        connection.ExecuteNonQuery(@"
            CREATE TABLE Artist (
                ArtistId " + declaredType + @",
                Name TEXT
            )");
        connection.ExecuteNonQuery(@"
            INSERT INTO Artist VALUES (
                x'1cb113dcfbe69f44a8925e2a47b05350',
                'ABBA'
            )");
    }

}

internal static class DbConnectionExtensions
{
    public static int ExecuteNonQuery(this DbConnection connection, string commandText)
    {
        using (var command = connection.CreateCommand())
        {
            command.CommandText = commandText;

            return command.ExecuteNonQuery();
        }
    }
}

public class ChinookContext : DbContext
{
    public DbSet<Artist> Artists { get; set; }

    protected override void OnConfiguring(DbContextOptions options)
    {
        options.UseSQLite("Filename=:memory:");
    }
}

public class Artist
{
    public Guid ArtistId { get; set; }
    public string Name { get; set; }
}   

}

[bricelam]

Column type names mean nothing in SQLite. (See Determination Of Column Affinity for the only semantics applied to them.) In order to round-trip CLR types, we need to provide a mapping between CLR types and column type names. Our current default mapping is compatible with the mapping used in System.Data.SQLite's EF6 provider.

As a TODO item, we need to allow users to override our default mapping with their own.

The heart of the issue is that, if we see "BLOB" as the column type, how do we know whether to map that to a byte[] or Guid when the user calls DataReader.GetValue().

[bricelam]

Just one more problem caused by the ever-useless dynamic typing "feature".

[ErikEJ]

"Column type names mean nothing in SQLite"
I know, but they seem to mean something in EF, as you can see from the test - declaring the column
"UNIQUEIDENTIFIER" makes the scenario work.

Fixing the code

public byte[] ArtistId { get; set; }

And then doing conversion from byte[] <=> Guid via a NotMapped property also makes the scenario "work"

[ajcvickers]

The changes described in #736, #738, and #771 will probably be needed here (at least to avoid other similar issues), along with making sure that the query pipeline is using the generic ReadValue. This will flow the CLR type being requested down to the ADO.NET provider layer such that a Guid object can be created when there is a Guid in the model.

[ErikEJ]

@ajcvickers @bricelam Sounds promising, thanks!

[bricelam]

This appears to be fixed now.