In Entity Framework, use stored procedures (code first)

.net c# entity-framework linq stored-procedures


I use this code to define my stored procedure

(@Country NVARCHAR(20))
    SELECT c.*,O.* from Customers
           as c inner join orders O on c.CustomerID=o.CustomerID

     where  c.Country=@Country 

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.

1/10/2012 6:27:18 PM

Accepted Answer

Take a look at Does Entity Framework Code First support stored procedures? and 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

5/23/2017 12:33:59 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow