Dapper: Microsoft.Data.Sqlite and Guid column

[tidyui]

Hi there! I'm using dapper in my .net core library and have previously tested with SqlServer & LocalDb. I just switched to Sqllite and Guid id columns seem to not be working with the message:

Unable to cast object of type 'System.Byte[]' to type 'System.Guid'

See full stack trace below:

InvalidOperationException: Error parsing column 0 (Id=System.Byte[] - Object)
  Dapper.SqlMapper.ThrowDataException(Exception ex, int index, IDataReader reader, object value)
  Deserialize45afa75f-34ca-4996-b0bd-653743fbf96d(IDataReader )
  Dapper.SqlMapper+<QueryImpl>d__124.MoveNext()
  System.Collections.Generic.List..ctor(IEnumerable<T> collection)
  System.Linq.Enumerable.ToList<TSource>(IEnumerable<TSource> source)
  Dapper.SqlMapper.Query<T>(IDbConnection cnn, string sql, object param, IDbTransaction transaction, bool buffered, Nullable<int> commandTimeout, Nullable<CommandType> commandType)

Regards

[mgravell]

This is a more nuanced one than you would think; historically, indeed Dapper has let the downstream provider choose the types that it returns. Most ADO.NET providers know how to identify and cast a GUID column from their data store. We are aware that some providers don't do this correctly. We investigated automatically casting between GUID and byte[] in the cases where it didn't happen, but then another problem came up: endianness. It turns out that different backend stores use different endianness to store GUIDs. This means that there is no reliable endianness choice that Dapper can choose or infer that will work correctly in all cases. Because of this I'm not happy for dapper to provide a probably-wrong conversion.

so: you'll either need to use a provider that understands guids, or use byte[] and perform your own conversion (checking the results)

[tidyui]

I think I'll take a look at how EntityFrameworkCore handles it (as I'm migrating the codebase from EF to Dapper). I did a first test at changing the column type in CREATE TABLE from UNIQUEIDENTIFIER to NVARCHAR(64), however the problem still remains.

There's no problem saving the models with the Guid property with Dapper, however Microsofts SQLite provider still converts the value to an octet-stream regardless of column type. The problem occurs when deserializing the value, as Dapper still gets back a byte[] even though the actual column type is NVARCHAR.

The simple solution would of course be to change all models to have a string Id and just initialize them with Id = Guid.NewGuid().ToString(), but how much fun would that be ;)

[darkurse]

@tidyui, I had a quick look at how EF core does it because I need this feature too. This is what I can tell from the code ( I am not a EF Core expert so please correct me if I am wrong ) :

1. Unique identifiers with Guid type are mapped to sqlite 'BLOB' types, not 'TEXT'
2. To write the Guid to db, it needs to convert it to a x'some-hex-value' string value for the insert/update sql query. EF does the mapping and then creates the value actually used for the projected sql query
3. To read the Guid, it uses this DbDataReader and this mapping but honestly from there I am not sure how he does it and I don't see anything specific to read Guid for Sqlite.
4. Guid.ToByteArray() is used for the convertion from Guid to byte[]

Not too sure where to go from there though. I might use byte[] and perform my own conversion as suggested by @mgravell .

[tidyui]

Yeah, I actually just changed all my GUIDS to NVARCHAR(36). The result is the same, the id is never changed so there's no need for re-indexing and all Guid's were generated by the repositories anyway :)

[darkurse]

I can't do that unfortunately. I have too many Guid around and has deep implications in my code AND my database. Guid as a string are stored in more bytes than it does for a blob !!

[ethanli83]

Hi @tidyui & @darkurse,

Have you guys tried SqlMapper.TypeHandler already? It seems working for me.

