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.

Answer 1

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