Unwanted UNICODE string prefix breaks LIKE operator with a CHAR field in 8-bit codepage

[ondrejtucny]

I have a pair of legacy tables d_limit and c_limit_type with a N:1 relationship. The legacy SQL code uses a [code] LIKE '_s' condition, which I cannot reasonably avoid right now.

The following legacy select works as intended, finding a d_limit row which is linked to a c_limit_type having the code column of the provided pattern:

select * from d_limit a
	join c_limit_type b on a.id_limit_type = b.id_limit_type
	where a.id_card = 4569828 and a.id_arch = 0 and b.[code] like '_s'

However, when I use the following query:

// find the sale limit
var limit = s.Query.All<DLimit>().FirstOrDefault(lim => lim.CardId == cardId && lim.IdArch == 0 && lim.LimitType.Code.Like("_s"));

the query returns nothing. The problem is the generated SQL:

exec sp_executesql N'SELECT [a].[id_limit], [a].[id_arch], 124 AS [TypeId], [a].[id_card], [a].[id_limit_type], [a].[limit], [a].[id_user], [a].[datum] 
	FROM [dbo].[d_limit] [a] 
	INNER JOIN [dbo].[c_limit_type] [b] ON ([a].[id_limit_type] = [b].[id_limit_type]) 
	WHERE ( ([a].[id_card] = @p0_0) AND  ( CAST([a].[id_arch]  AS integer) =  0) AND  ([b].[code] LIKE  N''_s'')) 
	ORDER BY  [a].[id_limit] ASC,  [a].[id_arch] ASC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;
',N'@p0_0 int',@p0_0=4569828

Here, the LIKE operator has the operand N'_s', and the UNICODE N specifier silently breaks the query. Apparently, it's not compatible with the data type of [code] which is CHAR(8). The database uses the SQL_Czech_CP1250_CI_AS collation, and hence an 8-bit code page.

I think it is an error to generate N'...' strings when querying a database with an 8-bit code page, and, specifically, when the other argument of an operator is a CHAR (or alike) and not an NVAR (or like).

In addition, please note DO here generates an unwanted cast as well: CAST([a].[id_arch] AS integer) = 0 — this cast is completely useless and I believe it is triggered by the fact that id_arch is a smallint here, and the constant is an int in C#. I think this is essentially the same issue as #189, which I reported earlier.

I am using release 7.1.0-Beta-2.