Entity Framework中的用户定义表生成了错误的查询

Question

我认为目前在实体框架6和可能的ADO.NET中遇到了一个bug。由于有截止日期，我不确定是否能等待修复此错误，并希望有人能帮助我找到一个干净的解决方法。

问题是查询在应该使用0.01和0.05的地方使用了1和5这些值。然而奇怪的是，0.1似乎在工作。

当前生成的查询为：（从SQL Server Profiler中获取）

declare @p3  dbo.someUDT
insert into @p3 values(NULL,5)
insert into @p3 values(5,0.10)
insert into @p3 values(NULL,1)
insert into @p3 values(1,2)

exec sp_executesql N'Select * from @AName',N'@AName  [dbo].[someUDT] READONLY',@AName=@p3

正确的代码应该是：

declare @p3  dbo.someUDT
insert into @p3 values(NULL,0.05)
insert into @p3 values(0.05,0.10)
insert into @p3 values(NULL,0.01)
insert into @p3 values(0.01,0.02)

exec sp_executesql N'Select * from @AName',N'@AName  [dbo].[someUDT] READONLY',@AName=@p3

我已经在github上创建了一个问题，链接在这里：用户定义表插入错误的值

我想在我的参数化查询中使用用户定义的表，这个问题解释了如何实现： Entity Framework 存储过程表值参数

这是用于获取上面SQL代码的C#代码

DataTable dataTable = new DataTable();
dataTable.Columns.Add("value1", typeof(decimal));
dataTable.Columns.Add("value2", typeof(decimal));

dataTable.Rows.Add(null,0.05m); 
dataTable.Rows.Add(0.05m,0.1m); 
dataTable.Rows.Add(null,0.01m); 
dataTable.Rows.Add(0.01m,0.02m); 
List<SqlParameter> Parameters = new List<SqlParameter>();

Parameters.Add(new SqlParameter("@AName", SqlDbType.Structured) { Value = dataTable , TypeName= "dbo.someUDT" });

dbContext.Database.ExecuteSqlCommand("Select * from @AName", Parameters.ToArray());

获取用户定义表的SQL代码

CREATE TYPE [dbo].[someUDT] AS TABLE
(
   [value1] [decimal](16, 5) NULL,
   [value2] [decimal](16, 5) NULL
)

编辑：
Gert Arnold找到了答案。根据他的答案，我在这里找到了一个现有的报告 SQL Server Profiler文本数据列错误地处理十进制输入

- Joost K

---

你能试一下这个代码 dataTable.Rows.Add(null,0.05m); 并检查它生成了什么查询吗？ - rjs123431

@rjs123431 我之前尝试过，结果是一样的。 - Joost K

@LuttiCoelho 我想要能够在SQL中使用datatable来做任何我想做的事情。所以我想要能够执行Select、Delete、Join等操作（尤其是Join）。 - Joost K

奇怪的是，我确实看到了不正确的SQL，但当我使用Database.SqlQuery（而不是Database.ExecuteSqlCommand）时，在客户端上收到了正确的值！ - Gert Arnold

你可以通过流畅的API或Code First DataAnnotations来配置精度级别。请确保它不是这个...听起来非常像，因为你正在使用小数。如果是这样，我会发布答案并将其标记为正确答案。 - Seabizkit

Answer 1

这是一个奇怪的Sql Profiler工具现象。 这些值被正确传输。 我可以通过创建一个带有您定义的用户类型和一个小表的数据库来证明这一点：

