Calling a Stored Procedure/Function in Entity Framework using DBContext

Executing a stored procedure/function is easier in entity framework. The following steps explain how to call a stored procedure/function and return single or tabular values.

Step 1: Create an entity class which inherits “DbContext” class.

  1. public partial class MyDatabaseEntites: DbContext {  
  2.   
  3.     public MyDatabaseEntites(): base("name=MyDatabaseEntites") {  
  4.   
  5.     }  
  6.   
  7.     public DbSet < Table1 > Table1DbSet {  
  8.         get;  
  9.         set;  
  10.     }  
  11.     public DbSet < Table2 > Table2DbSet {  
  12.         get;  
  13.         set;  
  14.     }............  
  15.   
  16. }  
Step 2: The following is the structure of the database with table and stored procedure.

table
  1. CREATE PROCEDURE GetTable1Data  
  2. AS   
  3. SELECT * FROM Table1  
  4. GO  
Step 3: Create a class to store the returned tabular value.
  1. public class Table1Results   
  2. {   
  3.    public int Column1 {getset;}   
  4.    public string Column2 {getset;}   
  5.    public string Column3 {getset;}   
  6.    public string Column4 {getset;}   
  7. }   
Step 4: Create an object for the entity above and method to call a function.
  1. public class RepositoryClass {  
  2.     private readonly DbContext myDatabaseReposityContext = null;  
  3.   
  4.     public RepositoryClass() {  
  5.         myDatabaseReposityContext = new MyDatabaseEntites();  
  6.     }  
  7.   
  8.     public List < Table1Results > GetTable1DataFromProc() {  
  9.         var query = "Exec GetTable1Data";  
  10.   
  11.         object[] parameters = new object[] {};  
  12.   
  13.         var response = myDatabaseReposityContext.Database.SqlQuery < Table1Results > (query, parameters).ToList();  
  14.   
  15.         return response;  
  16.     }  
  17. }  
Step 5: Call the “RepositoryClass” class and “GetTable1DataFromProc” method.
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now