Contains doesn't work correct with NTEXT and SQL CE

[bjarnef]

If generating a SQL query using Contains() on a db column of type NTEXT the query breaks on SQL CE using NPoco v4.0.2

Sql<ISqlContext> sql = Sql();

sql.Select("*")
   .From<ReviewDto>()
   .Where<ReviewDto>(x => x.StoreId == storeId);

if (!string.IsNullOrWhiteSpace(searchTerm))
{
    sql.Where<ReviewDto>(x =>
        x.Title.Contains(searchTerm) ||
        x.Name.Contains(searchTerm) ||
        x.Email.Contains(searchTerm) ||
        x.Body.Contains(searchTerm) // Body is of type NTEXT on SQL CE and NVARCHAR(MAX) on MSSQL 
    );
}

sql.OrderByDescending<ReviewDto>(x => x.CreateDate);

var page = _uow.Database.Page<ReviewDto>(pageNumber, pageSize, sql);

which generate the following SQL query:

SELECT *
FROM [review]
WHERE (([review].[storeId] = @0))
AND ((((upper([review].[title]) LIKE upper(@1) OR upper([review].[name]) LIKE upper(@2)) OR upper([review].[email]) LIKE upper(@3)) OR upper([review].[body]) LIKE upper(@4)))
ORDER BY ([review].[createDate]) DESC

If I remove the wrapping upper() function around [review].[body] it works. Alternatively cast the field, cast or convert on [review].[body] to nvarchar before using upper() function.

[bjarnef]

For now I use the following workaround:

sql.Where($"( upper({ReviewDto.TableName}.{SqlSyntax.GetQuotedColumnName("title")}) LIKE upper(@term) " +
   $"OR upper({ReviewDto.TableName}.{SqlSyntax.GetQuotedColumnName("name")}) LIKE upper(@term) " +
   $"OR upper({ReviewDto.TableName}.{SqlSyntax.GetQuotedColumnName("email")}) LIKE upper(@term) " +
   $"OR upper(convert(nvarchar(4000), {ReviewDto.TableName}.{SqlSyntax.GetQuotedColumnName("body")})) LIKE upper(@term))", new { term = $"%{searchTerm}%" });

instead of:

sql.Where<ReviewDto>(x =>
    x.Title.Contains(searchTerm) ||
    x.Name.Contains(searchTerm) ||
    x.Email.Contains(searchTerm) ||
    x.Body.Contains(searchTerm)
);

[schotime]

Tricky one this, cause if you want to do a case insensitive like you need the upper. And the fact you have to cast it, means if it really had ntext/nvarchar(max) data in it, it wouldn't work.