CREATE TABLE [dbo].[Values](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [decimal](16, 5) NOT NULL,
 CONSTRAINT [PK_Values] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
GO

然后插入几个值：

Id          Value
----------- ---------------------------------------
1           10.00000
2           1.00000
3           0.10000
4           0.01000

那么我运行了您的代码，稍作修改：

DataTable dataTable = new DataTable();
dataTable.Columns.Add("value1", typeof(decimal));
dataTable.Columns.Add("value2", typeof(decimal));

dataTable.Rows.Add(0.001m, 0.03m);
List<SqlParameter> Parameters = new List<SqlParameter>();

Parameters.Add(new SqlParameter("@AName", SqlDbType.Structured) { Value = dataTable, TypeName = "dbo.someUDT" });

using(var context = new MyContext(connStr))
{
    var query = "Select v.Id from dbo.[Values] v, @AName a "
        + " where v.Value BETWEEN a.value1 AND a.value2";
    var result = context.Database.SqlQuery<int>(query, Parameters.ToArray());
}

(MyContex 只是一个从 DbContext 继承而来的类，没有其他的东西)

0.001m 和 0.03m 之间只有一个值 ，这就是查询返回的结果：4。

然而，Sql Server 分析器记录了这个：

declare @p3 dbo.someUDT
insert into @p3 values(1,3) -- See here: the log is warped

exec sp_executesql N'Select v.Value from dbo.[Values] v, @AName a  where v.Value BETWEEN a.value1 AND a.value2',N'@AName [dbo].[someUDT] READONLY',@AName=@p3

在SSMS中返回记录#2。

我认为这与地区设置有关，十进制分隔符在日志记录中可能与十进制组分隔符混淆了。

- Gert Arnold

---

我从未想过问题出在日志记录上。非常棒的创新思维，感谢您解决了这个问题！ - Joost K

根据你的回答，我得到了这个错误报告 https://feedback.azure.com/forums/908035-sql-server/suggestions/38357785-sql-server-profiler-textdata-column-handles-decima。看起来我不是唯一一个遇到这个问题的人。 - Joost K

Answer 2

说实话，我没有和你一样的问题：

这是我的分析器日志：

declare @p3 dbo.someUDT
insert into @p3 values(NULL,0.05)
insert into @p3 values(0.05,0.10)
insert into @p3 values(NULL,0.01)
insert into @p3 values(0.01,0.02)

exec sp_executesql N'Select * from @AName',N'@AName [dbo].[someUDT] READONLY',@AName=@p3

我尝试了EntityFramework 6.2.0、6.3.0和6.4.0版本，但这些版本都没有出现问题：

DataTable dataTable = new DataTable();
dataTable.Columns.Add("value1", typeof(decimal));
dataTable.Columns.Add("value2", typeof(decimal));

dataTable.Rows.Add(null, 0.05);
dataTable.Rows.Add(0.05M, 0.1M);
dataTable.Rows.Add(null, 0.01);
dataTable.Rows.Add(0.01, 0.02);
List<SqlParameter> Parameters = new List<SqlParameter>();

Parameters.Add(new SqlParameter("@AName", SqlDbType.Structured) { Value = dataTable, TypeName = "dbo.someUDT" });

var dbContext = new test01Entities();
dbContext.Database.ExecuteSqlCommand("Select * from @AName", Parameters.ToArray());

另外，我测试了ADO.NET并得到了相同的结果：

SqlConnection cn = new SqlConnection("Data Source=(local);Initial Catalog=Test01;Integrated Security=true;");
using (var cmd = new SqlCommand("[foo]", cn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cn.Open();
    cmd.Parameters.AddWithValue("@param1", 0.02);
    cmd.Parameters.AddWithValue("@param2", 0.020);
    cmd.ExecuteNonQuery();
}

我正在使用Visual Studio 2017，.NET Framework 4.6.1和Microsoft SQL Server Enterprise（64位）。

- XAMT

---

我很确定这与语言设置有关（机器或数据库），所以你很幸运。 - Gert Arnold

我必须补充一点，我和@GertArnold都住在同一个国家，这很可能是我们都能够重现问题的原因。 - Joost K

@GertArnold 请制作一个示例（VS解决方案+SQL数据库）并分享给我。我会找到线索的。 - XAMT

@XAMT 这是一个 Sql Server Profiler 的 bug，所以我们无能为力。如果你愿意，可以尝试调整你的机器和数据库服务器的语言设置，看看在运行代码时 bug 出现的情况，但我认为这只是消磨时间的活动。 - Gert Arnold