When I execute a SQL Server stored procedure from Entity Framework Core (v2.0) in my ASP.NET Core project, I get this exception:
InvalidOperationException: no mapping to a relational type can be found for the CLR type 'Int32[]'
The SQL Server stored procedure code looks like this:
CREATE PROCEDURE [dbo].[sp-UpdateProductOrderAndStock]
@customerOrderID INT,
@qty INT
AS
DECLARE @customerProductID INT
SET @customerProductID = (SELECT CustomerProductID
FROM dbo.CustomerOrder
WHERE ID = @customerOrderID)
SET NOCOUNT ON;
UPDATE dbo.CustomerOrder
SET CompletedQty = CompletedQty + @qty
WHERE ID = @customerOrderID;
UPDATE dbo.CProductStation
SET Qty = Qty - @qty
WHERE CustomerProductID = @customerProductID AND StationNo = 0;
ASP.NET Core data model as below:
public class CustomerProductDelivery
{
public int ID { get; set; }
public int CustomerOrderID { get; set; }
public DateTime DeliveryDate { get; set; }
public string DeliveryNo { get; set; }
public int DeliveryQty { get; set; }
public CustomerOrder CustomerOrder { get; set; }
}
ASP.net core Controller code to execute the stored procedure:
_context.Database.ExecuteSqlCommand("sp-UpdateProductOrderAndStock @p0, @p1", parameters: new[] { customerProductDelivery.CustomerOrderID,customerProductDelivery.DeliveryQty });
Best Answer
TLDR
Use string interpolation
Detailed answer:
You are falling into a typical
params object[]
trap.First, let see what's the type of the expression
Since both elements are of type
int
, the inferred type isint[]
.Now, there are 2
ExecuteSqlCommand
method overloads which allow you to pass parameters separately - one receivingparams object[]
and second receivingIEnumerable<object>
.Although
IEnumerable<T>
is covariant, covariance does not work for value types, soIEnumerable<int>
cannot be treated asIEnumerable<object>
, hence the second overload does not match.So the only valid overload is the one receiving
params object[]
. But sinceint[]
cannot be cast toobject[]
, the compiler treats it as singleobject
, thus emitting something likenew object[] { parameters }
.The net effect is that the EF method receives single parameter with
int[]
type value and generates the exception in question.With that being said, it could be fixed in many ways.
One is to remove
new [] { }
from the call, which is the whole idea ofparams object[]
construct:Another is to replace the
new []
with the explicitnew object[]
.Yet another one is to utilize the EF Core introduced String interpolation in FromSql and ExecuteSqlCommand: