User defined table in Entity Framework generating incorrect query
Honestly, I have not the same problem as you:
This is my Profiler Log :
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
I tried EntityFramework version 6.2.0 & 6.3.0 & 6.4.0 and none of these shows the problem :
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());
Also, I test the ADO.NET and have same result:
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();
}
I am using Visual Studio 2017, .NET Framework 4.6.1 and Microsoft SQL Server Enterprise (64-bit)
It's a weird Sql Profiler artifact. The values are transferred correctly. I can demonstrate that by creating a database with your user-defined type and one little table:
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
And inserting a couple of values:
Id Value
----------- ---------------------------------------
1 10.00000
2 1.00000
3 0.10000
4 0.01000
Then I run your code, slightly adapted:
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
is just a class inheriting from DbContext
and nothing else)
There is only one value between 0.001m
and 0.03m
and that's exactly what the query returns: 4
.
However, Sql Server profiler logs this:
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
And in SSMS that returns record #2.
I think it has to do with regional settings and decimal separators getting mixed up with decimal group separators somewhere in the logging.