How to use DbContext Database SqlQuery TElement sql params with stored procedure EF Code First CTP5

Question

I have a stored procedure that has three parameters and I ve been trying to use the following to return the results   context Database SqlQuery lt myEntityType gt   mySpName   param1  param2  param3     At first I tried using SqlParameter objects as the params but this didn t work and threw a SqlException with the following message      Procedure or function  mySpName  expects parameter   param1   which was not supplied    So my question is how you can use this method with a stored procedure that expects parameters   Thanks

User · Answer

I had the same error message when I was working with calling a stored procedure that takes two input parameters and returns 3 values using SELECT statement and I solved the issue like below in EF Code First Approach

 SqlParameter @TableName = new SqlParameter()
        {
            ParameterName = "@TableName",
            DbType = DbType.String,
            Value = "Trans"
        };

SqlParameter @FieldName = new SqlParameter()
        {
            ParameterName = "@FieldName",
            DbType = DbType.String,
            Value = "HLTransNbr"
        };


object[] parameters = new object[] { @TableName, @FieldName };

List<Sample> x = this.Database.SqlQuery<Sample>("EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", parameters).ToList();


public class Sample
{
    public string TableName { get; set; }
    public string FieldName { get; set; }
    public int NextNum { get; set; }
}

UPDATE: It looks like with SQL SERVER 2005 missing EXEC keyword is creating problem. So to allow it to work with all SQL SERVER versions I updated my answer and added EXEC in below line

 List<Sample> x = this.Database.SqlQuery<Sample>(" EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", param).ToList();

User · Answer

I did mine with EF 6 x like this    using var db   new ProFormDbContext                                  var Action   1                   var xNTID    A239333                    var userPlan   db Database SqlQuery lt UserPlan gt                    AD usp UserPlanInfo  Action   NTID        HPID                   new SqlParameter  Action   Action                   new SqlParameter  NTID   xNTID   ToList                      Don t double up on sqlparameter some people get burned doing this to their variable  var Action   new SqlParameter   Action   1       Don t do this  as it is set below already

User · Answer

Also  you can use the  sql  parameter as a format specifier   context Database SqlQuery lt MyEntityType gt   mySpName  param1    0    param1

User · Answer

This solution is  only  for SQL Server 2005  You guys are lifesavers  but as  Dan Mork said  you need to add EXEC to the mix   What was tripping me up was     EXEC    before the Proc Name Commas in between Params  Chopping off     on the Param Definitions  not sure that bit is required though        context Database SqlQuery lt EntityType gt        EXEC ProcName  param1   param2        new SqlParameter  param1   param1        new SqlParameter  param2   param2

User · Answer

You should supply the SqlParameter instances in the following way     context Database SqlQuery lt myEntityType gt        mySpName  param1   param2   param3       new SqlParameter  param1   param1       new SqlParameter  param2   param2       new SqlParameter  param3   param3

User · Answer

Tom Halladay s answer is correct with the mention that you shopuld also check for null values and send DbNullable if params are null as you would get an exception like  The parameterized query       expects the parameter   parameterName   which was not supplied   Something like this helped me  public static object GetDBNullOrValue lt T gt  this T val        bool isDbNull   true      Type t   typeof T        if  Nullable GetUnderlyingType t     null          isDbNull   EqualityComparer lt T gt  Default Equals default T   val       else if  t IsValueType          isDbNull   false      else         isDbNull   val    null       return isDbNull   DBNull Value    object  val       credit for the method goes to https   stackoverflow com users 284240 tim-schmelter   Then use it like   new SqlParameter   parameterName   parameter GetValueOrDbNull      or another solution  more simple  but not generic would be   new SqlParameter   parameterName   parameter   object DBNull Value

User · Answer

Most answers are brittle because they rely on the order of the SP s parameters  Better to name the Stored Proc s params and give parameterized values to those   In order to use Named params when calling your SP  without worrying about the order of parameters  Using SQL Server named parameters with ExecuteStoreQuery and ExecuteStoreCommand  Describes the best approach  Better than Dan Mork s answer here    Doesn t rely on concatenating strings  and doesn t rely on the order of parameters defined in the SP    E g    var cmdText     DoStuff   Name    name param   Age    age param   var sqlParams   new       new SqlParameter  name param    Josh       new SqlParameter  age param   45      context Database SqlQuery lt myEntityType gt  cmdText  sqlParams

User · Answer

db Database SqlQuery lt myEntityType gt   exec GetNewSeqOfFoodServing  p0  p1  p2    foods WEIGHT NDB No  HLP CuntryID  HLP ClientID  Single     or  db Database SqlQuery lt myEntityType gt        exec GetNewSeqOfFoodServing  param1   param2        new SqlParameter  param1   param1        new SqlParameter  param2   param2       or  var cmdText    exec  DoStuff   Name    name param   Age    age param   var  params   new       new SqlParameter  name param    Josh       new SqlParameter  age param   45      db Database SqlQuery lt myEntityType gt  cmdText   params    or  db Database SqlQuery lt myEntityType gt   mySpName  0    1    2    new object     param1  param2  param3    ToList

User · Answer

I use this method   var results   this Database SqlQuery lt yourEntity gt   EXEC  ent   GetNextExportJob   0    ProcessorID     I like it because I just drop in Guids and Datetimes and SqlQuery performs all the formatting for me

User · Answer

return context Database SqlQuery lt myEntityType gt   mySpName  0    1    2    new object     param1  param2  param3         Or   using var context   new MyDataContext      return context Database SqlQuery lt myEntityType gt   mySpName  0    1    2    new object     param1  param2  param3    ToList          Or   using var context   new MyDataContext      object   parameters      param1  param2  param3     return context Database SqlQuery lt myEntityType gt   mySpName  0    1    2    parameters  ToList          Or   using var context   new MyDataContext        return context Database SqlQuery lt myEntityType gt   mySpName  0    1    2    param1  param2  param3  ToList

[c#] How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

Examples related to c#

Examples related to sql

Examples related to ado.net

Examples related to linq-to-entities

Examples related to entity-framework-ctp5