Query: Invalid SQL generated for Guid literals on SQLite

[ajcvickers]

This works on SQL Server, but not on SQLite.

public class GuidKey
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Id { get; set; }

    public string Foo { get; set; }
}

public class FindContext : DbContext
{
    public DbSet<GuidKey> GuidKeys { get; set; }
}

[Fact]
public virtual void Find_guid_key()
{
    using (var context = new FindContext())
    {
        context.Add(
            new GuidKey 
            {
                Id = new Guid("{3FDFC4F5-AEAB-4D72-9C96-201E004349FA}"), 
                Foo = "Smokey" 
            });

        context.SaveChanges();
    }

    using (var context = new FindContext())
    {
        var entity = context.GuidKeys.FirstOrDefault(
            e => e.Id == new Guid("{3FDFC4F5-AEAB-4D72-9C96-201E004349FA}"));

        Assert.Null(entity); // Not found

        entity = context.GuidKeys.ToList().FirstOrDefault(
            e => e.Id == new Guid("{3FDFC4F5-AEAB-4D72-9C96-201E004349FA}"));

        Assert.NotNull(entity); // Found by LINQ to Objects
    }
}

PRAGMA foreign_keys=ON;

SELECT "e"."Id", "e"."Foo"
FROM "GuidKey" AS "e"
WHERE "e"."Id" = '3fdfc4f5-aeab-4d72-9c96-201e004349fa'
LIMIT 1

[ajcvickers]

Note that this works, but only because it is client-evaluated:

var entity = context.GuidKeys.FirstOrDefault(
    e => e.Id.ToString() == new Guid("{3FDFC4F5-AEAB-4D72-9C96-201E004349FA}").ToString());

[natemcmaster]

Related to aspnet/Microsoft.Data.Sqlite#191

[ajcvickers]

@natemcmaster Related maybe, but in this case the database was created by EF and the query is fully in the form of Guid objects, not strings.

[bricelam]

That literal is wrong. It should be x'f5c4df3fabae724d9c96201e004349fa'. Anywhere else we inline Guid values is also broken.

[bricelam]

There's another instance of of this on StackOverflow: How to linq filter on multiple GUID at once generating an appropriate sql where clause