Linq filter issue involving a varchar(1) field

Question

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?

Answer 1

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

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

Answer 2

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

Answer 3

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.