How to call Stored Procedures with EntityFramework

Question

I have generated an EF4 Model from a MySQL database and I have included both StoredProcedures and Tables   I know how to make regular instert update fetch delete operations against the EF but I can t find my StoredProcedures   This was what I was hoping for    using  Entities context   new Entities          context MyStoreadProcedure Parameters        Edit 1    This is how it looked without EF    sqlStr    CALL updateGame                   commandObj   new OdbcCommand sqlStr  mainConnection   commandObj Parameters Add   id    OdbcType Int  Value   inGame id  commandObj Parameters Add   name   OdbcType VarChar  255  Value   inGame name  commandObj Parameters Add   description   OdbcType Text  Value         inGame description  commandObj Parameters Add   yearPublished   OdbcType DateTime  Value   inGame yearPublished  commandObj Parameters Add   minPlayers   OdbcType Int  Value   inGame minPlayers  commandObj Parameters Add   maxPlayers   OdbcType Int  Value   inGame maxPlayers  commandObj Parameters Add   playingTime   OdbcType VarChar  127  Value   inGame playingTime       return Convert ToInt32 executeScaler commandObj      PS  I can change EF version if needed  Edit 1    CREATE DEFINER  106228      PROCEDURE  updateGame       inId INT      inName VARCHAR 255       inDescription TEXT      inYearPublished DATETIME      inMinPlayers INT      inMaxPlayers INT      inPlayingTime VARCHAR 127

User · Answer

This is what I recently did for my Data Visualization Application which has a 2008 SQL Database. In this example I am recieving a list returned from a stored procedure:

public List<CumulativeInstrumentsDataRow> GetCumulativeInstrumentLogs(RunLogFilter filter)
    {
        EFDbContext db = new EFDbContext();
        if (filter.SystemFullName == string.Empty)
        {
            filter.SystemFullName = null;
        }
        if (filter.Reconciled == null)
        {
            filter.Reconciled = 1;
        }
        string sql = GetRunLogFilterSQLString("[dbo].[rm_sp_GetCumulativeInstrumentLogs]", filter);
        return db.Database.SqlQuery<CumulativeInstrumentsDataRow>(sql).ToList();
    }

And then this extension method for some formatting in my case:

public string GetRunLogFilterSQLString(string procedureName, RunLogFilter filter)
        {
            return string.Format("EXEC {0} {1},{2}, {3}, {4}", procedureName, filter.SystemFullName == null ? "null" : "\'" + filter.SystemFullName + "\'", filter.MinimumDate == null ? "null" : "\'" + filter.MinimumDate.Value + "\'", filter.MaximumDate == null ? "null" : "\'" + filter.MaximumDate.Value + "\'", +filter.Reconciled == null ? "null" : "\'" + filter.Reconciled + "\'");

        }

User · Answer

Based up the OP s original request to be able to called a stored proc like this     using  Entities context   new Entities          context MyStoreadProcedure Parameters        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

This is an example of querying MySQL procedure using Entity Framework  This is the definition of my Stored Procedure in MySQL   CREATE PROCEDURE GetUpdatedAds       IN curChangeTracker BIGINT     IN PageSize INT    BEGIN    -- select some recored    END    And this is how I query it using Entity Framework    var curChangeTracker   new SqlParameter   curChangeTracker   MySqlDbType Int64  Value   0   var pageSize   new SqlParameter   PageSize    MySqlDbType Int64   Value   100    var res    context Database SqlQuery lt MyEntityType gt    call GetUpdatedAds  curChangeTracker    pageSize        Note that I am using C  String Interpolation to build my Query String

User · Answer

Basically you just have to map the procedure to the entity using Stored Procedure Mapping   Once mapped  you use the regular method for adding an item in EF  and it will use your stored procedure instead   Please see  This Link for a walkthrough  The result will be adding an entity like so  which will actually use your stored procedure   using  var ctx   new SchoolDBEntities                          Student stud   new Student                stud StudentName    New sp student               stud StandardId   262               ctx Students Add stud               ctx SaveChanges

User · Answer

One way is to use the Database property off the DbContext   SqlParameter param1   new SqlParameter   firstName    Frank    SqlParameter  param2   new SqlParameter   lastName    Borland    context Database ExecuteSqlCommand  sp MyStoredProc  firstName   lastName                                  param1  param2     EF5 definitely supports that

User · Answer

Once your stored procedure is imported in your model  you can right click in it  from the model browser  in the Context Store Stored Procedures section   and click Add Function Import  If you need a complex type as a result  you can create it right there

User · Answer

You have use the SqlQuery function and indicate the entity to mapping the result   I send an example as to perform this   var oficio  new SqlParameter       ParameterName    pOficio       Value    0001      using  var dc   new PCMContext          return dc Database               SqlQuery lt ProyectoReporte gt   exec SP GET REPORTE  pOficio                                           oficio                ToList

[c#] How to call Stored Procedures with EntityFramework?

Examples related to c#

Examples related to mysql

Examples related to entity-framework

Examples related to stored-procedures