How to call Stored Procedure in Entity Framework 6 Code-First

Question

I am very new to Entity Framework 6 and I want to implement stored procedures in my project  I have a stored procedure as follows   ALTER PROCEDURE  dbo   insert department       Name  varchar  100  AS BEGIN     INSERT  dbo   Departments   Name       VALUES   Name       DECLARE  DeptId int      SELECT  DeptId    DeptId      FROM  dbo   Departments      WHERE   ROWCOUNT  gt  0 AND  DeptId    SCOPE IDENTITY        SELECT t0  DeptId      FROM  dbo   Departments  AS t0     WHERE   ROWCOUNT  gt  0 AND t0  DeptId     DeptId END   Department class   public class Department       public int DepartmentId   get  set               public string Name   get  set       modelBuilder   Entity lt Department gt      MapToStoredProcedures s   gt   s Update u   gt  u HasName  modify department                    Parameter b   gt  b Department   department id                    Parameter b   gt  b Name   department name       Delete d   gt  d HasName  delete department                    Parameter b   gt  b DepartmentId   department id       Insert i   gt  i HasName  insert department                    Parameter b   gt  b Name   department name       protected void btnSave Click object sender  EventArgs e        string department   txtDepartment text trim            here I want to call the stored procedure to insert values     My problem is  how can I call the stored procedure and pass parameters into it

User · Answer

You are using MapToStoredProcedures   which indicates that you are mapping your entities to stored procedures  when doing this you need to let go of the fact that there is a stored procedure and use the context as normal  Something like this  written into the browser so not tested   using MyContext context   new MyContext          Department department   new Department                 Name   txtDepartment text trim              context Set lt Department gt    Add department       If all you really trying to do is call a stored procedure directly then use SqlQuery

User · Answer

Nothing have to do    when you are creating dbcontext for code first approach initialize namespace below the fluent API area make list of sp and use it another place where you want    public partial class JobScheduleSmsEntities   DbContext       public JobScheduleSmsEntities             base  name JobScheduleSmsEntities                 Database SetInitializer lt JobScheduleSmsEntities gt  new CreateDatabaseIfNotExists lt JobScheduleSmsEntities gt                 public virtual DbSet lt Customer gt  Customers   get  set        public virtual DbSet lt ReachargeDetail gt  ReachargeDetails   get  set        public virtual DbSet lt RoleMaster gt  RoleMasters   get  set         protected override void OnModelCreating DbModelBuilder modelBuilder                  modelBuilder Types   Configure t   gt  t MapToStoredProcedures                modelBuilder Entity lt RoleMaster gt                    HasMany e   gt  e Customers                  WithRequired e   gt  e RoleMaster                  HasForeignKey e   gt  e RoleID                  WillCascadeOnDelete false             public virtual List lt Sp CustomerDetails02 gt  Sp CustomerDetails                   return   IObjectContextAdapter this  ObjectContext ExecuteFunction lt Sp CustomerDetails02 gt   Sp CustomerDetails                this Database SqlQuery lt Sp CustomerDetails02 gt   Sp CustomerDetails            using  JobScheduleSmsEntities db   new JobScheduleSmsEntities                         return db Database SqlQuery lt Sp CustomerDetails02 gt   Sp CustomerDetails   ToList                              public partial class Sp CustomerDetails02       public long  ID   get  set        public string Name   get  set        public string CustomerID   get  set        public long  CustID   get  set        public long  Customer ID   get  set        public decimal  Amount   get  set        public DateTime  StartDate   get  set        public DateTime  EndDate   get  set        public int  CountDay   get  set        public int  EndDateCountDay   get  set        public DateTime  RenewDate   get  set        public bool  IsSMS   get  set        public bool  IsActive   get  set        public string Contact   get  set

User · Answer

This is what EF  DB first  generates in the DbContext class   public ObjectResult lt int gt  Insert Department string department        var departmentParameter   new ObjectParameter  department   department        return   IObjectContextAdapter this  ObjectContext ExecuteFunction lt int gt   insert department   departmentParameter

User · Answer

