- You can tell EF to use your stored procedures to handle updates, deletes and inserts when you call your DbContext object SaveChanges method (instead of generated SQL)
- Entity properties are mapped to parameters of stored procedures
- EF creates a command to invoke the mapped stored procedure
- Reverse engineer from existing Db does not pull in Stored Procs
Stored Proc Mapping Conventions
Shortcut: Stored procedures can be generated stored using Code First migrations
protected override void OnModelCreating(DBModel Builder modelBuilder)
{
modelBuilder.Entity<Customer>().MapToStoredProcedures();
base.OnModelCreating(modelBuilder);
}
<EnityType>_<Operation>
Example:
Customer_Insert
Customer_Update
Customer_Delete
- Parameters for sp named same as entity properties
- Insert stored procedure returns the new Id of added entity
Custom Stored Proc Mapping Conventions
- SP Name (HasName)
- Parameters (Parmeter())
- FK Parameters (Parameter() and Navigation())
- Result Field Names (Result())
protected override void OnModelCreating(DBModel Builder modelBuilder)
{
modelBuilder.Entity<Customer>().MapToStoredProcedures(
s=>
s.Insert(i => i.HasName("CustomerINSERT")
.Parameter(c => c.Name, "CustName"))
// pulling foreign key out of navigation property
.Parameter(c => c.Category.Id, "CategoryId")
// pulling value from another entity that contains a collection of customers
// Customer has not property corresponding to Segment
.Navigation<CustomerSegment>(s => s.Customers, c=> c.Parameter(c => c.Id))
.Result(r => r.Id, "NewCustomerId")
)
.Update(u => u.HasName("CustomerUPDATE"))
.Delete(d => d.HasName("CustomerDELETE"))
);
base.OnModelCreating(modelBuilder);
}
Invoking any Stored Procedure
Shortcut: Use the EF Designer: Just select Add | New Item and, in the Add New Item dialog, pick the ADO.NET Entity Data Model. Copy the generated code to your DbContext.
In DbContext:
public virtual int UpdateStats(Nullable<int> CountyId, string Segment)
{
var CountryIdParameter = CountyId.HasValue ?
new ObjectParameter("CountyId", CountyId) :
new ObjectParameter("CountyId", typeof(int));
var SegmentParameter = Segment!= null ?
new ObjectParameter("Segment", Segment) :
new ObjectParameter("Segment", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction(
"UpdateCountrySegment", CountryIdParameter , SegmentParameter );
}
