在实体框架中使用存储过程(代码优先)

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:

这是我的c#代码:

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.

我想从Orders表和我的存储过程生成数据。如何在c#代码中获取订单数据?记住，我只想执行这个存储过程一次。

1 个解决方案

#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.

看看实体框架代码是否首先支持存储过程?以及http://blogs.msdn.com/b/wriju/archive/05/14/code first- 4-1-using-storing-procedure -to- insertdata.aspx，讨论通过DbContext对象执行存储的proc。

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).

我想也许你的问题是你没有把订单作为你的查询的一部分拿回来?这是正确的吗?如果是这样，那是因为您只是在选择客户。您需要创建一个与您期望从查询返回的模式相同的对象(即具有客户和订单属性)，或者选择一个动态类型(eww)。

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

我曾说过，我强烈建议在linq中这样做:

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

这是一个更好的方法，因为您正在使用EF为其设计的内容，而不是查询不适合您的模型的内容。

#1