Optional Parameters with EF Core FromSql

Question

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.

Answer 1

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