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.

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.

• sqlite
• entity-framework
• linq

Share Improve this question Follow edited Nov 19, 2024 at 23:33 user4157124 2,9961414 gold badges3131 silver badges4646 bronze badges asked Nov 19, 2024 at 5:50 ZoraizZoraiz 2366 bronze badges 3

• I am not an expert, but perhaps this, this, or one of the articles here or here might help. A couple of pages suggest changing BinaryGUID=True to BinaryGUID=False in the connection string. This may depend on whether GUIDs are stored as text or binary values in your database. – T N Commented Nov 19, 2024 at 7:39
• Thank you for your response, tried adding BinaryGUID=True in connection sting but i got no success as now it is unable to connect to database – Zoraiz Commented Nov 19, 2024 at 11:05
• It would be binaryguid=False; that you would want, to tell SQLLite to store GUIDs as Text rather than Blob. The data type on the underlying tables would need to be updated to CHAR(36) instead of BLOB. Ideally unless there is a good reason to use UUIDs for keys you can save yourself a lot of headache and performance by using sequences. UUIDs are typically used for systems that synchronize inserts across multiple databases. If that is not a requirement then they offer more disadvantage than advantage. – Steve Py Commented Nov 19, 2024 at 21:12

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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.