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


in code have following fragment of l2e query:

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

this translates following sql fragment:

where ([extent8].[ohcustno] = @p__linq__1) , (''in'' = [extent7].[ollastdoctype]) , (''9'' = [extent7].[olstatus]) ... 

on input query executes 3 seconds. change query way:

where ol.ordhead.ohcustno == login && (ol.ollastdoctype == "in" || ol.ollastdoctype == "cr") && ol.olstatus == "9" 

and resulting sql changes follows:

where ([extent6].[ohcustno] = @p__linq__1) , ([extent5].[ollastdoctype] in (n''in'',n''cr'')) , (''9'' = [extent5].[olstatus]) ... 

note, bizarre reason entity framework decided convert in , cr unicode. result query executes 6 seconds on same input. if manually remove n prefix in clause , re-run query in ssms execution time goes 3 seconds. of course because sql server query optimizer can't advantage of index because compared types different (varchar vs nvarchar)

can explain me why entity framework of sudden decides convert constants unicode , how can avoid it?

you can try method entityfunction.asnonunicode, follow

where ol.ordhead.ohcustno == login &&     (ol.ollastdoctype == entityfunctions.asnonunicode("in") ||     ol.ollastdoctype == entityfunctions.asnonunicode("cr")) &&     ol.olstatus == "9" 

this last hope, next report bug microsoft.


Comments