Menu
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?
498
In CHAR, If the length of the string is less than set or fixed-length then it is padded with extra memory space. In VARCHAR, If the length of the string is less than the set or fixed-length then it will store as it is without padded with extra memory spaces.
Today's development platforms or their operating systems support the Unicode character set. Therefore, In SQL Server, you should utilize NVARCHAR rather than VARCHAR. If you do use VARCHAR when Unicode support is present, then an encoding inconsistency will arise while communicating with the database.
Use varchar unless you deal with a lot of internationalized data, then use nvarchar . Just use nvarchar for everything.
varchar [ ( n | max ) ]Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
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.
157
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
