How to call stored procedure in MVC by EF

Question

Where can I get good tutorial on Entity framework with Stored Procedure in MVC framework?

Is it better to use Enterprise library in this case when I have almost everything written in the stored procedure.

Note: I am using stored procedure because they are really very complex and some of them is over 1000 lines.

Answer 1

MVC is in this case absolutely not related. The way how you call stored procedure from EF will be still the same. I guess you want to use stored procedures without actually using entities and linq-to-entities (main EF features), don't you? Generally you need:

• EDMX file (ado.net entity data model) where you run update from database and add all stored procedures you want to use. EDMX file also generates derived ObjectContext and all entities by default.
• Next you must go to Model Browser and create Function import for each procedure. Function import will create method on the derived ObjectContext which will allow you call the stored procedure as any other .net method.
• During function import you will have to create complex type (it can happen automatically) for result set returned from stored procedure.

You also don't have to use function imports at all and you can execute procedures directly by calling either:

• objectContext.ExecuteSqlCommand("storedProcedureName", SqlParameters) for SPs not returning record set
• objectContext.ExecuteStoreQuery<ResultType>("storedProcedureName", SqlParameters) for SPs returning record set. ResultType must have properties with same names as columns in result set. It can work only with flat types (no nested objects).

There are some limitations when using stored procedures:

• Entity framework doesn't like stored procedures which returns dynamic result sets (based on some condition result set has different columns)
• Entity framework doesn't support stored procedures returning multiple result sets - there are EFExtensions which does but it is more like doing ADO.NET directly.

Answer 2

If you are using Entityframwork Code-first,This way you can Use your stored-Procedure, In this Example I have four Input parameters.

var startDateTY = masterSales.PolicyStartDate;
var endateTY = masterSales.PolicyEndDate;
var startDatePY = masterSales.PolicyStartDate.Value.AddYears(-1);
var endatePY = masterSales.PolicyEndDate.Value.AddYears(-1);

var spParameters = new object[4];
spParameters[0] = new SqlParameter()
{
     ParameterName = "startDateTY",
     Value = startDateTY
};
spParameters[1] = new SqlParameter()
{
     ParameterName = "endateTY",
     Value = endateTY
};
spParameters[2] = new SqlParameter()
{
     ParameterName = "startDatePY",
     Value = startDatePY
};
spParameters[3] = new SqlParameter()
{
     ParameterName = "endatePY",
     Value = endatePY
};
var datalist = objContext.Database.SqlQuery<vMasterSalesAgentReport>("dbo.usp_GetSalesAgentReport @startDateTY,@endateTY,@startDatePY,@endatePY", spParameters).ToList();