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

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).

About this issue

Original URL
State: open
Created 4 years ago
Reactions: 1
Comments: 24 (9 by maintainers)

Most upvoted comments

To mitigate future breaking changes in this area, I’d recommend converting all the database values to lowercase and using a value converter.

UPDATE MyTable
SET GuidColumn = lower(GuidColumn);

modelBuilder
    .Entity<MyEntity>()
    .Property(e => e.GuidProperty)
    .HasConversion(
        g => g.ToString(),
        s => new Guid(s));

bricelam on Nov 9, 2020