找不到CLR类型'Int32 []'到关系类型的映射 [英] No mapping to a relational type can be found for the CLR type 'Int32[]'

问题描述

当我在ASP.NET Core项目中从Entity Framework Core（v2.0）执行SQL Server存储过程时，出现此异常：

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：找不到CLR类型'Int32 []'的关系类型映射

InvalidOperationException: no mapping to a relational type can be found for the CLR type 'Int32[]'

SQL服务器存储过程代码如下：

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数据模型如下：

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核心执行存储过程的控制器代码：

ASP.net core Controller code to execute the stored procedure:

_context.Database.ExecuteSqlCommand("sp-UpdateProductOrderAndStock @p0, @p1", parameters: new[] { customerProductDelivery.CustomerOrderID,customerProductDelivery.DeliveryQty });

推荐答案

TLDR

使用字符串插值

_context.Database.ExecuteSqlCommand($"Update_ProductOrderAndStock {customerProductDelivery.CustomerOrderID}, {customerProductDelivery.DeliveryQty}");

详细答案：

您正在陷入典型的 params对象[] 陷阱。

You are falling into a typical params object[] trap.

首先，让我们看看表达式的类型

First, let see what's the type of the expression

var parameters = new[] { customerProductDelivery.CustomerOrderID,customerProductDelivery.DeliveryQty }

由于两个元素的类型均为 int ，因此推断的类型为 int [] 。

Since both elements are of type int, the inferred type is int[].

现在，有2个 ExecuteSqlCommand 方法重载其中允许您分别传递参数-一个接收 params object [] ，第二个接收 IEnumerable< object> 。

Now, there are 2 ExecuteSqlCommand method overloads which allow you to pass parameters separately - one receiving params object[] and second receiving IEnumerable<object>.

尽管 IEnumerable< T> 是协变的，但协变不适用于值类型，因此 IEnumerable< int> 不能被视为 IEnumerable< object> ，因此第二个重载不匹配。

Although IEnumerable<T> is covariant, covariance does not work for value types, so IEnumerable<int> cannot be treated as IEnumerable<object>, hence the second overload does not match.

因此唯一有效的重载是接收 params对象[] 的重载。但是由于 int [] 无法转换为 object [] ，因此编译器将其视为单 object ，从而发出类似于 new object [] {parameters} 的东西。

So the only valid overload is the one receiving params object[]. But since int[] cannot be cast to object[], the compiler treats it as single object, thus emitting something like new object[] { parameters }.

最终结果是，EF方法接收到类型值为 int [] 的单个参数，并生成有问题的异常。

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.

一种是删除 new [] {} ，这是 params对象[] 构造的整体思想：

One is to remove new [] { } from the call, which is the whole idea of params object[] construct:

_context.Database.ExecuteSqlCommand(
    "Update_ProductOrderAndStock @p0, @p1", customerProductDelivery.CustomerOrderID, customerProductDelivery.DeliveryQty);

另一种是替换 new [] 与显式的 new对象[] 。

Another is to replace the new [] with the explicit new object[].

另一种方法是利用EF Core引入的 FromSql和ExecuteSqlCommand中的字符串插值：

Yet another one is to utilize the EF Core introduced String interpolation in FromSql and ExecuteSqlCommand:

_context.Database.ExecuteSqlCommand(
    $"Update_ProductOrderAndStock {customerProductDelivery.CustomerOrderID}, {customerProductDelivery.DeliveryQty}");

这篇关于找不到CLR类型'Int32 []'到关系类型的映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！