Automatically trim trailing spaces from query join clause with AsTracking - EF Core 7

Question

I have two classes (Item and Location)

public class Item
{
    public string Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Location> Locations { get; set; }
}

public class Location
{
    public string Id { get; set; }
    public string Rack { get; set; }
    public string IdItem { get; set; }
    public virtual Item Item { get; set; }
}

I have a foreign key between these tables with Item.Id and Location.ItemId. On Item table, the Id column have spaces after the Id (example : '123 '). But on the Location table, on the Item column, I have no space after the Id (example: '123').

When I make queries with AsNoTracking() the trimmed join works as expected (thanks to SQL-92 ANSI/ISO), however it doesn't work with AsTracking() and the relation becomes empty. The two generated queries are identical, EF Core doesn't seem to be able to deserialize the SQL data from relations in a tracking scenario.

//Locations are returned (Locations.Count > 0)
var Locations = db.Item.AsNoTracking()
    .Include(i => i.Locations)
    .FirstOrDefault(i => i.Id == "123")?.Locations;

//Locations are not returned (Count = 0)
var Locations = db.Item
    .Include(i => i.Locations)
    .FirstOrDefault(i => i.Id == "123")?.Locations;

Is it a bug in EF Core? Is there any workaround?

Answer 1

Sounds like a bug, so please go and report it to EF Core GitHub issue tracker.

Meanwhile, you can use the following workaround which utilizes a custom ValueComparer inside your OnModelCreating override:

var trimmingStringEndValueComparer = new ValueComparer<string>(
    (v1, v2) => v1 == null ? v2 == null : v2 != null && v1.TrimEnd() == v2.TrimEnd(),
    v => v.TrimEnd().GetHashCode());

modelBuilder.Entity<Item>().Property(e => e.Id)
    .Metadata.SetValueComparer(trimmingStringEndValueComparer);