Using guid in sqlite select where guid is stored in the sqlite db as binaries

Question

I have a table Employee in the SQLite database. Whose primary key is "ID" of GUID type. I try to find the record with id = guid 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3' with the following query with no luck:

SELECT * FROM Employee
WHERE Employee.Id = 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3'

Can anyone tell me how should I write the query?

Thanks,

Answer 1

This is a old question, but comes up with searches and while the selected answer is correct, it might not work for everyone easily. For example when using .NET bindings to SQLite.

The reason is that when the GUID is in format a8828ddf-ef22-4d36-935a-1c66ae86ebb3 you cannot just remove the dashes and put it to X'...'. The GUID is in several parts and the database is storing the binary differently.

When the GUID is a8828ddf-ef22-4d36-935a-1c66ae86ebb3 it will be stored in little endian as X'df8d82a822ef364d5a93b3eb86ae661c'. Note that all parts are reversed within the string.

Of course if you insert the data directly by removing the dashes as X'a8828ddfef224d36935a1c66ae86ebb3' you can also retrieve it with that, but if you let .NET to handle the conversion, GUID will be stored as little endian byte representations.

(This all assumes little endian architecture, haven't tested on big endian)

Answer 2

The answer of Sami pointed me in the right direction. .NET outputs a Guid in a different way, so if you store it by using .NET and than use rawsql to fetch the data, then the guid in the rawsql need to be transformed in the same way. Howerver, in my case it was not the little endian notation. With the following code you can find the correct string:

var byteArray = new Guid("a8828ddf-ef22-4d36-935a-1c66ae86ebb3").ToByteArray();
string hex = BitConverter.ToString(byteArray).Replace("-", string.Empty);

On my computer, this returns: "DF8D82A822EF364D935A1C66AE86EBB3" Hence my raw sql will have to be

"select * from [tablename] where ID = X'DF8D82A822EF364D935A1C66AE86EBB3'"

Why I needed this; because I use an in-memory SQLite DB that I populate using EF Core, where I want to test a rawSql command. This might be handy for others in the same situation. However, keep in mind that a rawSql that works in SQLite might not work on another DB (and vice versa).

Answer 3

Sqlite can also be configured to store GUIDs as text. This is done in the connection string.

Data Source=c:\mydb.db;Version=3;BinaryGUID=False;

See https://www.connectionstrings.com/sqlite/