Monday, 5 August 2013

Data read using Stored Procedure (Entity Framework 4.1 ):

Here, we will use stored procedure to get the Courses by Student. So we will create following "GetCoursesByStudentId" stored procedure:

       -- Add the parameters for the stored procedure here
        @StudentId int = null
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;
            -- Insert statements for procedure here
        select c.courseid,c.coursename,c.Location
        from student s left outer join studentcourse sc
        on sc.studentid = s.studentid
        left outer join course c on c.courseid = sc.courseid
        where s.studentid =@StudentId
Now, you have to perform two steps to use this stored procedure in entity framework.
  1. Add the stored procedure in EDM
  2. Add function import.
Add stored procedure in EDM: As we added tables in the EDM, the same way you can add stored procedures in the EDM. If you want to add it in existing EDM the right click on designer and click on "Update model from database..". This will popup update wizard where you can select stored procedures and add it.

When you click on "Finish", you won't find any changes in the designer that's because stored procedure is not
 being treated as entity. This step will only add stored procedure in storage model. You can see it in XML view of
 the designer.

 Entity Framework

Wait a minute.. still you cannot use this stored procedure because Entity Framework doesn't allow a stored procedure to be queried until it's mapped in the EDM. So now we have to map this stored procedure to the conceptual model. To do that we have to perform second step, "Add function import".

Add function import: Now in this step, we will import a function for the stored procedure. To do that, right click on the designer surface and select "Model Browser". Here you can see your stored procedure by expanding "Stored Procedures" node of SchoolDBModel.Store. Now, right click on your stored procedure and select "Add function import..".

This will popup "Add Function Import":

Here, you can select four types of return values: None, Scalars, Complex and Entities. Let's see each of these:

None: it means stored procedure will not return any value.
Scalars: it means stored procedure will return single value of selected type like binary, Boolean, byte etc.
Complex: It means stored procedure will return complex type which is only on conceptual model but not in database table. You can create complex type here only by first clicking on ‘Get Column Information’ which will get the schema of stored procedure and then click on ‘Create New Complex Type’ which will generate complex type.
Entities: it means stored procedure will return collection of selected entities.

In our case, stored procedure will return collection of Course entity. Click ‘OK’. This will update your conceptual model and bring stored procedure in conceptual model under function import.

Now you can query this stored procedure in entity framework using context as following:

 using (var ctx = new SchoolDBEntities())
        IList<Course> courseList = ctx.GetCoursesByStudentId(1).ToList<Course>();
        //do something with courselist here

Make sure that stored procedure returns the same columns as you have in course entity otherwise it will give you exception. If you use POCO entities then regenerate context from T4 template to include function import in the context.

So this way you can do read operation with stored procedure in entity framework.

for more info about Entity Framework and Demo Project Click Here


Post a Comment


ADFS (1) ADO .Net (2) Ajax (1) Angular (1) Angular Js (17) Angular2 (28) ASP .Net (14) Authentication (1) Azure (1) Breeze.js (1) C# (50) CD (1) CI (2) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DevOps (4) DI (4) Dotnet (22) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (8) Lamda (3) Linq (11) Mongodb (1) MVC (50) NodeJS (7) RDLC (1) Report (1) SDLC (1) Sql Server (30) SSIS (3) SSO (1) SSRS (2) UI (1) WCF (13) Web Api (11) Web Service (1) XMl (1)

Dotnet Guru Archives