CODEWITHSUNDEEP
c#entity-frameworksqliteentity-framework-6system.data.sqlite
Tony Vitabile
Tony Vitabile

Reputation: 8614

How does the SQLite Entity Framework 6 provider handle Guids?

I am porting our product's database to SQLite from another product that supported Guids. As we know, SQLite does not support Guids. I've got created an entity framework 6 model from my database (database first) and I need to build a query from C# that compares the Guid to one passed from the code.

The thing is I can't find any documentation on how the SQLite Entity Framework provider handles Guids. A web search didn't find anything useful for me, either. Just questions about using Entity Framework with SQLite.

Can anybody point me to the documentation, or maybe tell me how to work with Guids in a SQLite database through an EF6 model?

Upvotes: 14

Views: 6181

Answers (3)

VahidN
VahidN

Reputation: 19186

If you have an old database which has Blob type Guids, you should convert it to text type to make it work with the new EF-Core versions. more info

UPDATE MyTable
SET GuidColumn = hex(substr(GuidColumn, 4, 1)) ||
                 hex(substr(GuidColumn, 3, 1)) ||
                 hex(substr(GuidColumn, 2, 1)) ||
                 hex(substr(GuidColumn, 1, 1)) || '-' ||
                 hex(substr(GuidColumn, 6, 1)) ||
                 hex(substr(GuidColumn, 5, 1)) || '-' ||
                 hex(substr(GuidColumn, 8, 1)) ||
                 hex(substr(GuidColumn, 7, 1)) || '-' ||
                 hex(substr(GuidColumn, 9, 2)) || '-' ||
                 hex(substr(GuidColumn, 11, 6))
WHERE typeof(GuidColumn) == 'blob';

Also it's better to change its collation to NOCASE:

GuidColumn TEXT NOT NULL COLLATE NOCASE

Upvotes: 0

Greg
Greg

Reputation: 326

It appears that this was resolved in 1.0.95 however broken again in 1.0.97. The solution is to set the BinaryGUID property on the connection string to true and set the following environment variable (before you make the connection)

Environment.SetEnvironmentVariable("AppendManifestToken_SQLiteProviderManifest",";BinaryGUID=True;");

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

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

Upvotes: 13

Tony Vitabile
Tony Vitabile

Reputation: 8614

I finally have an answer to this problem.

My problem is that the SQLite Entity Framework 6 provider doesn't handle converting literal Guids in your code into SQL properly. That is, a Linq expression of the form

context.MyEntity.Where( x => x.GuidColumn == new Guid("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx") )

Gets converted into the following SQL:

SELECT GuidColumn, Column1, Column2, . . . Column n
FROM MyEntity AS Extent1
WHERE Extent1.GuidColumn = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

This is wrong, since the value stored in the column is a byte array.

According to this problem report on the SQLite site, it turns out that the SQLite team considers this to be a bug in the provider and they are working to fix it in release 1.0.95.0. I don't know when that will be released, but at least they recognize it as a problem and are going to fix it.

Upvotes: 4

Related Questions