Stored Procedures
The following example shows how to call a stored procedure using Entity Framework.
Calling Stored Procedures with SQL
To execute a stored procedure, you execute SQL directly through the Entity Framework context: Define a class to capture the results of the stored procedure; for example, the Name class shown below. This class should define a property for each output column that you are interested in using. Once this class is defined, you can invoke the ExecuteStoreQuery method of the Entity Framework context.
C#
public class SearchSuppliersOutput {
public string Name { get; set; }
// ... add other return values with matching data type
}
public class Program {
static void Main(string[] args) {
using (CompositeEntities context = new CompositeEntities())
{
//use the line below for versions earlier than EF 5:
//IEnumerable<SearchSuppliersOutput> result = context.ExecuteStoreQuery<SearchSuppliersOutput>("EXEC SearchSuppliers @Country = ?;",new CompositeParameter("Country", "US"));
//use the line below for EF 5 and 6:
IEnumerable<SearchSuppliersOutput> result = context.Database.SqlQuery<SearchSuppliersOutput>("EXEC SearchSuppliers @Country = ?", new CompositeParameter("Country", "US"));
List<SearchSuppliersOutput> resultList = result.ToList<SearchSuppliersOutput>();
foreach (SearchSuppliersOutput value in resultList) {
Console.WriteLine("Name: " + value.Name);
}
}
}
}
VB.NET
Public Class SearchSuppliersOutput
Public Property Name() As String
' ... add other return values with matching data type
End Class
Module Program
Sub Main
Using context As New CompositeEntities
'use the line below for versions earlier than EF 5:
'Dim result As IEnumerable(Of SearchSuppliersOutput) = context.ExecuteStoreQuery(Of SearchSuppliersOutput)("EXEC SearchSuppliers @Country = ?;", New CompositeParameter("Country", "US"))
'use the line below for EF 5 and 6:
Dim result As IEnumberable(Of SearchSuppliersOutput) = context.Database.SqlQuery(Of SearchSuppliersOutput)("EXEC SearchSuppliers @Country = ?;", New CompositeParameter("Country", "US"))
Dim resultList As List(Of SearchSuppliersOutput) = result.ToList()
For Each value As SearchSuppliersOutput In resultList
Console.WriteLine("Name: " + value.Name)
Next
End Using
End Sub
End Module
You can avoid using CompositeParameter by passing in the parameters directly, as shown in the following example:
"EXEC SearchSuppliers @Country=US, ..."