Last post Sep 09, 2015 04:55 AM by meeyourmark
Sep 08, 2015 12:58 AM|Janshair Khan|LINK
I'm developing an ASP.NET MVC application. I'm coding an Index action of the account controller whose code is:-
var jobPostings = context.Database.SqlQuery<EGB_JOBPOSTINGS>("EXEC EGB_GETACTIVEJOBPOSTINGS @jobID", new SqlParameter("jobID", 2));
ViewBag.JobInfo = new SelectList(jobPostings, "JobID");
My problem is that, I want to use the stored procedure EGB_GETACTIVEJOBPOSTINGS with Entity Framework using Raw SQL Queries as using SqlQuery. That Stored Procedure getting data from multiple tables through multiple joins. I want to ask that how can I get
the result of the stored procedure while SqlQuery uses only EGB_JOBPOSTINGS class (causes a runtime exception) which is not the only object that Stored Procedure returns.
So what is the optimum way to get through? or How to solve this? Or is there any alternative approach
Sep 08, 2015 03:05 AM|Nasser Malik|LINK
You might be returning multiple result sets and not handling them correctly. Or other big problem is that when we do changes in SP but it's not reflecting in the EF mapping. For that we have to delete and remap SP.
Please read following articles
Stored Procedures in the Entity Framework [Must Read]
Stored Procedures with Multiple Result Sets
The Pitfalls of Mapping the Entity Framework to Stored Procedures
Sep 08, 2015 04:21 AM|Nan Yu|LINK
Hi Janshair ,
want to ask that how can I get the result of the stored procedure while SqlQuery uses only EGB_JOBPOSTINGS class (causes a runtime exception) which is not the only object that Stored Procedure returns.
Yes , that will cause the error since the EF mapping is not correct , you could re-mapping the SP as Nasser suggested . You could also refer to links below for returning multiple result sets from an Entity Framework Stored Procedure with function import
Sep 08, 2015 07:57 AM|Janshair Khan|LINK
Thanks for reply
Actually you're right, I'm trying to access multiple result sets from a single procedures through a parameter. Please can you tell me the problem that I'm confronting in the form of an exception. Here is the code
using (var db = new BSTNPORTAL.BSTNPORTAL())
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "[dbo].[EGB_GETACTIVEJOBPOSTINGS]";
cmd.Parameters.AddWithValue("@jobid", new SqlParameter("jobid", 2));
db.Database.Connection.Open(); //Exception comes here
var reader = cmd.ExecuteReader();
var jobPostings = ((IObjectContextAdapter)db)
.Translate<EGB_JOBPOSTINGS>(reader, "Postings", MergeOption.AppendOnly);
foreach (var item in jobPostings)
var jobCatagories = ((IObjectContextAdapter)db)
.Translate<EGB_JOBCATEGORIES>(reader, "Catagories", MergeOption.AppendOnly);
foreach (var item in jobCatagories)
var classOfStaff = ((IObjectContextAdapter)db)
.Translate<EGB_CLASSOFSTAFF>(reader, "Catagories", MergeOption.AppendOnly);
foreach (var item in classOfStaff)
and Exception is
An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
Additional information: ExecuteReader: Connection property has not been initialized.
I'll be very happy if you solve this issue.
Thanks in advance.
Sep 09, 2015 04:55 AM|meeyourmark|LINK
Please check the DB connection is valid: