Why are my GUID type filters returning null?

Question

In my .NET Core application using Entity Framework Core I was using SQL Server but am shifting to SQLite. In SQLite GUID type filters are returning null:

public Guid GetAdminBranchId(Guid companyId)
{
   return _apiDbContext.CompanyBranches
                       .Where(a => a.CompanyId == companyId)
                       .FirstOrDefault();
}

I am using LINQ queries using Microsoft.EntityFrameworkCore.Sqlite version 8.0.7. If I use AsEnumerable() it works since subsequent operations in the LINQ query will be performed locally in memory:

var result1 = _apiDbContext.CompanyBranches
     .AsEnumerable()
     .FirstOrDefault(b => b.CompanyId == companyId);

But on a large dataset it will take lots of time.

Answer 1

Since SQLite stores GUID values as TEXT or BLOB, the comparison logic may not match properly.

If your IDs are GUIDs, the easiest solution is to convert the values into string format before performing the comparison. Something like:

public Guid GetAdminBranchId(Guid companyId)
{
    return _apiDbContext.CompanyBranches
        .Where(a => a.CompanyId.ToString() == companyId.ToString())
        .Select(a => a.Id)
        .FirstOrDefault();
}

Note:
The line .Select(a => a.Id) assumes that Id is the GUID field you want to return.

See the link : SQLite Data Types

Your second case works because of the .AsEnumerable(). Without .AsEnumerable(), the query is translated into SQL and executed on the SQLite database.

P.S It is costly operation.