It work for me at code first  It return a list with matching property of view model StudentChapterCompletionViewModel   var studentIdParameter   new SqlParameter        ParameterName    studentId        Direction   ParameterDirection Input       SqlDbType   SqlDbType BigInt       Value   studentId       var results   Context Database SqlQuery lt StudentChapterCompletionViewModel gt                    exec dbo sp StudentComplettion  studentId                    studentIdParameter                   ToList      Updated for Context  Context is the instance of the class that Inherit DbContext like below   public class ApplicationDbContext   DbContext       public DbSet lt City gt  City   get  set       var Context   new  ApplicationDbContext

User · Answer

Using MySql and Entity framework code first Approach   public class Vw EMIcount       public int EmiCount   get  set        public string Satus   get  set       var result   context Database SqlQuery lt Vw EMIcount gt   call EMIStatus  2018-3-01    2019-05-30     ToList

User · Answer

All you have to do is create an object that has the same property names as the results returned by the stored procedure  For the following stored procedure       CREATE PROCEDURE  dbo   GetResultsForCampaign         ClientId int        AS     BEGIN     SET NOCOUNT ON       SELECT AgeGroup  Gender  Payout     FROM IntegrationResult     WHERE ClientId    ClientId     END   create a class that looks like       public class ResultForCampaign               public string AgeGroup   get  set             public string Gender   get  set             public decimal Payout   get  set            and then call the procedure by doing the following       using var context   new DatabaseContext                      var clientIdParameter   new SqlParameter   ClientId   4                var result   context Database                  SqlQuery lt ResultForCampaign gt   GetResultsForCampaign  ClientId   clientIdParameter                   ToList            The result will contain a list of ResultForCampaign objects  You can call SqlQuery using as many parameters as needed

User · Answer

NET Core 5 0 does not have FromSql instead it has FromSqlRaw All below worked for me  Account class here is Entity in C  with exact same table and column names as in the database  App configuration class as below class AppConfiguration       public AppConfiguration                 var configBuilder   new ConfigurationBuilder            var path   Path Combine Directory GetCurrentDirectory     quot appsettings json quot            configBuilder AddJsonFile path  false           var root   configBuilder Build            var appSetting   root GetSection  quot ConnectionStrings DefaultConnection quot            sqlConnectionString   appSetting Value             public string sqlConnectionString   get  set       DbContext class  public class DatabaseContext   DbContext       public class OptionsBuild               public OptionsBuild                         setting   new AppConfiguration                opsBuilder   new DbContextOptionsBuilder lt DatabaseContext gt                 opsBuilder UseSqlServer setting sqlConnectionString               dbOptions   opsBuilder Options                     public DbContextOptionsBuilder lt DatabaseContext gt  opsBuilder   get  set            public DbContextOptions lt DatabaseContext gt  dbOptions   get  set             private AppConfiguration setting   get  set               public static OptionsBuild ops   new OptionsBuild         public DatabaseContext DbContextOptions lt DatabaseContext gt  options    base options                  disable initializer             Database SetInitializer lt DatabaseContext gt  null              public DbSet lt Account gt  Account   get  set       This code should be in your data access layer  List lt Account gt  accounts   new List lt Account gt     var context   new DatabaseContext DatabaseContext ops dbOptions   accounts   await context Account ToListAsync        direct select from a table  var param   new SqlParameter  quot  FirstName quot    quot Bill quot    accounts   await context Account FromSqlRaw lt Account gt   quot exec Proc Account Select quot    param  ToListAsync                 procedure call with parameter          accounts   context Account FromSqlRaw  quot SELECT   FROM dbo Account quot   ToList       raw query

User · Answer

This works for me by pulling back data from a stored procedure while passing in a parameter     var param   new SqlParameter   datetime   combinedTime   var result             db Database SqlQuery lt QAList gt   dbo GetQAListByDateTime  datetime   param  ToList       db is the dbContext

User · Answer

Mindless passenger  has a project that allows for multiple results sets to be returned from a stored proc using entity framework  One of his examples below      using  testentities te   new testentities            -------------------------------------------------------------        Simple stored proc       -------------------------------------------------------------     var parms1   new testone     inparm    abcd         var results1   te CallStoredProc lt testone gt  te testoneproc  parms1       var r1   results1 ToList lt TestOneResultSet gt

User · Answer

