Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

User defined table in Entity Framework generating incorrect query

I Think I am currently experiencing a bug in Entity Framework 6 and possibly ADO.NET. Since there is a deadline I am not sure I can wait for this bug to be fixed and hopefully someone can help me with a clean work around.

The problem is that the query uses the values 1 and 5 in places where it should be 0.01 and 0.05. However weirdly enough 0.1 seems to be working

The generated query currently is:(gotten from 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

While the correct code would be:

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 already created an issue on github here : User defined table inserting wrong value

I want to use a user defined table in my parameterized query, this question explains how this is done : Entity Framework Stored Procedure Table Value Parameter

This is the C# code used to get the SQL code above

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());

And SQL code to get the user defined table

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

EDIT:
Gert Arnold figured it out. Based on his answer I found an existing report here SQL Server Profiler TextData Column handles Decimal Inputs Incorrectly

like image 891
Joost K Avatar asked Dec 03 '19 08:12

Joost K


2 Answers

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.

like image 90
Gert Arnold Avatar answered Sep 28 '22 05:09

Gert Arnold


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)

like image 35
XAMT Avatar answered Sep 28 '22 04:09

XAMT