All geek questions in one place

EF 4 creates UNICODE string constants in SQL, where the column type is varchar. How to avoid?

In my code, I have the following fragment of an L2E request:

where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN") && ol.olstatus == "9"

This means the following SQL fragment:

 WHERE ([Extent8].[ohcustno] = @p__linq__1) AND (''IN'' = [Extent7].[ollastdoctype]) AND (''9'' = [Extent7].[olstatus]) ...

At a specific input, the query takes 3 seconds. I change the request like this:

 where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "IN" || ol.ollastdoctype == "CR") && ol.olstatus == "9"

and the resulting SQL changes look like this:

 WHERE ([Extent6].[ohcustno] = @p__linq__1) AND ([Extent5].[ollastdoctype] IN (N''IN'',N''CR'')) AND (''9'' = [Extent5].[olstatus]) ...

Note that for some bizarre reason, the Entity Framework decided to convert my IN and CR to unicode. As a result, the query now executes 6 seconds on the same input. If you manually remove the prefix N from the IN clause and re-run the request in SSMS, the execution time will return to 3 seconds. This, of course, is because the SQL Server query optimizer cannot use the index, because the types being compared are now different (varchar vs nvarchar)

Can someone explain to me why the Entity Framework suddenly decides to convert my constants to unicode and how can I avoid it?

+6
source share
4 answers

you can try this EntityFunction.AsNonUnicode method as follows

 where ol.ordhead.ohcustno == login && (ol.ollastdoctype == EntityFunctions.AsNonUnicode("IN") || ol.ollastdoctype == EntityFunctions.AsNonUnicode("CR")) && ol.olstatus == "9"

This is only a last resort, the next report error for Microsoft.

+13
source

The workaround of EntityFunction.AsNonUnicode is actually quite limited, it only works when the provided value is either a literal or a string:

System.NotSupportedException: the 'System.String AsNonUnicode (System.String)' method 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 also been documented here: 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, you can not do without intercepting the request and manually replacing the syntax with something like EFTraceProvider .

Brutal.

+2
source

This issue has been officially resolved in recent versions of EF. The column type can be determined using DataAnnotations. Hope this helps someone!

See this answer: EF Data Annotations Column Type

+1
source

This was a problem before the release of ODP.net Beta 2, but with the release of Beta3 ODP.net 4.112.2.50 this problem was resolved.

0
source

Source: https://habr.com/ru/post/886940/

More articles:


All Articles