Pass param into SQL checksum function with Dapper

Question

Faced with strange problem:

string sql = $@"SELECT * FROM SearchLogs
                            WHERE CHECKSUM(@query) = cs_SearchTerm 
                            AND Website = @website";          

 return await Connection.QueryFirstOrDefaultAsync<SearchLog>(sql,
                param: new { query, website });

Record 100% exists in the database, but I got null. cs_SearchTerm is computed, int, nullable field. Then I tried:

DECLARE @term nvarchar(500)
SET @term = @query

SELECT * FROM SearchLogs
WHERE CHECKSUM(@term) = cs_SearchTerm AND Website = @website

But got same result. Then I tried split into two operations:

 private async Task<int> SqlCheckSumAsync(string query)
 {
        string sql = @"SELECT CHECKSUM(@query)";

        return await Connection.ExecuteScalarAsync<int>(sql, param: new { query }, transaction: Transaction);
 }

string sql = $@"SELECT * FROM Search_Master 
                        WHERE cs_SearchTerm = @checksum
                        AND Website = @website";

        int checksum = await SqlCheckSumAsync(query);

        return (await Connection.QueryFirstOrDefaultAsync<Search_Master>(sql,
            param: new { checksum, website },
            transaction: Transaction));

But still got not positive result. I wonder what I doing wrong? Why I can't pass param into SQL scalar?

Answer 1

From the comments, this works:

 SELECT * FROM SearchLogs WHERE cs_SearchTerm = CHECKSUM('abc') AND Website = 'ICF'

So: that tells me that you computed your checksums using varchar inputs. This is very important, because CHECKSUM('abc') gives a completely different answer than CHECKSUM(N'abc'):

select checksum('abc') as [Ansi], checksum(N'abc') as [Unicode]

gives:

Ansi        Unicode
----------- -----------
34400       1132495864

By default, dapper uses nvarchar (because .NET strings are utf-16). So we need to tell dapper to pass that as an ANSI string; fortunately this is simple:

return await Connection.ExecuteScalarAsync<int>(sql,
    new { query = new DbString { Value = query, IsAnsi = true} },
    transaction: Transaction);

Dapper's DbString type allows fine-grained control over how strings are sent, including both whether they are unicode or ANSI, and whether they are fixed width (and if so: what) or variable width.