SQLite: Lower-case Guid strings don't work in queries

[Leon99]

I use Microsoft.EntityFrameworkCore.Sqlite 3.1.1 in a testing project (with SqlServer used elsewhere). I expect it to be a (mostly) drop-in alternative. However, this doesn't work as expected with SQLite (but works fine with SqlServer and InMemory):

public IQueryable<Item> Query(Guid? tenantId, string? tag = null)
		{
			var entities = this.dbContext.Items
					.Include(a => a.Tags)
					.Where(a => tag == null || a.Tags.Any(at => at.TenantId == tenantId && at.Tag == tag))
               ...

entities is empty, while it's expected to contain some items.
Upon checking a whole bunch of Guid-related issues here, the only workaround I found requires changing OnModelCreating (adding .HasConversion<string>() for the property helps), which is not acceptable since the same code works with other providers. Therefore, I conclude that it's a bug in the provider.

Tag.TenantId is Guid?. Database was created using context.Database.EnsureCreated();. GUIDs are formatted as 00000000-0000-0000-0000-000000000000 (as per https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/types).

[ajcvickers]

@Leon99 I have not been able to reproduce this--see my code below. Please attach a small, runnable project or post a complete code listing that reproduces the behavior you are seeing.

public class Item
{
    public int Id { get; set; }
    public ICollection<ItemTag> Tags { get; set; }
}

public class ItemTag
{
    public int Id { get; set; }
    public Guid TenantId { get; set; }
    public string Tag { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger 
        = LoggerFactory.Create(c => c.AddConsole());//.SetMinimumLevel(LogLevel.Debug));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlite("DataSource=C:\\Stuff\\Test.db");
    }

    public IQueryable<Item> Query(Guid? tenantId, string tag = null)
    {
        return Items
            .Include(a => a.Tags)
            .Where(a => tag == null || a.Tags.Any(at => at.TenantId == tenantId && at.Tag == tag));
    }
    
    public DbSet<Item> Items { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        var tenantId = Guid.NewGuid();
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Item
            {
                Tags = new List<ItemTag>
                {
                    new ItemTag {Tag = "X", TenantId = tenantId}
                }
            });

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var results = context.Query(tenantId, "X").ToList();
            Console.WriteLine(results.Count);
        }
    }
}

[Leon99]

@ajcvickers thanks for your prompt response. Your example helped me to find what causes the issue. Guids in my DB were entered manually and represented in lowercase. As per https://tools.ietf.org/html/rfc4122#section-3 and https://www.itu.int/rec/dologin_pub.asp?lang=e&id=T-REC-X.667-201210-I!!PDF-E&type=items, GUIDs are meant to be output as lower case characters. .NET's Guid.ToString() follows that and outputs lower case characters. SQLite provider violates that by persisting them in upper case and converting query parameters to upper case. Is there a reason to do that? Is there a setting that I could use to avoid converting all my Guids to upper case when putting them into a test DB?

[xtremertx]

Hi, a few ideas

1. You could add an extra normalization column containing GUID in upper-case while also having a column with lowercase one (wasted space in db).

2. Use EF raw query with upper operator (https://www.techonthenet.com/sqlite/functions/upper.php). (could be efficient)

3. Do conversion in your app, if you have a large number of such guids in string type, you can use unsafe way as last resort, this rutine will do conversion to uppercase without any allocation (rewriting original string).

    public unsafe string ToUpperCaseInvariant(string s)
    {
        var handle = System.Runtime.InteropServices.GCHandle.Alloc(s, System.Runtime.InteropServices.GCHandleType.Pinned);
        try
        {
            var strPtr = handle.AddrOfPinnedObject();
            char* str = (char*)strPtr.ToPointer();

            // To uppercase by flipping bits (only lowercase strings will be converted)
            for (int i = 0; i < s.Length; i++)
            {
                if ((str[i] > 96) && (str[i] < 123)) str[i] ^= (char)0x20;
            }

            return s;
        }
        finally
        {
            handle.Free();
        }
    }

[Leon99]

Thanks @xtremertx, but none of those workarounds will work for me - it's a testing project and it's supposed to test the code/DB structure that works with Sql Server.

[bricelam]

Can you change the column collation to NOCASE?

CREATE TABLE ItemTag (
    TenantId TEXT NOT NULL COLLATE NOCASE
);

[bricelam]

Looks like SQLite 3.31.0 adds some UUID functions that output lower-case strings. They also add a standard BLOB format; I wonder if it's compatible with Guid.ToByteArray()

We should re-consider our defaults (again) in 5.0

[Leon99]

@bricelam I guess I could try the NOCASE collation, but I'm afraid that would incur a performance penalty as case-insensitive search usually slower than case-sensitive.

[ajcvickers]

Notes from team discussion:

• We should support the new SQLite GUIDs in ADO.NET and EF Core
• We should investigate mitigations for any breaking change. For example:
  • Auto-conversion of old values using a UDF?
  • A tool/command that will update a database to the new format?
  • Configuration of which mapping to use, probably at the EF level?