System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: eafaece5c17c990bf630673d6665550e51c3e4be
Title: Collation & GUID
Status: Closed Type: Incident
Severity: Minor Priority: High
Subsystem: LINQ Resolution: Unable_To_Reproduce
Last Modified: 2016-06-21 20:20:17
Version Found In: 1.0.99.0
User Comments:
anonymous added on 2016-02-19 13:30:38:

I have a GUID field in a table. The field is defined as:

[PRINCIPAL_ID] TEXT NOT NULL DEFAULT ( hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(2)) || '-' || hex(randomblob(6)) ) COLLATE NOCASE

I updated the connection string to include "BinaryGUID=False", and was testing queries against the db. I have a known GUID in the database "81B3E425-C6B6-4C83-8A77-573C7DD16856".

When I try to query using Entity Framework:

ctx.Unit.Where(u => u.PrincipalId.ToString() == "81B3E425-C6B6-4C83-8A77-573C7DD16856")

It finds nothing. When I inspect the query that the IQueryable generates, I see:

SELECT 
... 
FROM [UNIT] AS [Extent1]
WHERE '81B3E425-C6B6-4C83-8A77-573C7DD16856' = (LOWER([Extent1].[PRINCIPAL_ID]))

This appears to override the default collation which was defined in the table.

If I use this exact same query against a similarly structured standard SQL database, it works irrespective of case as expected.

Because the C# GUID.ToString() method returns a lower case string, I can work around this for now by generating a new GUID and passing the string to it, however, it seems odd that it would explicitly add a "LOWER()" statement on a COLLATE NOCASE column. Perhaps it's even stranger that LOWER() overrides the collation defined on the column.


mistachkin added on 2016-04-01 02:49:22:
I've created a test case designed to trigger this issue; however, it does not
appear.

Any further information you could provide could be useful.  Are you sure the
System.Data.SQLite / System.Data.SQLite.Linq assemblies in use are 1.0.99.0?

mistachkin added on 2016-04-01 02:59:54:
Are you sure you aren't using LINQ-to-MSSQL?  Adding the .ToString method inside
of the Where clause results in the following exception:

System.NotSupportedException was unhandled
  HResult=-2146233067
  Message=LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.DefaultTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.Convert()
       at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at testlinq.Program.GuidCollation(Boolean binaryGuid) in C:\dev\sqlite\dotnet\testlinq\Program.cs:line 810
       at testlinq.Program.Main(String[] args) in C:\dev\sqlite\dotnet\testlinq\Program.cs:line 219

mistachkin added on 2016-04-01 03:04:21:
Test case added on branch, via check-in [9f01777e96e7f198].

mistachkin added on 2016-04-05 21:12:36:
This will not block the 1.0.100.0 release.