0

There are many articles that show how to create Insert, Update, Delete procedures using Code First like this one.

How about custom procedure like this simple select statement:

Select * from Customers

I could make changes to the Up and Down Migration methods, but is there a way to create custom procs using Fluent API directly.

usefulBee
  • 9,250
  • 10
  • 51
  • 89
  • for more info about DbContext.Database.ExecuteSqlCommand check this: https://msdn.microsoft.com/en-us/library/gg679456(v=vs.113).aspx – Jalal Oct 15 '15 at 18:02

1 Answers1

2

Here is a solution which I don't recommand but If you want to use DbContext.Database, here it is:

using(var db = new MyDbContext(connectionString))
{
    db.Database.ExecuteSqlCommand("CREATE PROCEDURE MyProcedure ... END;");

    var command = "EXEC MyProcedure;";
    IEnumerable<Customer> customers = db.Database.SqlQuery<Customer>(command, null);
}
Jalal
  • 6,594
  • 9
  • 63
  • 100
  • excellent but in this case, how calling this procedure will map to a model that does not even exist? I mean the ExecuteSqlCommand creates the proc in db but then the proc result will have no class representation in the Model. – usefulBee Oct 15 '15 at 18:07
  • 1
    use `DbContext.Database.SqlQuery` to map procedure result into your model. I have updated my answer – Jalal Oct 15 '15 at 18:10
  • In Database First, EntityFramework used to create a model that represent the proc result for example: GetCustomers_Result. Or do I have to create this manually? – usefulBee Oct 15 '15 at 18:10
  • @usefulBee since you can still use `IDatabaseInitializer.InitializeDatabase(TContext context)` to seed database, It would be hard to maintain or handle migrations for stored procedure code in this way. – Jalal Oct 15 '15 at 18:43
  • 1
    I found the following techinque to be helpfull as well for calling the stored proc without passing a string: var result = db.Set().ToList(); as found here: http://stackoverflow.com/a/20905566/2093880 – usefulBee Oct 15 '15 at 18:57