SQL和Dapper性能隐式转换 [英] SQL and Dapper Performance Implicit Conversion

问题描述

我们如何防止Dapper中的SQL隐式转换？

How do we prevent SQL Implicit Conversions in Dapper?

我们意识到，我们正在执行SQL隐式转换，从而导致了索引扫描和死锁。 Dapper参数为nvarchar，而SQL表列为varchar。这导致我们所有的sql列都转换为nvarchar。

We realized, we were conducting an SQL Implicit conversion, causing an Index Scan and Deadlocks. Dapper parameters are nvarchar, while SQL table columns are varchar. This caused all our sql columns convert into nvarchar.

我们通过遍历所有嵌入式Dapper代码并将列转换为 cast（@SSN as varchar（9）），cast（ @LastName为varcarh（25）），cast（@EmployeeId为varchar（10）

We fixed the issue by going through all our embedded Dapper code and converting columns as cast(@SSN as varchar(9)), cast(@LastName as varcarh(25)), cast(@EmployeeId as varchar(10)

必须有一种更简单的方法，是

There has got to be an easier way, is there?

我读了这个博客，除了我们没有像下面这样设置字符串：

I read this blog, except we aren't setting strings like this example:

new { queryPlanHash = args[0], startDate = DateTime.Today.AddDays(-7) });

https://www.codeproject.com/articles/594133/bepluscarefulpluswithplusvarcharsplusinplusdapper

在连接中是否可以更改某些内容

Is there something we can change in the connection string?

FinanceConnectionString "providerName="System.Data.SqlClient" connectionString="Data Source=(local);Initial Catalog=FinanceData;integrated security=SSPI;persist security info=False; Trusted_Connection=Yes" />

推荐答案

您可以配置Dapper始终以varchar而不是nvarchar的形式提交字符串

You can configure Dapper to submit your strings always as varchar rather than nvarchar

Dapper.SqlMapper.AddTypeMap(typeof(string), System.Data.DbType.AnsiString);

另请参见可以在Dapper中默认使用AnsiStrings吗？

这篇关于SQL和Dapper性能隐式转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！