CODEWITHSUNDEEP
stored-procedures.net-coreentity-framework-core-2.1
K.Z
K.Z

Reputation: 5075

How to pass null parameters to context query type in Entity Framework Core 2.1

I am working on Entity Framework Core 2.1 within .NET Core application. I have written stored procedure to get claims list via Context Query-Type which take three parameters where two of them are nullable Guid. It work fine as far no null parameter pass over but with passing null parameters, it throw error

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

error

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

EF Query Type

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

Upvotes: 7

Views: 8915

Answers (2)

mitesh
mitesh

Reputation: 33

You can also do this in the following way.

If you want to allow the null values in the value, you could achieve this as shown below:

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

Upvotes: 2

K.Z
K.Z

Reputation: 5075

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

Upvotes: 13

Related Questions