SQL Server susceptible to imlicit conversions

Open
#139 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Bug
Clarity
Needs clarification
Activity status
Stale
Tech stack
csharp, sql
Domain
databases, performance

Research direction

Start by reading GetDatabaseType, SqlServerAdapter, and AppendColumnNameEqualsValue, then trace where generated predicates and update assignments use that entry point. The issue does not identify a requested change or tests, so the scope and definition of done would need to be established before implementation.

Written by the indexing model from the issue text.

Description

With SQL Server, every helper that sports a WHERE predicate, when receiving some key property of type string, is liable to incur implicit conversion. string gets translated into nvarchar but if the database column type is either varchar or char(n) the column types will be mismatched, the column will be auto-converted, and performance tanks. Dealing with this idiosyncrasy in Dapper.Contrib appears to be only narrowly possible: there is no attribute with which to specify the database column type, no hook to override the type at render time, and no native way to cast the value in the generated predicate. There is zero native support for DbType, SqlDbType, or Dapper's DbString.

The best I can come up with is instructing GetDatabaseType to use a hypothetical implementation like

public class X : SqlServerAdapter
{
    private readonly IReadOnlyDictionary<string, string> dbTypeByName = new Dictionary<string, string>
    {
        { "foo", "varchar" },
    };

    public new void AppendColumnNameEqualsValue(StringBuilder sb, string columnName)
    {
        if (dbTypeByName.TryGetValue(columnName, out var dbType))
            sb.AppendFormat("[{0}] = cast(@{1} as {2})", columnName, columnName, dbType);
        else
            base.AppendColumnNameEqualsValue(sb, columnName);
    }
}

However, it pays to be aware that AppendColumnNameEqualsValue gets used both for the update column and the predicate, and even if that is desirable it lacks context and falls apart the moment two identically named columns have incompatible types.

The only practically feasible remedies I can see are changing the database column types, if possible, or avoiding the various predicate aware helpers entirely.

Dominant language
C#
Stars
293
Forks
109
No merged PRs in 30d

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from DapperLib/Dapper.Contrib

All issues in DapperLib/Dapper.Contrib

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.