System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 33b39f05ae41dda312667608e3ddb28c79f26150
Title: GUID comparison fails in v1.0.97.0 EF query that previously worked in v1.0.96.0
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: LINQ Resolution: Works_As_Designed
Last Modified: 2015-08-12 14:45:43
Version Found In: 1.0.97.0
User Comments:
anonymous added on 2015-07-01 11:29:23:
When using v 1.0.96.0 (and earlier) when an EF query involving a comparison to a GUID value (stored in SQLite as a blob), the EF query passes the parameter correctly identified as type GUID (-- p__linq__0: '2d18e106-9a12-4007-b262-740ebdebbbb4' (Type = Guid, IsNullable = false)) and the queries succeed. in v1.0.97.0 exactly the same queries against exactly the same database fail and the guid parameters appear to be getting identified as fixed length ansi strings (-- p__linq__0: '2d18e106-9a12-4007-b262-740ebdebbbb4' (Type = AnsiStringFixedLength, IsNullable = false)).
I logged the 1.0.97.0 version of the query passed to EF (which failed to return any data) then reverted to v1.0.96.0 and re-ran the program without any other changes and logged the same query which correctly returned data as expected. Here are the logged queries in full:

v1.0.96.0 (correctly returned data):

Opened connection at 01/07/2015 11:55:50 +01:00
SELECT 
[Extent2].[databaseprojectid] AS [databaseprojectid], 
[Extent2].[projecttype] AS [projecttype], 
[Extent2].[title] AS [title], 
[Extent2].[description] AS [description], 
[Extent2].[termsofuse] AS [termsofuse], 
[Extent2].[createdby] AS [createdby], 
[Extent2].[createdon] AS [createdon], 
[Extent2].[comments] AS [comments]
FROM  [databaseprojectmembers] AS [Extent1]
INNER JOIN [databaseprojects] AS [Extent2] ON [Extent1].[databaseprojectid] = [Extent2].[databaseprojectid]
WHERE [Extent1].[brahmsuserid] = @p__linq__0
-- p__linq__0: '2d18e106-9a12-4007-b262-740ebdebbbb4' (Type = Guid, IsNullable = false)
-- Executing at 01/07/2015 11:55:50 +01:00
-- Completed in 5 ms with result: SQLiteDataReader

Closed connection at 01/07/2015 11:55:50 +01:00

v1.0.97.0 (incorrectly returned NO data):

Opened connection at 30/06/2015 17:28:36 +01:00
SELECT 
[Extent2].[databaseprojectid] AS [databaseprojectid], 
[Extent2].[projecttype] AS [projecttype], 
[Extent2].[title] AS [title], 
[Extent2].[description] AS [description], 
[Extent2].[termsofuse] AS [termsofuse], 
[Extent2].[createdby] AS [createdby], 
[Extent2].[createdon] AS [createdon], 
[Extent2].[comments] AS [comments]
FROM  [databaseprojectmembers] AS [Extent1]
INNER JOIN [databaseprojects] AS [Extent2] ON [Extent1].[databaseprojectid] = [Extent2].[databaseprojectid]
WHERE [Extent1].[brahmsuserid] = @p__linq__0
-- p__linq__0: '2d18e106-9a12-4007-b262-740ebdebbbb4' (Type = AnsiStringFixedLength, IsNullable = false)
-- Executing at 30/06/2015 17:28:36 +01:00
-- Completed in 4 ms with result: SQLiteDataReader

Closed connection at 30/06/2015 17:28:36 +01:00


So, as you will see, the only difference appears to be the identified type of the passed GUID parameter (Type = Guid in 1.0.96.0 (correct for my database storage type) and Type = AnsiStringFixedLength in 1.0.97.0 (incorrect for my database storage type)).

This is just one example but throughout my system data that was returned with v1.0.96.0 fails to be returned with v1.0.97.0 as I use Guid's as identifiers extensively (I have reasons for using Guid rather than integer identifiers so changing that, for me, isn't an option).

In a compiled application that doesn't work (using v1.0.97.0) all I have to do to make it work again is to is overwrite the 1.0.97.0 dll's with the version 1.0.96.0 dll's and then it all works again.

In the meantime I have reverted to using v1.0.96.0 but I hope this issue can be addressed in some way moving forward. Thanks

mistachkin added on 2015-07-01 16:52:11:
I think this is related to the changes made to fix [a4d9c7ee94].  The solution
is to set the BinaryGUID connection string property to True.  When using LINQ,
this also needs to be done via an environment variable because there was no
other reliable way to pass configuration information to it, e.g.:

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

This environment variable must be set prior to opening the database connection
via LINQ.  See also, check-in [87b4244129].

mistachkin added on 2015-07-22 18:40:54:
Deleted the previous comment.  Please be respectful.  Meanwhile, if you change
the ProviderManifestToken in the EDMX file appropriately, it should work.

anonymous added on 2015-08-12 14:45:43:
I wonder why this works out of the box when using "Code first" (=still DB first but without EDMX) -- I'm using the DbContext constructor which takes an existing DbConnection as parameter and in this case every connection string parameter -- especially 'BinaryGuid' -- seems to get used.