Use stored procedure in Entity Framework (code first)

Question

I use this code to define my stored procedure

CREATE PROCEDURE [dbo].[SP]
(@Country NVARCHAR(20))
AS
BEGIN
    SET NOCOUNT ON;
    SELECT c.*,O.* from Customers
           as c inner join orders O on c.CustomerID=o.CustomerID

     where  c.Country=@Country 
END

and this is my C# code:

IList<Entities.Customer> Customers;

using (var context = new NorthwindContext())
{
   SqlParameter categoryParam = new SqlParameter("@Country", "London");
   Customers = context.Database.SqlQuery<Entities.Customer>("SP @Country",  categoryParam).ToList();
}

Problem is here :

I want to message the data from Orders table and my stored procedure generate this to me. How can I get the Orders data in my C# code? Remember I want to execute this stored procedure only once.

score 7 · Accepted Answer · edited May 23 '17 at 12:33

7

Take a look at Does Entity Framework Code First support stored procedures? and http://blogs.msdn.com/b/wriju/archive/2011/05/14/code-first-4-1-using-stored-procedure-to-insert-data.aspx which talk about executing a stored proc via a DbContext object.

I think perhaps your issue is that you aren't getting the orders back as part of your query? is this correct? If so this is because you are only selecting customers. You need to either create an object of the same schema as you expect to be returned from your query (ie that has both customer and order properties) or select into a dynamic type (eww).

Having said that i strongly recommend doing this in linq instead:

from c in context.Customers.Include(c=>c.Orders)
where c.Country == country
select c;

This is a much better approach as you are using EF for what its designed for and not querying for something which doesn't fit your model

edited May 23 '17 at 12:33

Community

1
1

answered Jan 11 '12 at 05:10

undefined

33,537
22
129
198

tnkx for you nice and complete answer.but now ,how can i select dynamic type with linq from my Store procedure. – Mahdi jokar Jan 11 '12 at 06:11
im not 100% sure but something like var customers = context.Database.SqlQuery("SP @Country", categoryParam).ToList(); will probably be about right – undefined Jan 11 '12 at 07:14
tnkx i will try your solution – Mahdi jokar Jan 11 '12 at 07:16

Use stored procedure in Entity Framework (code first)

1 Answers1