Entity Framework can execute stored procedures in SQL Server. For example, I have a simple stored procedure as following:
1 2 3 4 5 |
CREATE PROCEDURE ListAllClients AS BEGIN SELECT * FROM dbo.Clients; END |
In Code First model this can be done by different ways depending on whether you want to get a resultset from a stored procedure.
1. SqlQuery (returning a resultset)
1 |
var records = ctx.Clients.SqlQuery("dbo.ListAllClients"); |
or
1 |
var records = ctx.Database.SqlQuery<Client>("dbo.ListAllClients"); |
If you need to pass a parameters, you need to add a parameter name after the name of stored procedure and supply SqlParameter. The command will be like this:
1 2 |
var records = ctx.Database.SqlQuery<Client>("dbo.ListClientsByCountry @CountryCode", new SqlParameter("@CountryCode", "US")); |
[UPDATE 2016-08-24]
SqlQuery returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery.
2. ExecuteSqlCommand (no resultset)
If you want to execute a stored procedure or an SQL command that won’t return a result set, you can call this function.
1 2 |
ctx.Database.ExecuteSqlCommand("dbo.UpdateCountryCode @CountryCode", new SqlParameter("@CountryCode", "DE")); |
3. Low-level methods through ObjectContext
There’s an excellent article on MSDN Stored Procedures with Multiple Result Sets that describes how to get a couple of resultsets with the help of Database.Connection.CreateCommand and ObjectContext.Translate.