Must declare the scalar variable "@historyId" exception

Question

I have a working raw Sql command execution

using (var cmd = SqlUtils.CreateSqlCommand(cmdText, sqlConn))
    {
      cmd.Parameters.Add(new SqlParameter("historyId", SqlDbType.Int)).Value = AAverage.CashFlowRelevantHistoryId;
      cmd.Parameters.Add(new SqlParameter("nodeId", SqlDbType.Int)).Value = AAverage.CashNodeId;
      cmd.Parameters.Add(new SqlParameter("denomId", SqlDbType.Int)).Value = AAverage.DenominationId;
      cmd.Parameters.Add(new SqlParameter("dayId", SqlDbType.Int)).Value = AAverage.CashFlowDayCategoryId;
      cmd.Parameters.Add(new SqlParameter("hour", SqlDbType.Int)).Value = AAverage.Hour;
      cmd.Parameters.Add(new SqlParameter("type", SqlDbType.Int)).Value = AAverage.ValueType;
      cmd.Parameters.Add(new SqlParameter("average", SqlDbType.Real)).Value = AAverage.AverageDerivative;

      using (var reader = cmd.ExecuteReader())
        if (reader.HasRows && reader.Read())
        {
          IDataRecord record = (IDataRecord)reader;
          AAverage.Id = record.GetInt32ByName("Id");
        }
    }

but if I've changed it to use SqlQuery method, I get the error in the subject:

  StringBuilder cmdText = new StringBuilder();
  cmdText.AppendLine("INSERT INTO CashFlowAverageDerivatives");
  cmdText.AppendLine("  (CashFlowRelevantHistoryId, CashNodeId, DenominationId, CashFlowDayCategoryId, Hour, ValueType, AverageDerivative)");
  cmdText.AppendLine("VALUES");
  cmdText.AppendLine("  (@historyId, @nodeId, @denomId, @dayId, @hour, @type, @average)");
  cmdText.AppendLine("SELECT Id FROM CashFlowAverageDerivatives WHERE @@ROWCOUNT > 0 and Id = scope_identity()");
  AAverage.Id = ADbContext.Database.SqlQuery<int>(
    cmdText.ToString(),
    new SqlParameter("historyId", SqlDbType.Int).Value = AAverage.CashFlowRelevantHistoryId,
    new SqlParameter("nodeId", SqlDbType.Int).Value = AAverage.CashNodeId,
    new SqlParameter("denomId", SqlDbType.Int).Value = AAverage.DenominationId,
    new SqlParameter("dayId", SqlDbType.Int).Value = AAverage.CashFlowDayCategoryId,
    new SqlParameter("hour", SqlDbType.Int).Value = AAverage.Hour,
    new SqlParameter("type", SqlDbType.Int).Value = AAverage.ValueType,
    new SqlParameter("average", SqlDbType.Real).Value = AAverage.AverageDerivative
  ).First<int>();

and I have no idea why. If I tried to rename "historyId" in Sql text to something else, exception reports this new name. It seems like parameter definition was missing or misspelled, but list of parameters has been copy&pasted, VS IDE search command finds it, prefix "@" does not matter...

Sure I can live with my working version, but I'm learning EF and it tease me I'm not able to solve this

Answer 1

You were sending the sql parameter as value directly.

When you did this

new SqlParameter("historyId", SqlDbType.Int).Value = AAverage.CashFlowRelevantHistoryId

You actually sent a value of AAverage.CashFlowRelevantHistoryId, not a SqlParameter.

Try changing it with this and see if it works.

AAverage.Id = ADbContext.Database.SqlQuery<int>(
  cmdText.ToString(),
  new SqlParameter("historyId", AAverage.CashFlowRelevantHistoryId),
  new SqlParameter("nodeId", AAverage.CashNodeId),
  new SqlParameter("denomId", AAverage.DenominationId),
  new SqlParameter("dayId", AAverage.CashFlowDayCategoryId),
  new SqlParameter("hour", AAverage.Hour),
  new SqlParameter("type", AAverage.ValueType),
  new SqlParameter("average", AAverage.AverageDerivative)
).First<int>();

Or this

AAverage.Id = ADbContext.Database.SqlQuery<int>(
  cmdText.ToString(),
  new SqlParameter("historyId", SqlDbType.Int) { Value = AAverage.CashFlowRelevantHistoryId },
  new SqlParameter("nodeId", SqlDbType.Int) { Value = AAverage.CashNodeId },
  new SqlParameter("denomId", SqlDbType.Int) { Value = AAverage.DenominationId },
  new SqlParameter("dayId", SqlDbType.Int) { Value = AAverage.CashFlowDayCategoryId },
  new SqlParameter("hour", SqlDbType.Int) { Value = AAverage.Hour },
  new SqlParameter("type", SqlDbType.Int) { Value = AAverage.ValueType },
  new SqlParameter("average", SqlDbType.Real) { Value = AAverage.AverageDerivative }
).First<int>();