是否可以使用 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();更多推荐
EF Core FromSql 的可选参数
发布评论