zespri zespri - 1 year ago 100
C# Question

EF 4 produces UNICODE string constans in SQL where the column type is varchar. How to avoid?

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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download