EF6调用存储过程时DateTime参数传递失败问题求助

存储过程定义 #

PROCEDURE [Calculation_Upsert]
    @Id INT = NULL,
    @BillWho VARCHAR(50) = NULL,
    @ClearingBroker VARCHAR(255) = NULL,
    @Customer VARCHAR(255) = NULL,
    @EffectiveDate DATETIME = NULL,
    @ExecutingAccount VARCHAR(20) = NULL,
    @ExecutingBroker VARCHAR(103) = NULL,
    @Trader VARCHAR(255) = NULL,
    @User VARCHAR(50)
AS...

异常现象 #

通过EF6的dbContext调用该存储过程时，出现多种异常：

• 多数时候异常消息为空
• 有时提示“无法将nvarchar转换为DateTime”
• 有时提示@EffectiveDate参数缺失

尝试过传递null、DBNull.Value、字符串格式的"2023-01-13T10:00:00"或"2023-01-13 10:00:00"均无效。

尝试的三种调用方式 #

方法1：使用SqlQuery传递参数数组 #

var sqlparams = new object[]
{
    new SqlParameter("@Id", Id),
    new SqlParameter("@BillWho", BillWho),
    new SqlParameter("@ClearingBroker",cb),
    new SqlParameter("@Customer", cus),
    new SqlParameter("@EffectiveDate", DBNull.Value), //tried also null, DateTime.UTCNow, "2023-01-13T10:00:00", "2023-01-13 10:00:00"
    new SqlParameter("@ExecutingAccount", ea),
    new SqlParameter("@ExecutingBroker", eb),
    new SqlParameter("@Trader", trader),
    new SqlParameter("@User",user)
};

return await _dbContext.Database.SqlQuery<int>("[Calculation_Upsert] @Id, @BillWho, @ClearingBroker, @Customer, @EffectiveDate, @ExecutingAccount, @ExecutingBroker, @Trader, @UserNbk",
        sqlparams).ToListAsync();

• 使用DBNull.Value时返回System.Data.SqlClient.SqlError: 且异常消息为空
• 使用DateTime.UTCNow或字符串时提示“Error convert nvarchar to DateTime”

方法2：使用自动生成的上下文方法 #

_dbContext.Calculation_Upsert(Id, BillWho, cb, cus, DateTime.UtcNow, ea, eb, trader, user);
return new List<int>() { 1 };

返回System.Data.Entity.Core.EntityCommandExecutionException: 'An error occurred while executing the command definition. See the inner exception for details.'但InnerException消息为空。

方法3：拼接SQL字符串执行 #

await _dbContext.Database.ExecuteSqlCommandAsync(
        $"EXECUTE [Calculation_Upsert] @Id={Id}, @BillWho='{BillWho}', @ClearingBroker='{cb}', " +
            $"@Customer='{cus}', @EffectiveDate='2023-01-13T14:08:00', @ExecutingAccount='{ea}', @ExecutingBroker='{eb}', @Trader='{trader}', @UserNbk='{user}'"
        );
return new List<int>() { 1 };

返回System.Data.SqlClient.SqlError: 且异常消息为空。

SSMS中执行正常的代码 #

DECLARE @return_value int

EXEC    @return_value = [Calculation_Upsert]
        @Id = 123,
        @BillWho = 'Customer',
        @ClearingBroker = 'Broker',
        @Customer = 'My Customer',
        @EffectiveDate = null, --'2023-01-13T14:08:00', **both values work**
        @ExecutingAccount = '12345',
        @ExecutingBroker = 'Eb',
        @Trader = 'My Trader',
        @User = 'username'

SELECT  'Return Value' = @return_value

GO

已尝试更新Edmx中的数据库架构，但问题仍存在。确定遗漏了某个细节，但始终无法找到，恳请帮助。

内容的提问来源于stack exchange，提问作者wizard