Linq to SQL Decimal output Parameters

  • Thread starter Thread starter Martin H
  • Start date Start date
M

Martin H

I am having problems with the SQL generated by LINQ to execute a stored
procedure with Decimal output parameters. This simple example illustrates the
problem

CREATEPROCEDURE ShowLinqBug(
@paramValue Decimal(12,4) OUTPUT)
AS
BEGIN
SET @paramValue = 9999999.9999
END
go

When this is called from using LINQ using this procedure

private void button1_Click(object sender, EventArgs e)
{
try
{
DataClasses1DataContext context = new DataClasses1DataContext();
decimal? input = 0;
context.ShowLinqBug(ref input);
MessageBox.Show(input.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

This is the SQL that is generated

declare @p3 numeric(1,0)
set @p3=0
declare @p4 int
set @p4=NULL
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[ShowLinqBug] @Input = @p0
OUTPUT',N'@p0 decimal(1,0) output,@RETURN_VALUE int output',@p0=@p3
output,@RETURN_VALUE=@p4 output
select @p3, @p4

Notice that even though the stored procedure parameter is Decimal(12,4),
LINQ has converted this to numeric(1,0) based on the actual value passed in.
Id the value of input is set to null then linq will declare it as
numeric(29,0) , Because te stored procedure attempts to update @paramValue =
9999999.9999 it fails and raises an "Error converting decimal to decimal"
error. In this simple example it is easy (albeit ugly) to initialise the
variable to 9999999.9999 so a numeric(12,4) parameter will be passed. However
this can't easily be done if the stored procedure is being used to update a
Linq class and is mapped to a property.

I have for this problem being reporting elsewhere but I can't find anything.
I hope that this means that I am overlooking something trivial. Has anyone
got any fix for this behaviour or any otherwise a simple workaround?
 
Hi Mhorner,

Regarding on this LINQ sp issue, I've performed some test according to the
code you provided and did repro the same behavior. Based on my research, I
think the problem is likely due to the default generated LINQ
store-procedure function is declared as the following style:

==========
[Function(Name="dbo.ShowLinqBug")]
public int ShowLinqBug([Parameter(DbType = "Decimal")] ref
System.Nullable<decimal> paramValue)
{
...............
==========

therefore, the runtime LINQ context class may change the decimal type's
detailed precision or other info according to the input parameter value.
One approach I've found is manually customize the linq function and
explicitly specify the full decimal type. e.g.

===========
[Function(Name="dbo.ShowLinqBug")]
public int ShowLinqBug([Parameter(DbType = "Decimal(12,4)")] ref
System.Nullable<decimal> paramValue)
{
===============

thus, it can work correctly. In addition, if you do not want to directly
modify the auto-generated "xxx.designer.cs" file(which maybe override when
update the linq class in IDE), you can add a dedicated partial class file
to include all of your custom changes. e.g.

==================
namespace LINQConsole1
{
//testDBDataContext is the class name of the auto-generated LINIQ
context class

public partial class testDBDataContext : System.Data.Linq.DataContext
{

//my own function
[Function(Name = "dbo.ShowLinqBug")]
public int MyShowLinqBug([Parameter(DbType = "Decimal(12,4)")] ref
System.Nullable<decimal> paramValue)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), paramValue);
paramValue =
((System.Nullable<decimal>)(result.GetParameterValue(0)));
return ((int)(result.ReturnValue));
}

}

}
====================

Hope this helps.


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: =?Utf-8?B?TWFydGluIEg=?= <[email protected]>
Subject: Linq to SQL Decimal output Parameters
Date: Sat, 27 Sep 2008 19:18:01 -0700
Click to expand...
I am having problems with the SQL generated by LINQ to execute a stored
procedure with Decimal output parameters. This simple example illustrates the
problem

CREATEPROCEDURE ShowLinqBug(
@paramValue Decimal(12,4) OUTPUT)
AS
BEGIN
SET @paramValue = 9999999.9999
END
go

When this is called from using LINQ using this procedure

private void button1_Click(object sender, EventArgs e)
{
try
{
DataClasses1DataContext context = new DataClasses1DataContext();
decimal? input = 0;
context.ShowLinqBug(ref input);
MessageBox.Show(input.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

This is the SQL that is generated

declare @p3 numeric(1,0)
set @p3=0
declare @p4 int
set @p4=NULL
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[ShowLinqBug] @Input = @p0
OUTPUT',N'@p0 decimal(1,0) output,@RETURN_VALUE int output',@p0=@p3
output,@RETURN_VALUE=@p4 output
select @p3, @p4

Notice that even though the stored procedure parameter is Decimal(12,4),
LINQ has converted this to numeric(1,0) based on the actual value passed in.
Id the value of input is set to null then linq will declare it as
numeric(29,0) , Because te stored procedure attempts to update @paramValue =
9999999.9999 it fails and raises an "Error converting decimal to decimal"
error. In this simple example it is easy (albeit ugly) to initialise the
variable to 9999999.9999 so a numeric(12,4) parameter will be passed. However
this can't easily be done if the stored procedure is being used to update a
Linq class and is mapped to a property.

I have for this problem being reporting elsewhere but I can't find anything.
I hope that this means that I am overlooking something trivial. Has anyone
got any fix for this behaviour or any otherwise a simple workaround?
Click to expand...
 
Steven,

Thanks very much for this workaround.

Initially when I encountered this problem I was mapping a stored procedure
to the update behaviour of a class and I then called the stored procedure
directly. I think that going down the SubmitChanges() route will only be
possible by editing the auto generated code which as you point out will be
overridden every time the code is regenerated.

I wonder what the chances of this being fixed at some point?

"Steven Cheng" said:
Hi Mhorner,

Regarding on this LINQ sp issue, I've performed some test according to the
code you provided and did repro the same behavior. Based on my research, I
think the problem is likely due to the default generated LINQ
store-procedure function is declared as the following style:

==========
[Function(Name="dbo.ShowLinqBug")]
public int ShowLinqBug([Parameter(DbType = "Decimal")] ref
System.Nullable<decimal> paramValue)
{
...............
==========

therefore, the runtime LINQ context class may change the decimal type's
detailed precision or other info according to the input parameter value.
One approach I've found is manually customize the linq function and
explicitly specify the full decimal type. e.g.

===========
[Function(Name="dbo.ShowLinqBug")]
public int ShowLinqBug([Parameter(DbType = "Decimal(12,4)")] ref
System.Nullable<decimal> paramValue)
{
===============

thus, it can work correctly. In addition, if you do not want to directly
modify the auto-generated "xxx.designer.cs" file(which maybe override when
update the linq class in IDE), you can add a dedicated partial class file
to include all of your custom changes. e.g.

==================
namespace LINQConsole1
{
//testDBDataContext is the class name of the auto-generated LINIQ
context class

public partial class testDBDataContext : System.Data.Linq.DataContext
{

//my own function
[Function(Name = "dbo.ShowLinqBug")]
public int MyShowLinqBug([Parameter(DbType = "Decimal(12,4)")] ref
System.Nullable<decimal> paramValue)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), paramValue);
paramValue =
((System.Nullable<decimal>)(result.GetParameterValue(0)));
return ((int)(result.ReturnValue));
}

}

}
====================

Hope this helps.


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: =?Utf-8?B?TWFydGluIEg=?= <[email protected]>
Subject: Linq to SQL Decimal output Parameters
Date: Sat, 27 Sep 2008 19:18:01 -0700
Click to expand...
I am having problems with the SQL generated by LINQ to execute a stored
procedure with Decimal output parameters. This simple example illustrates the
problem

CREATEPROCEDURE ShowLinqBug(
@paramValue Decimal(12,4) OUTPUT)
AS
BEGIN
SET @paramValue = 9999999.9999
END
go

When this is called from using LINQ using this procedure

private void button1_Click(object sender, EventArgs e)
{
try
{
DataClasses1DataContext context = new DataClasses1DataContext();
decimal? input = 0;
context.ShowLinqBug(ref input);
MessageBox.Show(input.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

This is the SQL that is generated

declare @p3 numeric(1,0)
set @p3=0
declare @p4 int
set @p4=NULL
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[ShowLinqBug] @Input = @p0
OUTPUT',N'@p0 decimal(1,0) output,@RETURN_VALUE int output',@p0=@p3
output,@RETURN_VALUE=@p4 output
select @p3, @p4

Notice that even though the stored procedure parameter is Decimal(12,4),
LINQ has converted this to numeric(1,0) based on the actual value passed in.
Id the value of input is set to null then linq will declare it as
numeric(29,0) , Because te stored procedure attempts to update @paramValue =
9999999.9999 it fails and raises an "Error converting decimal to decimal"
error. In this simple example it is easy (albeit ugly) to initialise the
variable to 9999999.9999 so a numeric(12,4) parameter will be passed. However
this can't easily be done if the stored procedure is being used to update a
Linq class and is mapped to a property.

I have for this problem being reporting elsewhere but I can't find anything.
I hope that this means that I am overlooking something trivial. Has anyone
got any fix for this behaviour or any otherwise a simple workaround?
Click to expand...
Click to expand...
 
Thanks for your reply Mhorner,

Yes, the current model may require us to manually specify the "DbType"
explicitly in case the default generated attribute is not that detailed and
specific.

As for the chances that this behavior got fixed, I think it is hard to say
on this. Generally for a problem to be fixed, the dev team will first
receive enough feedbacks on that and then identify it and make plan
according to their schedule. If you do feel it an necessary feature to add,
I suggest you submit your comments to our connect feedback site so that the
dev team can hear more on this:

http://connect.microsoft.com/feedback/default.aspx?SiteID=210

Thanks again for your feedback.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: =?Utf-8?B?TWFydGluIEg=?= <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: Linq to SQL Decimal output Parameters
Date: Mon, 29 Sep 2008 16:22:01 -0700
Click to expand...
Steven,

Thanks very much for this workaround.

Initially when I encountered this problem I was mapping a stored procedure
to the update behaviour of a class and I then called the stored procedure
directly. I think that going down the SubmitChanges() route will only be
possible by editing the auto generated code which as you point out will be
overridden every time the code is regenerated.

I wonder what the chances of this being fixed at some point?

"Steven Cheng" said:
Hi Mhorner,

Regarding on this LINQ sp issue, I've performed some test according to the
code you provided and did repro the same behavior. Based on my research, I
think the problem is likely due to the default generated LINQ
store-procedure function is declared as the following style:

==========
[Function(Name="dbo.ShowLinqBug")]
public int ShowLinqBug([Parameter(DbType = "Decimal")] ref
System.Nullable<decimal> paramValue)
{
...............
==========

therefore, the runtime LINQ context class may change the decimal type's
detailed precision or other info according to the input parameter value.
One approach I've found is manually customize the linq function and
explicitly specify the full decimal type. e.g.

===========
[Function(Name="dbo.ShowLinqBug")]
public int ShowLinqBug([Parameter(DbType = "Decimal(12,4)")] ref
System.Nullable<decimal> paramValue)
{
===============

thus, it can work correctly. In addition, if you do not want to directly
modify the auto-generated "xxx.designer.cs" file(which maybe override when
update the linq class in IDE), you can add a dedicated partial class file
to include all of your custom changes. e.g.

==================
namespace LINQConsole1
{
//testDBDataContext is the class name of the auto-generated LINIQ
context class

public partial class testDBDataContext : System.Data.Linq.DataContext
{

//my own function
[Function(Name = "dbo.ShowLinqBug")]
public int MyShowLinqBug([Parameter(DbType = "Decimal(12,4)")] ref
System.Nullable<decimal> paramValue)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), paramValue);
paramValue =
((System.Nullable<decimal>)(result.GetParameterValue(0)));
return ((int)(result.ReturnValue));
}

}

}
====================

Hope this helps.


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: =?Utf-8?B?TWFydGluIEg=?= <[email protected]>
Subject: Linq to SQL Decimal output Parameters
Date: Sat, 27 Sep 2008 19:18:01 -0700
Click to expand...
I am having problems with the SQL generated by LINQ to execute a stored
procedure with Decimal output parameters. This simple example
Click to expand...
illustrates
the
problem

CREATEPROCEDURE ShowLinqBug(
@paramValue Decimal(12,4) OUTPUT)
AS
BEGIN
SET @paramValue = 9999999.9999
END
go

When this is called from using LINQ using this procedure

private void button1_Click(object sender, EventArgs e)
{
try
{
DataClasses1DataContext context = new DataClasses1DataContext();
decimal? input = 0;
context.ShowLinqBug(ref input);
MessageBox.Show(input.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

This is the SQL that is generated

declare @p3 numeric(1,0)
set @p3=0
declare @p4 int
set @p4=NULL
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[ShowLinqBug] @Input = @p0
OUTPUT',N'@p0 decimal(1,0) output,@RETURN_VALUE int output',@p0=@p3
output,@RETURN_VALUE=@p4 output
select @p3, @p4

Notice that even though the stored procedure parameter is Decimal(12,4),
LINQ has converted this to numeric(1,0) based on the actual value
Click to expand...
passed
in.
Id the value of input is set to null then linq will declare it as
numeric(29,0) , Because te stored procedure attempts to update
Click to expand...
@paramValue
=
9999999.9999 it fails and raises an "Error converting decimal to decimal"
error. In this simple example it is easy (albeit ugly) to initialise the
variable to 9999999.9999 so a numeric(12,4) parameter will be passed. However
this can't easily be done if the stored procedure is being used to
Click to expand...
update
a
Linq class and is mapped to a property.

I have for this problem being reporting elsewhere but I can't find anything.
I hope that this means that I am overlooking something trivial. Has anyone
got any fix for this behaviour or any otherwise a simple workaround?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top