title: Calling stored procedure from Entity Framework Code First with Database.SqlQuery is not working
body: <p>I am trying to call a stored procedure from my <code>DbContext</code>, but I am getting an error on the SQL that is being generated. The generated TSQL by EF has this error : </p>
  
  <blockquote>
    <p>The parameterized query expects the parameter ..., which was not supplied</p>
  </blockquote>
  
  <p>How could I fix the EF query to create the proper call to the stored procedure?</p>
  
  <p>My code looks like this. It is long but simple. It is declaring first the parameters names and the stored procedure name. Then using the <code>DbContext</code> and <code>Database.SqlQuery</code> to call the stored procedure with created <code>SqlParamter</code>s. At the end its showed the SQL that is being generated by this call.</p>
  
  <pre><code>const string PayerId = "@PayerId";
  const string ProviderId = "@ProviderId";
  const string LineOfBusinessId = "@LineOfBusinessId";
  const string PracticeLocationId = "@PracticeLocationId";
  const string ClinicalCodingCategoryId = "@ClinicalCodingCategoryId";
  const string FacilityName = "@FacilityName";
  const string FacilityType = "@FacilityType";
  const string ServiceDate = "@ServiceDate";
  const string MilesFromPracticeLocation = "@MilesFromPracticeLocation";
  const string GetPossibleSiteofServiceName = "getPossibleSiteofService";
  
  var payerId = new Guid("5a4a16c3-d352-e411-80db-00155d66b519"); 
  var providerId = new Guid("89516ad4-D852-E411-80DB-00155D66B519");
  var lineOfBusinessId = new Guid("a2b3bcf3-9ab3-e311-80c3-00155d66cdf0"); 
  var practiceLocationId = new Guid("36561da7-d852-e411-80db-00155d66b519"); 
  var clinicalCodingCategoryId = new Guid("d541b72f-7ca3-e411-80db-00155d66b519");
  string facilityName = null;
  string facilityType = null;
  Nullable&lt;DateTime&gt; serviceDate = null;
  var milesFromPracticeLocation = 20;
  
  using (var context = new AnalyticContext())
  {
      var payerIdParameter = new SqlParameter(PayerId, payerId);
      var providerIdParameter = new SqlParameter(ProviderId, providerId);
      var lineOfBusinessIdParameter = new SqlParameter(LineOfBusinessId, lineOfBusinessId);
      var practiceLocationIdParameter = new SqlParameter(PracticeLocationId, practiceLocationId);
      var clinicalCodingCategoryIdParameter = new SqlParameter(ClinicalCodingCategoryId, clinicalCodingCategoryId);
      var facilityNameParameter = new SqlParameter(FacilityName, facilityName);
      var facilityTypeParameter = new SqlParameter(FacilityType, facilityType);
      var serviceDateParameter = new SqlParameter(ServiceDate, serviceDate);
      var milesFromPracticeLocationParameter = new SqlParameter(MilesFromPracticeLocation, milesFromPracticeLocation);
  
      var data = context.Database
                     .SqlQuery&lt;PossibleSiteofService&gt;(GetPossibleSiteofServiceName, payerIdParameter, 
                          providerIdParameter, lineOfBusinessIdParameter, 
                          practiceLocationIdParameter, 
                          clinicalCodingCategoryIdParameter, 
                          facilityNameParameter, facilityTypeParameter, 
                          serviceDateParameter, milesFromPracticeLocationParameter)
                     .FirstOrDefault();
              }
  </code></pre>
  
  <p>Generated SQL code :</p>
  
  <pre><code>exec sp_executesql 
  N'getPossibleSiteofService',
  N'@PayerId uniqueidentifier,
  @ProviderId uniqueidentifier,
  @LineOfBusinessId uniqueidentifier,
  @PracticeLocationId uniqueidentifier,
  @ClinicalCodingCategoryId uniqueidentifier,
  @FacilityName nvarchar(4000),
  @FacilityType nvarchar(4000),
  @ServiceDate nvarchar(4000),
  @MilesFromPracticeLocation int',
  @PayerId='5A4A16C3-D352-E411-80DB-00155D66B519',
  @ProviderId='89516AD4-D852-E411-80DB-00155D66B519',
  @LineOfBusinessId='A2B3BCF3-9AB3-E311-80C3-00155D66CDF0',
  @PracticeLocationId='36561DA7-D852-E411-80DB-00155D66B519',
  @ClinicalCodingCategoryId='D541B72F-7CA3-E411-80DB-00155D66B519',
  @FacilityName=default,
  @FacilityType=default,
  @ServiceDate=default,
  @MilesFromPracticeLocation=20
  </code></pre>
  
  <p>The stored procedure is very simple something like this:</p>
  
  <pre><code>[dbo].[getPossibleSiteofService]
      @PayerId UniqueIdentifier, --Required
      @ProviderId UniqueIdentifier, --Required
      @LineOfBusinessId UniqueIdentifier, --Required
      @PracticeLocationId UniqueIdentifier, --Required
      @ClinicalCodingCategoryId UniqueIdentifier, --Required
      @FacilityName varchar(255) = NULL, --Optional
      @FacilityType varchar(255) = NULL, --Optional
      @ServiceDate DateTime = NULL, --Optional Will Default to CURRENT DTM
      @MilesFromPracticeLocation int = NULL --Optional
  AS
  ....
  </code></pre>