在CodeFirst 4.1中执行StoredProcedure [英] Execute StoredProcedure in CodeFirst 4.1

问题描述

我理解存储过程映射不支持我的理解是我应该可以调用存储过程。

我有很多复杂的存储过程和设计师我可以创建一个复杂的类型，我都很好。

现在在代码中，我们假设我有以下存储过程，只是把一些愚蠢的东西放在一起理念。我想要返回一个有1个地址的学生。

在代码中我有一个学生和地址实体。但是没有 StudentAddressEntity ，因为它是一个链接表。

我尝试过以下操作，但我收到错误

。附近的语法不正确}

System.Data.Common.DbException {System.Data.SqlClient.SqlException} / p>

  ALTER过程[dbo]。[GetStudentById] 
 @StudentID int 
 AS 
 SELECT * 
 FROM学生S 
在S.Studentid = sa.studentid 
上连接StudentAddress SA左连接SA上的地址A.AddressID = A.AddressID 
其中S.StudentID = @StudentID 
  

C＃代码：

  using（var ctx = new SchoolContext（））
 {
 var student = ctx.Database.SqlQuery< Student>（GetStudentById，@ StudentID ，
 new SqlParameter（StudentID，id））; 
} 
  

任何例子，如何调用sp并在代码中填写一个complexType首先，使用out参数等。我可以挂入ADO.NET吗？

只尝试一个SP，返回所有没有参数的学生，我得到这个错误

System.SystemException =无法为
'StudentAddress'创建
'CodeFirstPrototype.Dal.Address'的值。只有
属性与原始类型支持
。

是因为我有一种方式忽略链接表格？

任何建议？

解决方案

我相信你的例外实际上是：

'，'附近的语法不正确。

因为这是无效语句：GetStudentById，@ StudentID。它应该没有逗号：GetStudentById @StudentID。

EF中存储过程的问题是他们不支持加载导航属性。 EF将仅实现主实体，导航属性将不会被加载。这通过 EFExtensions 解决了。 EFExtensions是为ObjectContext API，所以你必须检查它是否也可用于DbContext API。

I understand stored procedures mapping is not supported by my understanding is that I should be able to call stored procedures.

I have quite a few complex stored procedures and with the designer I could create a complex type and I was all good.

Now in code first let's suppose I have the following stored procedure, just put together something silly to give an idea. I want to return a student with 1 address.

In code I have A Student and Address Entity. But no StudentAddressEntity as it's a link table.

I have tried the following but I get an error

Incorrect syntax near '."}
System.Data.Common.DbException {System.Data.SqlClient.SqlException}

ALTER Procedure [dbo].[GetStudentById]
   @StudentID int
AS
   SELECT  *
   FROM Student S
   left join StudentAddress SA on S.Studentid = sa.studentid
   left join Address A on SA.AddressID = A.AddressID
   where S.StudentID = @StudentID

C# code:

using (var ctx = new SchoolContext())
{
   var student = ctx.Database.SqlQuery<Student>("GetStudentById,@StudentID",
                                                new SqlParameter("StudentID", id));
}

Any examples out there how to call sp and fill a complexType in code first, using out parameters etc.. Can I hook into ADO.NET?

Trying just an SP that returns all students with no parameters I get this error

System.SystemException = Cannot create a value for property 'StudentAddress' of type 'CodeFirstPrototype.Dal.Address'. Only properties with primitive types are supported.

Is it because I have in a way ignore the link table?

Any suggestions?

解决方案

I believe that your exception actually is:

Incorrect syntax near ','.

because this is invalid statement: "GetStudentById,@StudentID". It should be without comma: "GetStudentById @StudentID".

The problem with stored procedures in EF is that they don't support loading navigation properties. EF will materialize only the main entity and navigation properties will not be loaded. This is solved for example by EFExtensions. EFExtensions are for ObjectContext API so you will have to check if it is also usable for DbContext API.

这篇关于在CodeFirst 4.1中执行StoredProcedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！