You can pass parameters to sp GetById and fetch the results either in ToList   or FirstOrDefault     var param    new SqlParameter   id   106   var result   dbContext                 Database                 SqlQuery lt Category gt   dbo sp GetById  id   param                  FirstOrDefault

User · Answer

object   xparams                 new SqlParameter   ParametterWithNummvalue   DBNull Value               new SqlParameter   In Parameter    Value                new SqlParameter   Out Parameter   SqlDbType Int   Direction   ParameterDirection Output             YourDbContext Database ExecuteSqlCommand  exec StoreProcedure Name  ParametterWithNummvalue   In Parameter   Out Parameter   xparams           var ReturnValue     SqlParameter params 2   Value

User · Answer

Create Procedure in MYsql   delimiter    create procedure SP Dasboarddata fromdate date  todate date  begin select count Id  as count date status sum amount  as amount from  details where  Emidate between fromdate and todate  group by date  status  END       Create class which contains stored procedure return result set values   Table  SP reslutclass    public  class SP reslutclass        Key      public int emicount   get  set        public DateTime Emidate   get  set        public int   Emistatus   get  set        public int emiamount   get  set         Add Class in Dbcontext    public  class ABCDbContext DbContext       public ABCDbContext DbContextOptions lt ABCDbContext gt  options           base options                 public DbSet lt SP reslutclass gt  SP reslutclass   get  set        Call entity in repository     var counts    Dbcontext SP reslutclass FromSql  call SP Dasboarddata                        2019-12-03   2019-12-31     ToList

User · Answer

When EDMX create this time if you select stored procedured in table select option then just call store procedured using procedured name     var num1   1   var num2   2    var result   context proc name num1 num2  tolist      list or single you get here   using same thing you can call insert update or delete procedured

User · Answer

public static string ToSqlParamsString this IDictionary lt string  string gt  dict                        string result   string Empty              foreach  var kvp in dict                                result        kvp Key    kvp Value                                return result Trim                       public static List lt T gt  RunSproc lt T gt  string sprocName  IDictionary lt string  string gt  parameters                        string command     exec  sprocName   parameters ToSqlParamsString                  return Context Database SqlQuery lt T gt  command  ToList

User · Answer

You can now also use a convention I created which enables invoking stored procedures  including stored procedures returning multiple resultsets   TVFs and scalar UDFs natively from EF       Until Entity Framework 6 1 was released store functions  i e  Table Valued Functions and Stored Procedures  could be used in EF only when doing Database First  There were some workarounds which made it possible to invoke store functions in Code First apps but you still could not use TVFs in Linq queries which was one of the biggest limitations  In EF 6 1 the mapping API was made public which  along with some additional tweaks  made it possible to use store functions in your Code First apps    Read more      I pushed quite hard for the past two weeks and here it is     the beta version of the convention that enables using store functions  i e  stored procedures  table valued functions etc   in applications that use Code First approach and Entity Framework 6 1 1  or newer   I am more than happy with the fixes and new features that are included in this release    Read more

User · Answer

I solved it with ExecuteSqlCommand  Put your own method like mine in DbContext as your own instances   public void addmessage  lt yourEntity gt   msg        var date   new SqlParameter   date    msg MDate       var subject   new SqlParameter   subject    msg MSubject       var body   new SqlParameter   body    msg MBody       var fid   new SqlParameter   fid    msg FID       this Database ExecuteSqlCommand  exec messageinsert  Date    Subject    Body    Fid   date subject body fid       so you can have a method in your code-behind like this     WebMethod    this method is static and i use web method because i call this method from client side public static void AddMessage string Date  string Subject  string Body  string Follower  string Department        try               using  DBContext reposit   new DBContext                          msge  lt yourEntity gt  Newmsg   new msge                Newmsg MDate   Date              Newmsg MSubject   Subject Trim                Newmsg MBody   Body Trim                Newmsg FID  5              reposit addmessage Newmsg                       catch  Exception                throw            this is my SP    Create PROCEDURE dbo MessageInsert       Date nchar  size         Subject nchar  size         Body nchar  size         Fid int AS     insert into Msg  MDate MSubject MBody FID  values   Date  Subject  Body  Fid      RETURN   hope helped you

User · Answer

