EF Core FromSql的可选参数 [英] Optional Parameters with EF Core FromSql

问题描述

是否可以使用EF Core FromSql来执行具有可选参数的存储过程？

我一直在测试一种简单的方案，以用作模板将旧的EF6调用更新为EF核心调用。我正在使用的测试示例作为概念证明：

 创建过程[dbo]。[TestNullableParameters] 
 @ addressId int = null，
 @city nvarchar（100）= null，
 @createdByUserId int 
 AS 
 BEGIN 
从CRM中选择* .address a 
其中（@addressId为null或a.AddressId = @addressId）
和（@city为null或a.City如@city）
和a.CreatedByUserId = @createdByUserId 
 END 
  

我的调用此proc的测试代码：

  [测试] 
 public void TestNullableParameters（）
 {
 var procName = exec CRM..TestNullableParameters; 
 var context = _testContainer.Resolve< CRM2Context>（）; 
 
 var addressId = new SqlParameter（ @ addressId，182）; 
 var createdByUserId = new SqlParameter（ @ createdByUserId，1620）; 
 var参数= new [] {addressId，createdByUserId}; 
 var结果= context.Address.FromSql（procName，parameters）.ToList（）; 
} 
  

此代码不起作用，因为它指出该过程需要 @createdByUserId ，但未提供-尝试将createdByUserId映射到@city，然后没有值映射到@createdByUserId。

如果我尝试定义参数@

如果我尝试显式添加仅包含@addressId和@的参数列表，则表示该过程要求@city为非空值。 createdByUserId，它指出它缺少不可为空的@city。

解决方案

为了跳过可选参数，您应该使用命名参数语法

  @parameterName = parameterValue 
  

如指定参数名称部分，用于执行存储过程的SQL Server文档。

执行此操作后，就无需处理 DBNull 和完全 SqlParameter -您可以使用 FromSql 重载接受C＃内插字符串，并让EF Core为您创建参数。

因此，调用SP的示例代码可以简化为：

  var结果= context.Address.FromSql（
 $ exec CRM.TestNullableParameters @addressId = {201}，@createdByUserId = {1620}）
 .ToList（）; 
  

Is it possible to use EF Core FromSql to execute a stored procedure that has optional parameters?

I have been testing out a simple scenario to use as a template for updating old EF6 calls to EF Core calls. The test example I am using as a proof of concept:

CREATE PROCEDURE [dbo].[TestNullableParameters] 
    @addressId int = null,
    @city nvarchar(100) = null,
    @createdByUserId int
AS
BEGIN
    select * from CRM..Address a
    where (@addressId is null or a.AddressId = @addressId)
    and (@city is null or a.City like @city)
    and a.CreatedByUserId = @createdByUserId
END

My test code that calls this proc:

[Test]
public void TestNullableParameters()
{
    var procName = "exec CRM..TestNullableParameters ";
    var context = _testContainer.Resolve<CRM2Context>();

    var addressId = new SqlParameter("@addressId", 182);
    var createdByUserId = new SqlParameter("@createdByUserId", 1620);
    var parameters = new[] {addressId, createdByUserId};
    var result = context.Address.FromSql(procName, parameters).ToList();
}

This code does not work, as it states the procedure requires "@createdByUserId", which was not supplied -- it attempts to map createdByUserId to @city, and then has no value to map to @createdByUserId.

If I try to define a parameter @city with value null, it states that the procedure requires a non-null value for @city.

If I try to explicitly add a parameter list with only @addressId and @createdByUserId, it states that it is missing non-nullable @city.

解决方案

In order to skip the optional parameters, you should use the named parameter syntax

@parameterName = parameterValue

as explained in the Specifying Parameter Names section of the SQL Server documentation for executing stored procedures.

Once you do that, there is no need to deal with DBNull and SqlParameters at all - you can use the FromSql overload accepting C# interpolated string and let EF Core create parameters for you.

Thus the sample code for calling the SP can be reduced to:

var result = context.Address.FromSql(
    $"exec CRM.TestNullableParameters @addressId = {201}, @createdByUserId = {1620}")
   .ToList();

这篇关于EF Core FromSql的可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！