linq to entity sqlFunction.string convert return varchar and not nvarchar

Question

I have to write linq to sql server between two tables,the related fields are in a different types. one is string(nvarchar) and the other is int.

here is my linq:

from je in a
join vP in b
on je.BaseRef  equals SqlFunctions.StringConvert((decimal) vP.DocEntry)

the result of the linq in sql was:

SELECT 
[Extent1].[Account] AS [Account]
FROM  [dbo].[a] AS [Extent1]
JOIN [dbo].[b] AS [Extent2] ON ([Extent1].[BaseRef] = (STR( CAST( [Extent2].[DocEntry] AS float)))) OR (([Extent1].[BaseRef] IS NULL) AND (STR( CAST( [Extent2].[DocEntry] AS float)) IS NULL))

that query return null because sql can not compare varchar and nvarchar. linq to entity translate sqlfunction.stringFormat to str('') and not to convert(nvarchar,''). how can I convert to nvarchar?

Gert Arnold · Accepted Answer · 2012-10-11 08:51:34Z

4

There is a work-around:

SqlFunctions.StringConvert((decimal) vP.DocEntry) + ""

This will translate to

(STR( CAST( [Extent2].[DocEntry] AS float))) + N''

which lifts the whole expression to nvarchar.

But I suspect that the comparison between char and nvarchar should not be a problem. The problem is more likely to be solved by trimming the conversion result:

SqlFunctions.StringConvert((decimal) vP.DocEntry).Trim()

edited Oct 11, 2012 at 8:51

answered Oct 11, 2012 at 7:14

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Miri Dzimi Over a year ago

Hello ,Thanks for your reply. I tried it but it returned varchar.

Gert Arnold Over a year ago

Are you sure? I checked a similar query with SQL_VARIANT_PROPERTY( STR( CAST( CAST( xxx AS float)) + N'', 'BaseType'). It was nvarchar. But are you sure that a mismatch between char and nvarchar is the problem? They should compare equally.

Miri Dzimi Over a year ago

hi gert, I tried it, also with the trim() and it was the same. sql can not compare nvarchar and varchar, sql can compare int and string without converting, but linq to entity does not pass build in this situation.

Gert Arnold Over a year ago

Char and nvarchar should compare just fine. Try SELECT CASE WHEN N'b' = CAST('b' AS char) THEN 1 ELSE 0 END (output: 1). It must be something else. Maybe you should show some sample data (including BaseRef and DocEntry).

Miri Dzimi Over a year ago

when i run the query that I got from linq:

|

Collectives™ on Stack Overflow

linq to entity sqlFunction.string convert return varchar and not nvarchar

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related