In my code I have the following fragment of a L2E query:
where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN") && ol.olstatus == "9"
This translates to following SQL fragment:
WHERE ([Extent8].[ohcustno] = @p__linq__1) AND (''IN'' = [Extent7].[ollastdoctype]) AND (''9'' = [Extent7].[olstatus]) ...
On a certain input the query executes 3 seconds. I change the query this way:
where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN" || ol.ollastdoctype == "CR") && ol.olstatus == "9"
and the resulting SQL changes are as follows:
WHERE ([Extent6].[ohcustno] = @p__linq__1) AND ([Extent5].[ollastdoctype] IN (N''IN'',N''CR'')) AND (''9'' = [Extent5].[olstatus]) ...
Note, that for some bizarre reason Entity Framework decided to convert my IN and CR to unicode. The result is that the query now executes 6 seconds on the same input. If I manually remove the N prefix from the IN clause and re-run query in SSMS the execution time goes back to 3 seconds. This is of course because SQL Server Query Optimizer can't get advantage of an index because compared types are now different (varchar vs nvarchar)
Can anyone explain me why Entity Framework all of a sudden decides to convert my constants to unicode and how can I avoid it?
you can try this method EntityFunction.AsNonUnicode, as follow
where ol.ordhead.ohcustno == login &&
(ol.ollastdoctype == EntityFunctions.AsNonUnicode("IN") ||
ol.ollastdoctype == EntityFunctions.AsNonUnicode("CR")) &&
ol.olstatus == "9"
This is only last hope, next is report bug to microsoft.
The EntityFunction.AsNonUnicode workaround is actually quite limited, it only works when the value supplied is either a literal or a string:
System.NotSupportedException: The method 'System.String
AsNonUnicode(System.String)' is only supported in LINQ to Entities
when the argument is a string variable or literal.
This is a serious problem in EF4.1 and has been documented here as well:
http://connect.microsoft.com/VisualStudio/feedback/details/650410/entity-framework-contains-still-defaulting-to-unicode-for-varchar-fields
Until this is fixed in EF itself, there is no workaround short of intercepting the query and manually replacing the syntax using something like EFTraceProvider.
Brutal.
This issue has been officially resolved in the latest EF versions. You can define the column type using DataAnnotations. Hope this helps someone!
See this answer: EF Data Annotations Column Type
This was a problem till ODP.net Beta 2 release but with Beta3 release of ODP.net 4.112.2.50 this problem is solved.
