using stored procedure in entity framework

Question

I am using asp net mvc 5 and C  with Entity Framework    I have model and domain classes for function    now I need to use stored procedure     which I am struggling at the movement    I am following code first existing database and I have stored procedure written there  My question is how I can call that stored procedure in my web application    Stored procedure   ALTER PROCEDURE  dbo   GetFunctionByID        FunctionId INT   AS BEGIN     SELECT        FROM Functions As Fun     WHERE Function ID    FunctionId END   Domain class    public class Functions        public Functions                    public int Function ID   get  set        public string Title   get  set        public int Hierarchy level   get  set        Function model    Table  Functions    public class App Functions       public App Functions                     Key      public int Function ID   get  set          StringLength 50        Required      public string Title   get  set         public int Hierarchy level   get  set          public virtual ICollection lt App Controllers gt  App Controllers   get  set          BaseContext   public class BaseContext lt TContext gt    DbContext where TContext   DbContext       static BaseContext                 Database SetInitializer lt TContext gt  null              protected BaseContext             base  name   ApplicationDbConnection               Function context   public class FunctionsContext   BaseContext lt FunctionsContext gt        public DbSet lt App Functions gt  Functions   get  set

User · Answer

You can call a stored procedure using SqlQuery  See here      Prepare the query var query   context Functions SqlQuery       EXEC  dbo   GetFunctionByID   p1        new SqlParameter  p1   200        add NoTracking   if required     Fetch the results var result   query ToList

User · Answer

Mindless passenger  has a project that allows you to call a stored proc from entity frame work like this      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             and I am working on a stored procedure framework  here  which you can call like in one of my test methods shown below      TestClass  public class TenantDataBasedTests   BaseIntegrationTest        TestMethod      public void GetTenantForName ReturnsOneRecord                    ARRANGE         const int expectedCount   1          const string expectedName    Me               Build the paraemeters object         var parameters   new GetTenantForTenantNameParameters                       TenantName   expectedName                        get an instance of the stored procedure passing the parameters         var procedure   new GetTenantForTenantNameProcedure parameters               Initialise the procedure name and schema from procedure attributes         procedure InitializeFromAttributes                Add some tenants to context so we have something for the procedure to return          AddTenentsToContext Context               ACT            Get the results by calling the stored procedure from the context extention method          var results   Context ExecuteStoredProcedure procedure               ASSERT         Assert AreEqual expectedCount  results Count            internal class GetTenantForTenantNameParameters        Name  TenantName         Size 100        ParameterDbType SqlDbType VarChar       public string TenantName   get  set        Schema  app     Name  Tenant GetForTenantName    internal class GetTenantForTenantNameProcedure       StoredProcedureBase lt TenantResultRow  GetTenantForTenantNameParameters gt        public GetTenantForTenantNameProcedure          GetTenantForTenantNameParameters parameters            base parameters                  If either of those two approaches are any good

User · Answer

Simple  Just instantiate your entity  set it to an object and pass it to your view in your controller     Entity     VehicleInfoEntities db   new VehicleInfoEntities      Stored Procedure      dbo prcGetMakes       or  you can add any parameters in your stored procedure inside the brackets        dbo prcGetMakes  BMW       Controller  public class HomeController   Controller       VehicleInfoEntities db   new VehicleInfoEntities         public ActionResult Index                 var makes   db prcGetMakes null            return View makes

User · Answer

After importing stored procedure  you can create object of stored procedure pass the parameter like function  using  var entity   new FunctionsContext         var DBdata   entity GetFunctionByID 5  ToList lt Functions gt         or you can also use SqlQuery  using  var entity   new FunctionsContext          var Parameter   new SqlParameter                        ParameterName    FunctionId                        Value   5                     var DBdata   entity Database SqlQuery lt Course gt   exec GetFunctionByID  FunctionId    Parameter  ToList lt Functions gt

User · Answer

Add some tenants to context so we have something for the procedure to return          AddTenentsToContext Context           ACT        Get the results by calling the stored procedure from the context extention method      var results   Context ExecuteStoredProcedure procedure           ASSERT     Assert AreEqual expectedCount  results Count

User · Answer

You need to create a model class that contains all stored procedure properties like below  Also because Entity Framework model class needs primary key  you can create a fake key by using Guid   public class GetFunctionByID        Key      public Guid  GetFunctionByID   get  set            All the other properties      then register the GetFunctionByID model class in your DbContext   public class FunctionsContext   BaseContext lt FunctionsContext gt        public DbSet lt App Functions gt  Functions   get  set        public DbSet lt GetFunctionByID gt  GetFunctionByIds  get set       When you call your stored procedure  just see below    var functionId   yourIdParameter  var result    db Database SqlQuery lt GetFunctionByID gt   GetFunctionByID  FunctionId   new SqlParameter   FunctionId   functionId   ToList

[asp.net-mvc] using stored procedure in entity framework

Examples related to asp.net-mvc

Examples related to entity-framework

Examples related to stored-procedures

Examples related to repository-pattern