如何将 null 参数传递给实体框架核心 2.1 中的上下文查询类型

Question

我正在.NET Core应用程序中开发实体框架核心2.1。我编写了存储过程来通过上下文查询类型获取声明列表，该过程采用三个参数，其中两个是可为空的 Guid。到目前为止，它工作正常，没有空参数传递，但传递空参数时，它会抛出错误

https://learn.microsoft.com/en-us/ef/core/modeling/query-types

错误

The parameterized query '(@UserVal uniqueidentifier,@ClientVal nvarchar(4000),@Consultati' expects the parameter '@ClientVal', which was not supplied.

EF 查询类型

public override IQueryable<GetSystemClaims> Execute()
{
public Guid UserId { get; set; }
public Guid? ClientId { get; set; }
public Guid? ConsultationId { get; set; }

var userParam = new SqlParameter("@UserVal", UserId);
var clientParam = new SqlParameter("@ClientVal", ClientId);
var consultationParam = new SqlParameter("@ConsultationVal", ConsultationId);
var userClaimsList = Context.Query<UserClaimsQueryView>().FromSql("EXECUTE dbo.ListUserClaims @userId=@UserVal, @clientId=@ClientVal, @consultationId=@ConsultationVal"
, userParam, clientParam, consultationParam);
return userClaimsList;
}

Answer 1

var userParam = new SqlParameter("@UserVal", UserId);
var clientParam = new SqlParameter("@ClientVal", SqlDbType.UniqueIdentifier);
clientParam.Value = (object)ClientId ?? DBNull.Value;
var consultationParam = new SqlParameter("@ConsultationVal", SqlDbType.UniqueIdentifier);
consultationParam.Value = (object)ConsultationId ?? DBNull.Value;
var userClaimsList = Context.Query<UserClaimsQueryView>().FromSql("EXECUTE dbo.ListUserClaims @userId=@UserVal, @clientId=@ClientVal, @consultationId=@ConsultationVal"
, userParam, clientParam, consultationParam);

Answer 2

您也可以通过以下方式执行此操作。

如果要允许值中的空值，可以按如下所示实现：

var countryParam = new SqlParameter("@CountryName", searchField.CountryName);
countryParam.Value = (object)searchField.CountryName ?? DBNull.Value;