title: Entity Framework Core 3.1 stored procedure optional parameters not working
body: <p>[SOLVED] (see the end)</p>
  <p>I've spent most of the day looking for a solution that works with no luck.</p>
  <ul>
  <li>EF Core 3.1</li>
  <li>Visual Studio 2019</li>
  <li>SQL Server 2017</li>
  <li>.Net Core 3.1</li>
  </ul>
  <p>I have not been able to find a solution that works to call a stored procedure from .Net Core 3.1 with optional parameters.  I've read dozens of articles from SO to blogs, but nothing works.</p>
  <p>Here's my C# code for the method to call the stored procedure with all the attempts at making this work:</p>
  <pre><code>public List&lt;BudgetWorkflowStatusByDepartment&gt; GetBudgetWorkflowStatusByDepartment(int? companyId, int? departmentId, int? locationId, int? sublocationId)
  {
          //var compId = new SqlParameter(&quot;@CompanyId&quot;, companyId);
          //compId.Value = (object)companyId ?? SqlInt32.Null;
  
          //var deptId = new SqlParameter(&quot;@DepartmentId&quot;, departmentId);
          //deptId.Value = (object)departmentId ?? SqlInt32.Null;
  
          //var locId = new SqlParameter(&quot;@LocationId&quot;, locationId);
          //locId.Value = (object)locationId ?? SqlInt32.Null;
  
          //var subId = new SqlParameter(&quot;@SubLocationId&quot;, sublocationId);
          //subId.Value = (object)sublocationId ?? SqlInt32.Null;
  
          var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
          compId.Value = (object)companyId ?? DBNull.Value;
  
          var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
          deptId.Value = (object)departmentId ?? DBNull.Value;
  
          var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
          locId.Value = (object)locationId ?? DBNull.Value;
  
          var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
          subId.Value = (object)sublocationId ?? DBNull.Value;
  
          //var result = context.BudgetWorkflowStatusByDepartment
          //    .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID, @DepartmentID, @LocationID, @SubLocationID&quot;, compId, deptId, locId, subId).ToList();
  
          //var result = context.BudgetWorkflowStatusByDepartment
          //    .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment {0}, {1}, {2}, {3}&quot;, companyId, departmentId, locationId, companyId).ToList();
  
          // Error Must declare the scalar variable &quot;@CompanyID&quot;.
          //var result = context.BudgetWorkflowStatusByDepartment
          //    .FromSqlRaw($&quot;EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}&quot;).ToList();
  
          // Error Must declare the scalar variable &quot;@CompanyID&quot;.
          var result = context.BudgetWorkflowStatusByDepartment
              .FromSqlRaw($&quot;EXEC mis.BudgetWorkflowStatusByDepartment {compId}, {deptId}, {locId}, {subId}&quot;).ToList();
  
          //var result = context.BudgetWorkflowStatusByDepartment
          //        .FromSqlInterpolated($&quot;EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}&quot;).ToList();
  
          return result;
  }
  </code></pre>
  <p>None of these attempts work.  The errors range from:</p>
  <blockquote>
  <p>Data is Null. This method or property cannot be called on Null values.</p>
  </blockquote>
  <p>... to ...</p>
  <blockquote>
  <p>Must declare the scalar variable &quot;@compId&quot;.</p>
  </blockquote>
  <p>... to some other errors when I just tried something that was most likely just incorrect in a desperate attempt to get it to work.</p>
  <p>This is the signature of the stored procedure itself showing that all parameters are nullable.</p>
  <pre><code>ALTER PROCEDURE [mis].[BudgetWorkflowStatusByDepartment] 
      (@CompanyID int = null,
       @DepartmentID int = null,
       @LocationID int = null,
       @SubLocationID int = null)
  </code></pre>
  <p>The frustrating part is that this stored procedure works, in SQL Server, in LinqPad with or without parameters. And it works in my code only with ALL the parameters, but if any are null, it throws an error.</p>
  <p>Any insight on how I can get the function to work with optional parameters, is appreciated.</p>
  <h3>Edited: 8/24/2020</h3>
  <p>Based on the new suggestions, I tried them and for some reason they still don't work.</p>
  <p>I've tested the sproc directly in SSMS with all four parameters, and it works.  Then I removed each of the last params one by one and tested it as each was removed and it works returning correct data.</p>
  <p>When I do the same test using the Api and the service call to this method, the four params work, 3 params work, but 2 params, 1 param and no params, don't work!  I don't understand why not.</p>
  <p>Here are my new attempts:</p>
  <pre class="lang-cs prettyprint-override"><code>
              /// New attempts as of: 8/24/2020
              // Error Data is Null. This method or property cannot be called on Null values.
              //var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
              //compId.Value = (object)companyId ?? DBNull.Value;
  
              //var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
              //deptId.Value = (object)departmentId ?? DBNull.Value;
  
              //var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
              //locId.Value = (object)locationId ?? DBNull.Value;
  
              //var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
              //subId.Value = (object)sublocationId ?? DBNull.Value;
  
              //var result = context.BudgetWorkflowStatusByDepartment
              //        .FromSqlInterpolated($&quot;EXEC mis.BudgetWorkflowStatusByDepartment {compId}, {deptId}, {locId}, {subId}&quot;).ToList();
  
              // Error Data is Null. This method or property cannot be called on Null values.
              
              //var result = context.BudgetWorkflowStatusByDepartment
              //        .FromSqlInterpolated($&quot;EXEC mis.BudgetWorkflowStatusByDepartment {companyId}, {departmentId}, {locationId}, {sublocationId}&quot;).ToList();
  
  
              //var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId) { IsNullable = true };
              //compId.Value = (object)companyId ?? DBNull.Value;
  
              //var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId) { IsNullable = true };
              //deptId.Value = (object)departmentId ?? DBNull.Value;
  
              //var locId = new SqlParameter(&quot;@LocationID&quot;, locationId) { IsNullable = true };
              //locId.Value = (object)locationId ?? DBNull.Value;
  
              //var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId) { IsNullable = true };
              //subId.Value = (object)sublocationId ?? DBNull.Value;
  
              //// Error Data is Null. This method or property cannot be called on Null values.
              //var result = context.BudgetWorkflowStatusByDepartment
              //                    .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID = @CompanyID, @DepartmentID = @DepartmentID, @LocationID = @LocationID, @SubLocationID = @SubLocationID&quot;, compId, deptId, locId, subId)
              //                    .ToList();
  
              var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId) { IsNullable = true };
              compId.Value = (object)companyId ?? DBNull.Value;
  
              var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId) { IsNullable = true };
              deptId.Value = (object)departmentId ?? DBNull.Value;
  
              var locId = new SqlParameter(&quot;@LocationID&quot;, locationId) { IsNullable = true };
              locId.Value = (object)locationId ?? DBNull.Value;
  
              var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId) { IsNullable = true };
              subId.Value = (object)sublocationId ?? DBNull.Value;
  
              // Error Data is Null. This method or property cannot be called on Null values.
              var result = context.BudgetWorkflowStatusByDepartment
                                  .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID, @DepartmentID, @LocationID, @SubLocationID&quot;, compId, deptId, locId, subId)
                                  .ToList();
  </code></pre>
  <p>I thought I should include my DbContext property settings for this sproc as additional info.  This same pattern is used for two other sprocs and they work but they don't require optional parameters.</p>
  <pre class="lang-cs prettyprint-override"><code>        public DbSet&lt;BudgetWorkflowStatusByDepartment&gt; BudgetWorkflowStatusByDepartment { get; set; }
  </code></pre>
  <p>And in the OnModelCreating() method I set the <code>HasNoKey()</code> method.</p>
  <pre class="lang-cs prettyprint-override"><code>            modelBuilder.Entity&lt;BudgetWorkflowStatusByDepartment&gt;().HasNoKey();
  </code></pre>
  <p>I appreciate everyone's suggestions.</p>
  <p>My work around on this will be to tell the DBA to change the sproc to remove the parameters all together, and I'll just do a <code>.Where()</code> clause on the result.</p>
  <p>Luckily for this particular feature it won't be returning more than 200 rows, but it will return many columns, but this still should not be taxing.</p>
  <h3>Solved reason</h3>
  <p>It was my fault.  I had some <code>int</code> properties that needed to be nullable ints.  Once I did that, it worked, and it seems to work with most solutions.</p>
  <p>It's important to pay attention to those properties that could be null and make them nullable, so you don't fall prey to my issue, that took an entire day to solve.</p>
  <hr />
  <p>I thought that since I was going through all these attempts at something I hadn't done before (working with optional params with .Net Core 3.1 stored procedures), that I'd document my results with the various options in handling this.</p>
  <p>These are some options on what works and what doesn't when executing a stored procedure from .Net Core 3.1.</p>
  <p>For these examples, this will be the signature of the .Net Core method.</p>
  <pre class="lang-cs prettyprint-override"><code>public List&lt;BudgetWorkflowStatusByDepartment&gt; GetBudgetWorkflowStatusByDepartment
  (int? companyId, int? departmentId, int? locationId, int? sublocationId)
  {
  ...
  }
  
  </code></pre>
  <p>All of the parameters are optional.  The following examples would be the code you could use in this method.</p>
  <p>This is the signature for the stored procedure.</p>
  <pre class="lang-sql prettyprint-override"><code>ALTER procedure mis.BudgetWorkflowStatusByDepartment_Filtered (
        @CompanyID int = null
      , @DepartmentID int = null
      , @LocationID int = null
      , @SubLocationID int = null
  )
  </code></pre>
  <h2>Working Examples</h2>
  <p>The following examples demonstrate how to use either the <code>FromSqlRaw</code> or <code>FromSqlInterpolated</code> methods to execute the stored procedure.</p>
  <h3>FromSqlRaw Methods</h3>
  <p>This example simply creates <code>SqlParameter</code> objects and sets the values to the incoming value, or <code>DbNull</code>.  This is important as <code>null</code> won't work in this case.  (See Non Working Examples below)</p>
  <pre class="lang-cs prettyprint-override"><code>// Works
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  var result = context.BudgetWorkflowStatusByDepartment
  .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID, @DepartmentID, @LocationID, @SubLocationID&quot;, compId, deptId, locId, subId).ToList();
  
  </code></pre>
  <hr />
  <p>The following works by including the <code>IsNullable=true</code> property of the <code>SqlParameter</code> object.</p>
  <pre class="lang-cs prettyprint-override"><code>// Works
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId) { IsNullable = true };
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId) { IsNullable = true };
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId) { IsNullable = true };
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId) { IsNullable = true };
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  var result = context.BudgetWorkflowStatusByDepartment
          .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID = @CompanyID, @DepartmentID = @DepartmentID, @LocationID = @LocationID, @SubLocationID = @SubLocationID&quot;, compId, deptId, locId, subId)
          .ToList();
  </code></pre>
  <hr />
  <p>The following is similar to above but a slightly short-hand syntax method.</p>
  <pre class="lang-cs prettyprint-override"><code>// Works
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId) { IsNullable = true };
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId) { IsNullable = true };
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId) { IsNullable = true };
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId) { IsNullable = true };
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  // Works
  var result = context.BudgetWorkflowStatusByDepartment
          .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID, @DepartmentID, @LocationID, @SubLocationID&quot;, compId, deptId, locId, subId)
          .ToList();
  </code></pre>
  <hr />
  <h3>FromSqlInterpolated</h3>
  <p>The following works because it uses the <code>.FromSqlInterpolated()</code> method and then the placeholder syntax will work.</p>
  <pre class="lang-cs prettyprint-override"><code>// Works
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  var result = context.BudgetWorkflowStatusByDepartment
      .FromSqlInterpolated($&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}&quot;).ToList();
  </code></pre>
  <hr />
  <p>The following is similar to above, but it uses a slightly short-hand syntax.</p>
  <pre class="lang-cs prettyprint-override"><code>// Works
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  var result = context.BudgetWorkflowStatusByDepartment
      .FromSqlInterpolated($&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {compId}, {deptId}, {locId}, {subId}&quot;).ToList();
  </code></pre>
  <hr />
  <p>The following is the ultimate in short-hand syntax.  With the interpolated method, you can directly use the incoming variables and not have to create the <code>SqlParameter</code> objects.</p>
  <pre class="lang-cs prettyprint-override"><code>// Works
  var result = context.BudgetWorkflowStatusByDepartment
      .FromSqlInterpolated($&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {companyId}, {departmentId}, {locationId}, {sublocationId}&quot;).ToList();
  </code></pre>
  <hr />
  <h2>Non Working Examples</h2>
  <p>The following code returns an empty array, which is not correct.  This parameterization of the code can only be used this way with the &quot;interpolated&quot; Sql method.</p>
  <pre class="lang-cs prettyprint-override"><code>
  // Returns empty array
  var result = context.BudgetWorkflowStatusByDepartment
  .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {0}, {1}, {2}, {3}&quot;, companyId, departmentId, locationId, companyId).ToList();
  
  </code></pre>
  <p>Result - an empty array, which is incorrect:</p>
  <pre><code>[]
  </code></pre>
  <hr />
  <p>The following method is incorrect in the syntax of the <code>FromSqlRaw</code> method.  The parameters in the string want to use interpolation, but this is the wrong method for that syntax.</p>
  <pre class="lang-cs prettyprint-override"><code>// Error Must declare the scalar variable &quot;@CompanyID&quot;.
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  var result = context.BudgetWorkflowStatusByDepartment
  .FromSqlRaw($&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}&quot;).ToList();
  </code></pre>
  <p>Error:</p>
  <pre><code>Must declare the scalar variable &quot;@CompanyID&quot;.
  </code></pre>
  <hr />
  <p>The following is also incorrect.  The parameters in the string cannot be populated in this manner.</p>
  <pre class="lang-cs prettyprint-override"><code>// Error Must declare the scalar variable &quot;@CompanyID&quot;.
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
  compId.Value = (object)companyId ?? DBNull.Value;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
  deptId.Value = (object)departmentId ?? DBNull.Value;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
  locId.Value = (object)locationId ?? DBNull.Value;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
  subId.Value = (object)sublocationId ?? DBNull.Value;
  
  var result = context.BudgetWorkflowStatusByDepartment
  .FromSqlRaw($&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {compId}, {deptId}, {locId}, {subId}&quot;).ToList();
  </code></pre>
  <p>Error:</p>
  <pre><code>Must declare the scalar variable &quot;@CompanyID&quot;.
  </code></pre>
  <hr />
  <p>And lastly, this will also not work because the <code>??</code> operator is setting null values to <code>null</code> instead of <code>DbNull.Value</code>.</p>
  <pre class="lang-cs prettyprint-override"><code>
  var compId = new SqlParameter(&quot;@CompanyID&quot;, companyId);
  compId.Value = (object)companyId ?? null;
  
  var deptId = new SqlParameter(&quot;@DepartmentID&quot;, departmentId);
  deptId.Value = (object)departmentId ?? null;
  
  var locId = new SqlParameter(&quot;@LocationID&quot;, locationId);
  locId.Value = (object)locationId ?? null;
  
  var subId = new SqlParameter(&quot;@SubLocationID&quot;, sublocationId);
  subId.Value = (object)sublocationId ?? null;
  
  var result = context.BudgetWorkflowStatusByDepartment
  .FromSqlRaw(&quot;EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID, @DepartmentID, @LocationID, @SubLocationID&quot;, compId, deptId, locId, subId).ToList();
  
  </code></pre>
  <p>Error:</p>
  <pre><code>The parameterized query '(@CompanyID nvarchar(4000),@DepartmentID nvarchar(4000),@Locatio' expects the parameter '@CompanyID', which was not supplied.
  </code></pre>