14

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?

Tony Vitabile
  • 8,298
  • 15
  • 67
  • 123
  • SQLite doesn't have an explicit column type GUID, but storing them with type affinity `BLOB` works perfectly fine. Don't know much about Entity Framework, but it seems type converters (beyond enums) will be only in EF 7. But GUID has a CTor from byte array etc, so it might be pretty straightforward. – peterchen Dec 03 '14 at 18:45
  • I'm storing them as BLOBs in my model, however, I have a problem. The code has an expression similar to "ID == Guid('xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx') which is throwing an exception because the type of ID in the database is `Byte[]` and the thing on the right is a `Guid`. The code in question has to run on our client (the code I'm working on) with SQLite, and on our server, where the database is SQL Server. The expression string can't change. I have to do something on the SQLite side to make the comparison work. I just don't know what. That's why I'm looking for the documentation. – Tony Vitabile Dec 03 '14 at 18:54
  • In SQLite, you can override the Guid() function: https://www.sqlite.org/c3ref/create_function.html (not sure how that works through EF though) – peterchen Dec 03 '14 at 19:52
  • That 'Guid()' function does not run in SQLite. It runs in the SQLite Entity Framework provider to convert a string into a Guid. The provider then emits a SQL query which compares the value in the column to the Guid. The problem is that the EF provider doesn't like the expression because the column type in EF is `byte[]`, but the thing being compared is a `Guid.` I can't seem to find any documentation and the lack of Guid support is killing me. – Tony Vitabile Dec 03 '14 at 20:35
  • Did you find a solution? I'm having the same problem right now... – Daniel Sklenitzka Dec 15 '14 at 15:44
  • I do not have an solution yet. I'm going to try something like `"ID == new byte[] { 0xXX, 0xYY . . . }`" and see if that works. If it does work, I'll have to write code that parses the condition string and transforms it into the format above. I'm working on other things right now and will come back to that soon. If you give this a shot & get it to work, post it as your answer & I'll give you a +1. If it works for me, too, I'll check your answer as the answer. – Tony Vitabile Dec 15 '14 at 17:21
  • I use Guids without any problems with EF6 and System.Data.SQLite 1.0.94. The entities have properties of type `Guid` and the database has columns of type `uniqueidentifier`, which obviously doesn't actually exist in SQLite. The columns appear to be blobs though when using `select typeof(GUID)`. – kjbartel Jan 06 '15 at 06:30

2 Answers2

13

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/

Greg
  • 326
  • 2
  • 6
  • 2
    I'm using 1.0.97, but setting the environment variable actually breaks it for me. I get `System.Data.Entity.Core.ProviderIncompatibleException : The provider did not return a ProviderManifest instance.` and an inner exception `System.ArgumentException : An entry with the same key already exists.`. Only changing the connection string works just fine however. – Thorarin Jul 24 '15 at 09:05
  • 1
    Great Post! This fixed the problem for me using 1.0.99 and Entity Framework 6 – RichTurner Jan 26 '16 at 14:21
  • 1
    BinaryGUID=True in config file is all I needed to do. – CrusherJoe Sep 26 '16 at 08:39
  • BinaryGUID is today default on. I was forced to disable it to be able to have working queries. – SeriousM May 17 '17 at 19:46
  • BinaryGUID=true; or BinaryGUID=false is working for me, setting it together wit enviroment variable causes the mentioned `An entry with the same key already exists` – Davi Fiamenghi Feb 11 '20 at 16:42
  • BinaryGUID=true; is enough. It retrieves data how it should, using GUID – Serlok Sep 08 '21 at 08:23
4

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.

Tony Vitabile
  • 8,298
  • 15
  • 67
  • 123
  • 1
    Thats bad.... we have decided to store GUIDs as strings due to this issue – Aleksei Poliakov Jun 16 '17 at 09:54
  • While I am no longer at that company & no longer have access to that code base, I think what we did was put the Guid into a Guid variable & compared against that. As I remember, this is handled correctly. – Tony Vitabile Jun 21 '17 at 19:13