System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 8d928c3e888e7fd0064df3d4bc6912cf4e515a3f
Title: Incorrect Sql generated for constant binary GUIDs when using Linq.
Status: Closed Type: Feature_Request
Severity: Minor Priority: Blocker
Subsystem: LINQ Resolution: Fixed
Last Modified: 2015-01-14 17:32:50
Version Found In: 1.0.94.0
User Comments:
anonymous added on 2014-08-19 06:37:39:
SqlGenerator.cs
line 940

    case PrimitiveTypeKind.Guid:
        result.Append(EscapeSingleQuote(e.Value.ToString(), false /* IsUnicode */));
        break;
    
This case doesn't handle binary GUIDs. Should produce a binary string literal instead of a string literal.

To reproduce:

    string badSql = context.Tasks.Where(task => task.AssignedEmployeeId == new Guid("00224583-1ace-45ab-a982-88074632ab81")).ToString();

The resulting sql:

    SELECT 
    [Extent1].[Id] AS [Id], 
    ...
    [Extent1].[AssignedEmployeeId] AS [AssignedEmployeeId]
    FROM [EmployeeTasks] AS [Extent1]
    WHERE '00224583-1ace-45ab-a982-88074632ab81' = [Extent1].[AssignedEmployeeId]

There I'd expect a binary string literal, i.e. X'83452200CE1AAB45A98288074632AB81'.

mistachkin added on 2014-08-19 18:08:08:
This is not a bug, per se.  Yes, it would be nicer if the generated SQL took the
various connection settings into account; however, at that point in the code, it
does not appear to have ready access to any connection context to query those
settings from.  That being said, any change to this particular code would be
quite incompatible with the previous releases.

anonymous added on 2015-01-07 21:27:26:
If not a bug, what can be done to work around this? You can't just switch to using TEXT GUIDs in the database because the other Linq functions are hardcoded as BLOB GUIDs, for example Guid.New() generates the SQL 'RANDOMBLOB(16)'. It needs to at least be consistent.

mistachkin added on 2015-01-08 02:37:44:
I'm working on fixing this for 1.0.95.0.

mistachkin added on 2015-01-08 19:03:45:
Quick summary of changes on the 'tkt-8d928c3e88' branch:

1. The "ProviderManifestToken" may now contain a connection string.

2. All the DateTime and Guid handling related parameters contained within
   it will be used, if present, to modify the handling performed on these
   types by the System.Data.SQLite.Linq/EF6 providers.

3. The "AppendManifestToken_SQLiteProviderManifest" environment variable
   may be set in order to make it easier to modify the provider manifest
   token at runtime.

Could you please test the changes on the 'tkt-8d928c3e88' branch and see if
they work in your environment?

anonymous added on 2015-01-12 22:38:40:
The GUIDs are working great. And so are DateTimes.

Only thing worth mentioning is literal DateTimes as kind Ticks will translate to TEXT (i.e. WHERE time > '626828544000000000'). I don't see that as a problem in my environment but not sure if it was intended.

mistachkin added on 2015-01-12 23:44:54:
Thanks.  Should be fixed on the branch now.  Still needs more tests on my side.

mistachkin added on 2015-01-13 05:11:53:
I've added the necessary tests on the branch.