// first add this class to your project
public class GuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override Guid Parse(object value)
    {
        var inVal = (byte[])value;
        byte[] outVal = new byte[] { inVal[3], inVal[2], inVal[1], inVal[0], inVal[5], inVal[4], inVal[7], inVal[6], inVal[8], inVal[9], inVal[10], inVal[11], inVal[12], inVal[13], inVal[14], inVal[15] };
        return new Guid(outVal);
    }

    public override void SetValue(System.Data.IDbDataParameter parameter, Guid value)
    {
        var inVal = value.ToByteArray();
        byte[] outVal = new byte[] { inVal[3], inVal[2], inVal[1], inVal[0], inVal[5], inVal[4], inVal[7], inVal[6], inVal[8], inVal[9], inVal[10], inVal[11], inVal[12], inVal[13], inVal[14], inVal[15] };
        parameter.Value = outVal;
    }
}

// Then you add follow line at where you app beigns to run.
SqlMapper.AddTypeHandler<Guid>(new GuidTypeHandler());

I found it in this stackoverflow question: http://stackoverflow.com/questions/25568657/dapper-typehandler-setvalue-not-being-called;

[supersonicclay]

@ethanli83 my experience is that TypeHandler works with C# Guid when Dapper converts the db type to C# type:

var x = connection.Query<ClassContainingAGuidProperty>("SELECT GuidColumnFromDb")

But it doesn't work when using Guid as a parameter like this:

connection.Execute("INSERT INTO MyTable (@guidValue)", new { guidValue = Guid.NewGuid() })

[darkurse]

Hi @tidyui ,

interesting but I was worried of the endianness aspect. So we ended up using the official sqlite System.Data.SQLite.Core assembly which allows - via the connection string - to manage Guid as a binary object :

To get a quick idea :

``` C#
using System.Data.SQLite;

[...]

SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder() {
DataSource = "my.db",

// from doc : Determines how GUIDs are stored.
// If true - GUID columns are stored in binary form,
// If false - GUID columns are stored as text
BinaryGUID = true
}

using (System.Data.IDbConnection db = new SQLiteConnection(builder))
{
var result = connection.Query("the query", theFilter());
}
```

[NickCraver]

This is currently a blocker for me in MiniPofiler.Providers.Sqlite as well. I can't use Microsoft.Data.Sqlite cleanly. This relates to #227 as well, since that would work here with most providers. Perhaps we should take another look at using the .Get____() methods on ADO.NET (for Guid and others)?

@mgravell thoughts? I know of the problems with for example .GetChar(), but I'd imagine GetGuid is far more stable. If we special cased here as we do for GetChar(), we may be in a better place overall. I can give this a go with some tests soon as time allows.

[NickCraver]

We've talked to the EF team about this while on campus last time, the TL;DR is: it sucks. GUID support across providers is a bad situation. But there's no consensus about where to paper over it either. I'm personally of the opinion ADO.NET should be where this happens (e.g. in the Get___() methods), but can absolutely see it both ways. I'll update if things change here, but at the moment all I have is discussion news.

[taimuralam]

After battling with this issue, I got around by doing something like this :-

GuidHandler : SqlMapper.TypeHandler<Guid>

[koskedk]

When using SqlMapper.TypeHandler. I experienced the wrong output.

Initially using as is from @ethanli83 suggestion.

Original Guid value : when saving data was
1dfa344a-ba82-4d07-903c-aa94009255f9

The result from Dapper query it somehow changed to:
4a34fa1d-82ba-074d-903c-aa94009255f9

I had to change to this for correct result

 public override Guid Parse(object value)
        {
            var inVal = (byte[])value;
            return new Guid(inVal);
        }

After changing it to this. I got the desired result. For anyone who experienced this.

[ethanli83]

hi @koskedk ,

Thank you for pointing it out. Seems like the StackOverflow answer got the index in wrong order. Instead of

byte[] outVal = new byte[] { inVal[3], inVal[2], inVal[1], inVal[0], inVal[5], inVal[4], inVal[7], inVal[6], inVal[8], inVal[9], inVal[10], inVal[11], inVal[12], inVal[13], inVal[14], inVal[15] };

it should probably be:

