Xamarin SQLite - Type Blob (Guid)

[maxrodro]

Hi All,

Some good practice to work the GUIDs in the SQLite as Blob since they must be handled as TEXT in the SQLite queries

I appreciate your comments

[Mimetis]

Never use concatenated string in sqlite query like

var command = new SqliteCommand($"Select * from Client where Id='{clientId.ToString()}'");

Instead use parameterized query, like

var command = new SqliteCommand($"Select * from Client where Id=@clientId");
var clientIdParam = new SqliteParameter("@clientId");
clientIdParam.DbType = DbType.Guid;
clientIdParam.Value = clientId;
command.Parameters.Add(clientIdParam);

By the way, Guid in Sqlite are stored as blob but they are basically strings, so you can do something like:

select * from Customer where rowguid = '3F5AE95E-B87D-4AED-95B4-C3797AFCB74F'

[gentledepp]

@maxrodro
Having guids stored as blobs in sqlite may be faster, but it comes with compatibility issues (little endian, big endian crap)
That is why Microsoft dropped support for this in Microsoft.Data.SQLite

Since Dotmim.Sync uses Microsoft.Data.SQLite... you get the point ;-)

[maxrodro]

Hello,

Based on what has been explained I have an issue with the Linq, where the companyID is type Guid and in its comparison with the SQLite BLOB it does not find the record.

My code
public static readonly SQLiteAsyncConnection Database = new SQLiteAsyncConnection(DBPath);

var companyEntity = Database.Table<Models.Company>().FirstOrDefaultAsync(f => f.CompanyId == companyId);

companyEntity is null

f.CompanyId = BLOB
companyId = GUID

//Model
public class Company
{
public System.Guid CompanyId { get; set; } // CompanyId (Primary key)
public string Name { get; set; } // Name (length: 50)
}

But if I change my model from Guid to String and the companyId to string it works correctly

//Model
public class Company
{
public string CompanyId { get; set; } // CompanyId (Primary key)
public string Name { get; set; } // Name (length: 50)
}

[Mimetis]

What is the framework you are using to make LINQ queries with SQLite ? Dapper ? EF Core ?

[maxrodro]

[maxrodro]

Any ideas? :-)

[Mimetis]

will test it on monday

[Mimetis]

Hey @maxrodro
I guess I found the issue.

The problem is coming from the way sqlite-net-pcl and DMS (and under the hood Microsoft.Data.Sqlite) are storing Guid (blob or text or varchar) and the case sensitivity used (COLLATE NOCASE)

Depending on the server source (sql, mysql), Guid type can be serialized in uppercase or lowercase format.

So far:
If you are using sqlite-net-pcl to create your table, please consider adding the collation attribute to your class, like:

public class Company
{
[PrimaryKey, Collation("NOCASE")]
public System.Guid CompanyId { get; set; }
public string Name { get; set; } // Name (length: 50)
}

If you are using DMS to generate the table, I've fixed the Guid storage in Dotmim.Sync.Sqlite to store Guid as string.
You can clone the master branch and try by yourself or you can wait for the next beta release.

And finally, if your table is already generated, and you don't want to restart from scratch, well, open your database and alter your column manually.

Let me know if it fixed your issue !