Issue with uppercase/lowercase GUID

[SSantiagoDev]

When your engine is generating a sql query containing a GUID, we can see that the GUID is uppercase (using the method ToQueryString()):

ex: .param set @__Id_Value_0 '3430B28F-C298-4294-AB74-D146C13DB605'

And that's ok when the db is created with your engine. So when a GUID is inserted, it will be uppercase as well in the db file. No issue...

BUT if you generate your sqlite db from a sql script with the official sqlite tool :

CREATE TABLE [Users] (
[Id] uniqueidentifier NOT NULL,
[Name] varchar NOT NULL,
, CONSTRAINT [PK_dbo.Users] PRIMARY KEY ([Id])
);

INSERT INTO [Users] ([Id], [Name]) VALUES ('3430b28f-c298-4294-ab74-d146c13db605', 'Me');

sqlite3.exe sqliteIssue.db < sqliteIssue.sql

Then the GUID is stored as lowercase in the db file! (open the db file with notepad)
So now if you copy the generated db to the bin folder of the following solution, you will see that we can't get the data because of this lowercase vs uppercase issue:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using static SQLitePCL.raw;

namespace SqliteGuidIssue
{
    class User
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    class Context : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=sqliteIssue.db"); //the file generated by the official tool based on the sql script provided above

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .Property(x => x.Id)
                .IsRequired();

            base.OnModelCreating(modelBuilder);
        }
    }

    class Program
    {
        static void Main()
        {
            var db = new Context();

            db.Database.OpenConnection();
            db.Database.EnsureCreated();

            // See what SQLite sees (inlines parameters)
            sqlite3_trace(
                ((SqliteConnection)db.Database.GetDbConnection()).Handle,
                (_, s) => Console.WriteLine(s),
                null);

            var userId = Guid.Parse("3430b28f-c298-4294-ab74-d146c13db605");

            var users = db.Users.ToList();
            Console.WriteLine($"Found: {users.Count()}\r\n");

            var myUsers = db.Users.Where(x => x.Id == userId).ToList();
            Console.WriteLine($"Found: {myUsers.Count()}");
        }
    }
}

Output:
_SELECT "u"."Id", "u"."Name"
FROM "Users" AS "u"
Found: 1

SELECT "u"."Id", "u"."Name"
FROM "Users" AS "u"
WHERE "u"."Id" = '3430B28F-C298-4294-AB74-D146C13DB605'
Found: 0_

And indeed, if I try to query manually my db with:

SELECT *
FROM [Users]
WHERE [Id] = '3430B28F-C298-4294-AB74-D146C13DB605'

It returns nothing. While with the lowercase GUID, no problem.

But if the db is created by your code, then the GUID is stored as uppercase in the db file, and then this works:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using static SQLitePCL.raw;

namespace SqliteGuidNoIssue
{
    class User
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
    }

    class Context : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=test.db");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .Property(x => x.Id)
                .IsRequired();

            modelBuilder.Entity<User>().HasData(new User[] {
                new User{Id=Guid.Parse("3430b28f-c298-4294-ab74-d146c13db605"),Name="Me"}
            });

            base.OnModelCreating(modelBuilder);
        }
    }

    class Program
    {
        static void Main()
        {
            var db = new Context();

            db.Database.OpenConnection();
            db.Database.EnsureCreated();

            // See what SQLite sees (inlines parameters)
            sqlite3_trace(
                ((SqliteConnection)db.Database.GetDbConnection()).Handle,
                (_, s) => Console.WriteLine(s),
                null);

            var userId = Guid.Parse("3430b28f-c298-4294-ab74-d146c13db605");

            var users = db.Users.ToList();
            Console.WriteLine($"Found: {users.Count()}\r\n");

            var myUsers = db.Users.Where(x => x.Id == userId).ToList();
            Console.WriteLine($"Found: {myUsers.Count()}");
        }
    }
}

Output:
SELECT "u"."Id", "u"."Name"
FROM "Users" AS "u"
WHERE "u"."Id" = '3430B28F-C298-4294-AB74-D146C13DB605'
Found: 1

Microsoft.EntityFrameworkCore.Sqlite 5.0.6
.NET 5
Windows 10

[SSantiagoDev]

To be even more complete, I would add the fact that the in memory filter works well (without this lowercase/uppercase issue):

            var inMemoryFilter = db.Users
                .ToList()
                .Where(x => x.Id == userId)
                .ToList();

[ajcvickers]

Duplicate of #19651?
/cc @bricelam

[bricelam]

You can work around the current state of things by using Collate:

var myUsers = db.Users
    .Where(x => EF.Functions.Collate(x.Id, "NOCASE") == userId)
    .ToList();