System.Data.SQLite

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'.

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.

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.

I'm working on fixing this for 1.0.95.0.

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?

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.

Thanks.  Should be fixed on the branch now.  Still needs more tests on my side.

I've added the necessary tests on the branch.