Geek Answers Handbook

Running a stored procedure in CodeFirst 4.1

I understand that displaying stored procedures is not supported, I understand that I should be able to call stored procedures.

I have quite a few complex stored procedures, and with the designer I can create a complex type, and everything was fine.

Now in the code, first suppose that I have the following stored procedure, just collect something stupid to give an idea. I want to return a student with 1 address.

In the code, I have a student and an address entity. But not StudentAddressEntity, since this is a table of links.

I tried the following, but I get an error

Invalid syntax next to "." }
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));
}

, sp complexType , .. ADO.NET?

SP, ,

System.SystemException = 'StudentAddress' 'CodeFirstPrototype.Dal.Address. .

, ?

?

+5
2

, :

','.

: "GetStudentById,@StudentID". : "GetStudentById @StudentID".

EF , . EF , . , , EFExtensions. EFExtensions API ObjectContext, , DbContext API.

+7

EFExtentions :

using (var context = new SchoolContext())
{
    var command = context.CreateStoreCommand("GetStudentById", CommandType.StoredProcedure,
      new SqlParameter("StudentID", id));

    using (command.Connection.CreateConnectionScope())
    using (var reader = command.ExecuteReader())
    {
        // use the reader to read the data
        // my recommendation is to create a Materializer using EFExtensions see 
        // http://blogs.msdn.com/b/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx

        // ex
        var student = Student.Materializer.Materialize(reader).SingleOrDefault();

        return student;
   }
}
0

More articles:


All Articles