byte[] outVal = new byte[] { inVal[0], inVal[1], inVal[2], inVal[3], inVal[4], inVal[5], inVal[6], inVal[7], inVal[8], inVal[9], inVal[10], inVal[11], inVal[12], inVal[13], inVal[14], inVal[15] };

Your solution makes more sense.

[AdamJachocki]

It is also blocker for me. Everything works fine in System.Data.Sqlite, but in MS one I am getting more and more errors :(

[taimuralam]

@AdamJachocki what is the error you getting? Whats an MS? Mind sharing the part of the code producing error..

[AdamJachocki]

Using: Dapper and Microsoft.Data.Sqlite (MS stands for Microsoft in previous comment)
The error: "Invalid cast from 'System.String' to 'System.Guid'.
"Error parsing column 0 (Id=98A28602-08EB-4E52-9F5C-40BDC822E93B - String)"

How I insert into db:

string sql = @"INSERT into documents(Id, name, owner_id)
        VALUES(@id, @name, @owner_id)";

if(doc.Id == Guid.Empty)
    doc.Id = Guid.NewGuid();

var @params = new { id = doc.Id, name = doc.Name, owner_id = doc.Owner.Id };
int result = await conn.ExecuteAsync(sql, @params, activeTransaction);

How I get:

string sql = @"SELECT doc.*, u.* 
                FROM documents doc
                LEFT JOIN users u on u.Id = doc.Owner_id 
                WHERE doc.Id = @docId";

var documents = await db.QueryAsync<Document, SystemUser, Document>(sql,
    (doc, su) =>
    {
        doc.Owner = su;
        return doc;
    },
    new { docId });

QueryAsync is:

var result = await conn.QueryAsync(sql, map, parameters);
return result;

conn is SqliteConnection (Microsoft.Data)

My SqliteDatabase schema is created using EfCore 3 migration system.
Every ID is of type Guid.

After adding GuidHandler like showed in previous comments, Guid started to work, but DateTime still doesn't work - error is the same. Cannot convert from string. I think this is more Microsoft.Data.Sqlite problem (or EfCore migrations), because everythink worked on nHibernate migrations and System.Data.Sqlite.

[taimuralam]

@AdamJachocki In my implementation of C# using dapper against both sql server and sqlite was to make my schema by hand. Was tedious but had more control. It really depends on what type your column is and what you're pushing in it. You can try making a DateTime handler too but I think it will be an overkill.

I remember having to make GuidHandler explicitly converting Guids to and fro the database. Since I had stored Guids as characters/strings in database. However, i don't remember doing any explicit work against DateTimes. They simply worked.

What is the Type of Connection you're using for SqlLite? Is it inserts only that is failing or is it the entire CRUD with DateTimes that fails? Can you inspect the raw sql that gets executed?

[AdamJachocki]

I can see that this is EfCore problem. EfCore gets data type from model, not from created migration. And since Sqlite doesn't support DateTimeOffset, Microsoft decided to create column of TEXT type instead of DATETIME.

[AdamJachocki]

Now GUID with Microsoft.Data.Sqlite seems not to be working at all. Look at this simple code:

SystemUser u2 = new SystemUser(su);
u2.Id = Guid.NewGuid();

await db.SaveAsync(u2);
SystemUser u2From = await db.GetAsync<SystemUser>(u2.Id);

This should write u2 to database and read it - as you can see I am using the same user id.
Expected result - u2From should be valid object.
Actual result: u2From is null, as there is no record in database (according to Ms.Data.Sqlite) that meets the conditions.
This is very big blocker.

I am using Dapper for my database operations. Save and get methods are really simple:

Get looks like that:

string sql = $"SELECT * FROM {tableName} WHERE Id = @id";
var result = await conn.QueryAsync<T>(sql, new { id });

where id is of object type

Save is just INSERT INTO statement run on IDbConnection.ExecuteAsync.
Save seems to be working fine - the record is saved in the database.

I also have my guid mappings, I tried the both solutions from previous posts.