CODEWITHSUNDEEP
c#sql-servervisual-studio-2010linq
Miri Dzimi
Miri Dzimi

Reputation: 31

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

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?

Upvotes: 3

Views: 6611

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109119

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()

Upvotes: 4

Related Questions