CODEWITHSUNDEEP
c#sql-serverlinq-to-sqlunicode
KClough
KClough

Reputation: 2089

Linq filter issue involving a varchar(1) field

I have a field in my database that is varchar(1). I'm not permitted to change it. The only values for this field are 0 or 1.

Here is the where clause of the linq query:

where
g.id_Group == idGroup &&
a.AccountOpen.Value == '1'

My linq query generated the following sql where clause

WHERE ([t1].[id_Group] = 1234) AND (UNICODE([t0].[AccountOpen]) = '1')

'AccountOpen' is the varchar(1) field.

I changed the where clause to this manually

WHERE ([t1].[id_Group] = 1234) AND ([t0].[AccountOpen] = '1')

The second query returns data rows and the first one does not. How can I get this to work WITHOUT changing the database schema?

Upvotes: 1

Views: 769

Answers (3)

KristoferA
KristoferA

Reputation: 12397

The VS2008 L2S designer incorrectly maps varchar(1) and nvarchar(1) to System.Char. (see http://social.msdn.microsoft.com/Forums/en/linqtosql/thread/ac91e587-6e91-454c-9fa2-bab20b7a258c)

You can change the mapping for your [n]varchar(1) columns the L2S designer so it is mapped to a string instead. That way you can do normal string comparisons, and also will not get exceptions when materializing records containing empty [n]varchar(1)s.

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147274

It's because the UNICODE() function:

Returns the integer value, as defined by the Unicode standard, for the first character of the input expression

So if the AccountOpen field contains the value "1", the UNICODE() value for that is 49...so it doesn't satisfy the search condition (49 <> '1')

Upvotes: 0

jball
jball

Reputation: 25014

Have you tried using a string instead of a char for comparison?

where 
g.id_Group == idGroup && 
a.AccountOpen.Value == "1"

Upvotes: 1

Related Questions