What is happening when dbcontext.Database.ExecuteSqlCommand is called?

Question

I am using Entity Framework Code First from database. I'm trying to run an existing unmapped stored procedure, using dbcontext.Database.ExecuteSqlCommand, dynamically creating the query and parameter array based on values passed in from the JSON (there are many optional parameters). I am not expecting a return value.

When I test from my service, I get SqlExceptions being thrown from within the stored proc:

Cannot insert the value NULL into column 'yyyyww', table 'dbname.dbo.table'; column does not allow nulls.

But when I run the same query using the same values from SSMS '12, there are no errors. The table does actually allow nulls for that column.

exec [dbname].[dbo].[storedproc] @yyyymm = 201409, @s_id = 75

Passing an optional string parameter causes an exception about converting nvarchar to int, which doesn't happen in the stored proc. I've tried a few different ways to set up the call, but they all throw the same exceptions.

version 1:

string query1 = "exec [dbname].[dbo].[storedproc] @yyyymm, @s_id";

List<SqlParameter> parms1 = new List<SqlParameter>();
parms1.Add(new SqlParameter("@yyyymm", obj.yyyymm));
parms1.Add(new SqlParameter("@s_id", obj.s_Id));

db.Database.ExecuteSqlCommand(query, parms1.ToArray());

version 2:

string query2 = "exec [dbname].[dbo].[storedproc] @yyyymm, @s_id";

var month = new SqlParameter("yyyymm", SqlDbType.Int);
month.Value = obj.yyyymm;
var s_id = new SqlParameter("s_id", SqlDbType.Int);
s_id.Value = obj.s_Id;

List<SqlParameter> parms2 = new List<SqlParameter>();
parms2.Add(month);
parms2.Add(s_id);

db.Database.ExecuteSqlCommand(query, parms2.ToArray());

version 3:

string query3 = "exec [dbname].[dbo].[storedproc] @yyyymm = {0}, @s_id = {1}";
db.Database.ExecuteSqlCommand(query3, obj.yyyymm, obj.s_Id);

Any thoughts on what is happening - why the values are being treated differently? Is there a better way to do this?

Answer 1

Here is a working example from some code I have used:

public int SalesByCategory(string categoryName, string ordYear)
    {
        var categoryNameParameter = categoryName != null ?
            new SqlParameter("@CategoryName", categoryName) :
            new SqlParameter("@CategoryName", typeof (string));

        var ordYearParameter = ordYear != null ?
            new SqlParameter("@OrdYear", ordYear) :
            new SqlParameter("@OrdYear", typeof (string));

        return Database.ExecuteSqlCommand("SalesByCategory @CategoryName, @OrdYear", categoryNameParameter, ordYearParameter);
    }

If that structure doesn't work for some reason, try turning the SQL Profiler on to see exactly what is happening.

---

Edit:
I originally got this code from this awesome project by Long Le. I've been using the framework for a while now and it is fantastic.