Why Entity Framework Core 7.0.2 for SQLite is returning nulls even tho the data exists?

I have been having a hard time trying to figure out why EF6 is not finding a record when I use the Id (which is currently a GUID) even tho the record exists.

The required field is as a String inside the SQLite database and in the model is a GUID. Let me share part of my code to make things more clear:

User Model:

public class User
{
    public Guid Id { get; set; } //Todo: Filter users by the Id
    public string Password { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public DateTime CreationDate { get; set; } = DateTime.UtcNow;
    public int AccountActive { get; set; } = 1;
    public Role Role { get; set; } = new Role();
}

The method inside the repo that I'm having problems with:

public User GetUser(string Id)
{
    if(Guid.TryParse(Id, out Guid result))
    {
        var user = _context.Users.Include(x => x.Role).SingleOrDefault(u => u.Id == result);
        if(user != null)
            return user;
    }

    throw new Exception("User not found!");
}

To me, it looks like EFCore is not able to parse the GUID back to a string to do the correct query. Trying out to see what is going on on the query side I was trying to output the query to the Debug by adding a new string to the appsettings.Development.json like this:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.Hosting.Lifetime": "Information",
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Default": "Data Source=Database/PTasks.db"
  }
}

The Output is showing much more information but no queries are been shown even after adding the option for sensitive data logging with optionsBuilder.UseSqlite(connectionString).EnableSensitiveDataLogging(true)

So far I have no clues on what is going on since this query has always worked for me over SQL Server, so I tend to think that this might be an issue with the use of EF6 and SQLite. As you can see I have been trying to inspect the Query but with no luck, so right now I'm out of ideas.

When I debug the method the var user = _context.Users.Include(x => x.Role).SingleOrDefault(u => u.Id == result); ends up as null even if the Id param was correctly parsed as a Guid and the Id exists in the database.

Just to add some extra information, I just changed the method to use RawSql and it is returning the expected data, but I really want to understand what is going on.

This is the new version:

public User GetUser(string Id)
    {
        if(Guid.TryParse(Id, out Guid result))
        {
            //var user = _context.Users.Include(x => x.Role).SingleOrDefault(u => u.Id == result);
            var user = _context
                 .Users
                 .FromSqlRaw($"select * from Users where Id = '{Id}'")
                 .SingleOrDefault();

            if(user != null)
                return user;
        }

        throw new Exception("User not found!");
    }

Of course, it is missing the join to the Roles table but that is not relevant to the issue.

So... I just found out this is indeed a Bug and it's been around since 2018. the issue arises when you have a PK in a table and that PK is a GUID when you try to filter by the PK, EF does not convert from Guid to String, so basically, the query will be wrong.

The workaround is simple but not very "elegant":

public User GetUser(string Id)
{
    if(Guid.TryParse(Id, out Guid result))
    {
        var user = _context
            .Users
            .Include(x => x.Role)
            .Single(u => u.Id.ToString().Equals(result.ToString()));

        if (user != null)
            return user;
    }

    throw new Exception("User not found!");
}

It works... but I hate this ToString() solution!