User defined table in Entity Framework generating incorrect query

Question

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

can you try this dataTable.Rows.Add(null,0.05m); and check what query it generates — rjs123431
– rjs123431, Commented Dec 3, 2019 at 8:26
You want to create a new table and return all values of the table? Sorry, but I don't understand what you really want. Can you share what is your principal goal with this? — Lutti Coelho
– Lutti Coelho, Commented Dec 5, 2019 at 16:43
@LuttiCoelho sorry for the confusion, the Select * from @AName is as placeholder. I am actually joining on the table in a bigger query that I didn't think was relevant to the question since this already replicates the issue in a simpler format. — Joost K
– Joost K, Commented Dec 5, 2019 at 16:46
The strange thing is, I indeed see the incorrect SQL, but when I use Database.SqlQuery (rather than Database.ExecuteSqlCommand) I receive the correct values in the client! — Gert Arnold
– Gert Arnold, Commented Dec 5, 2019 at 21:16

Gert Arnold · Accepted Answer · 2019-12-05 22:29:17Z

11

+50

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.

edited Dec 5, 2019 at 22:29

answered Dec 5, 2019 at 22:06

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joost K Over a year ago

I never thought about the issue being in logging. Great out of the box thinking and thank you for solving this!

Joost K Over a year ago

Based on your answer I got this bug report feedback.azure.com/forums/908035-sql-server/suggestions/… Seems like I wasn't the only one.

XAMT · Accepted Answer · 2019-12-07 18:49:52Z

1

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)

answered Dec 7, 2019 at 18:49

XAMT

1,6372 gold badges15 silver badges33 bronze badges

4 Comments

Gert Arnold Over a year ago

I'm pretty sure it's related to language settings (machine vs database), so you're just lucky.

Joost K Over a year ago

I have to add that me and @GertArnold both live in the same country making that a very likely explanation why we can both reproduce the problem

XAMT Over a year ago

@GertArnold Please make a sample (VS Solution + SQL Database) and share it. I will find the clue.

Gert Arnold Over a year ago

@XAMT It's a Sql Server Profiler bug so it's out of our hands. If you like you can play with your machine's and database server's language settings to see when the bug appears while you run your code, but IMO that's in the pastime department.

Collectives™ on Stack Overflow

User defined table in Entity Framework generating incorrect query

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related