EF Core FromSql 的可选参数

本文介绍了EF Core FromSql 的可选参数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

我一直在测试一个简单的场景，用作将旧的 EF6 调用更新为 EF Core 调用的模板.我用作概念证明的测试示例:

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(); }

此代码不起作用，因为它声明该过程需要@createdByUserId"，但未提供——它尝试将 createdByUserId 映射到 @city，然后没有值映射到 @createdByUserId.

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.

如果我尝试定义一个值为 null 的参数@city，它会指出该过程需要@city 的非空值.

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

如果我尝试显式添加只有@addressId 和@createdByUserId 的参数列表，它会指出它缺少不可为空的@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

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

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

一旦你这样做，就不需要处理 DBNull 和 SqlParameters - 你可以使用 FromSql 重载接受C# 内插字符串并让 EF Core 为您创建参数.

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.

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

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();