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.