You can call a stored procedure in your DbContext class as follows   this Database SqlQuery lt YourEntityType gt   storedProcedureName  params     But if your stored procedure returns multiple result sets as your sample code  then you can see this helpful article on MSDN  Stored Procedures with Multiple Result Sets

User · Answer

Using your example  here are two ways to accomplish this   1 - Use Stored procedure mapping  Note that this code will work with or without mapping   If you turn off mapping on the entity  EF will generate an insert   select statement   protected void btnSave Click object sender  EventArgs e         using  var db   DepartmentContext                    var department   new Department             department Name   txtDepartment text trim             db Departments add department           db SaveChanges                EF will populate department DepartmentId         int departmentID   department DepartmentId             2 - Call the stored procedure directly  protected void btnSave Click object sender  EventArgs e         using  var db   DepartmentContext                    var name   new SqlParameter   name   txtDepartment text trim                to get this to work  you will need to change your select inside dbo insert department to include name in the resultset         var department   db Database SqlQuery lt Department gt   dbo insert department  name   name  SingleOrDefault              alternately  you can invoke SqlQuery on the DbSet itself           var department   db Departments SqlQuery  dbo insert department  name   name  SingleOrDefault             int departmentID   department DepartmentId             I recommend using the first approach  as you can work with the department object directly and not have to create a bunch of SqlParameter objects

User · Answer

I found that calling of stored procedures in code-first approach is not convenient  I prefer to use Dapper instead  The following code was written with Entity Framework  var clientIdParameter   new SqlParameter  quot  ClientId quot   4    var result   context Database  SqlQuery lt ResultForCampaign gt   quot GetResultsForCampaign  ClientId quot   clientIdParameter   ToList     The following code was written with Dapper  return Database Connection Query lt ResultForCampaign gt                quot GetResultsForCampaign  quot               new                               ClientId   4                            commandType  CommandType StoredProcedure         I believe the second piece of code is simpler to understand

User · Answer

Take a look to this link that shows how works the mapping of EF 6 with Stored Procedures to make an Insert  Update and Delete  http   msdn microsoft com en-us data dn468673  Addition  Here is a great example to call a stored procedure from Code First   Lets say you have to execute an Stored Procedure with a single parameter  and that Stored Procedure returns a set of data that match with the Entity States  so we will have this   var countryIso    AR     Argentina  var statesFromArgentina   context Countries SqlQuery                                         dbo GetStatesFromCountry  p0   countryIso                                                          Now lets say that we whant to execute another stored procedure with two parameters   var countryIso    AR     Argentina var stateIso    RN     R  o Negro  var citiesFromRioNegro   context States SqlQuery                               dbo GetCitiesFromState  p0   p1   countryIso  stateIso                                Notice that we are using index-based naming for parameters  This is because Entity Framework will wrap these parameters up as DbParameter objects fro you to avoid any SQL injection issues   Hope this example helps

User · Answer

if you wanna pass table params into stored procedure  you must necessary set TypeName property for your table params   SqlParameter codesParam   new SqlParameter CODES PARAM  SqlDbType Structured               SqlParameter factoriesParam   new SqlParameter FACTORIES PARAM  SqlDbType Structured                codesParam Value   tbCodes              codesParam TypeName     dbo   MES CodesType                factoriesParam Value   tbfactories              factoriesParam TypeName     dbo   MES FactoriesType                  var list    context Database SqlQuery lt MESGoodsRemain gt     SP NAME   CODES PARAM    FACTORIES PARAM                     new SqlParameter                        codesParam                     factoriesParam                                     ToList

User · Answer

public IList lt Models StandardRecipeDetail gt  GetRequisitionDetailBySearchCriteria Guid subGroupItemId  Guid groupItemId        var query   this UnitOfWork Context Database SqlQuery lt Models StandardRecipeDetail gt   SP GetRequisitionDetailBySearchCriteria  SubGroupItemId  GroupItemId       new System Data SqlClient SqlParameter   SubGroupItemId   subGroupItemId       new System Data SqlClient SqlParameter   GroupItemId   groupItemId        return query ToList

[c#] How to call Stored Procedure in Entity Framework 6 (Code-First)?

Examples related to c#

Examples related to entity-framework

Examples related to stored-procedures