locked
Using Stored Procedures with Entity Framework using Raw SQL Joins. RRS feed

  • Question

  • User-491950272 posted

    Hi Everyone,

    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");
    return View();

    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

    Thanks

    Tuesday, September 8, 2015 12:58 AM

Answers

All replies

  • User-183374066 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 8, 2015 3:05 AM
  • User1724605321 posted

    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 mappings :
    http://www.codeproject.com/Articles/675933/Returning-Multiple-Result-Sets-from-an-Entity-Fram .

    http://www.binaryintellect.net/articles/30738a7c-5176-4333-aa83-98eab8548da5.aspx .

    Best Regards,

    Nan Yu

    Tuesday, September 8, 2015 4:21 AM
  • User-491950272 posted

    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())
    {
    db.Database.Initialize(force: false);

    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "[dbo].[EGB_GETACTIVEJOBPOSTINGS]";
    cmd.Parameters.AddWithValue("@jobid", new SqlParameter("jobid", 2));

    try
    {
    db.Database.Connection.Open();           //Exception comes here
    var reader = cmd.ExecuteReader();

    var jobPostings = ((IObjectContextAdapter)db)
    .ObjectContext
    .Translate<EGB_JOBPOSTINGS>(reader, "Postings", MergeOption.AppendOnly);

    foreach (var item in jobPostings)
    {
    Console.WriteLine(item.JOBID);
    }

    reader.NextResult();
    var jobCatagories = ((IObjectContextAdapter)db)
    .ObjectContext
    .Translate<EGB_JOBCATEGORIES>(reader, "Catagories", MergeOption.AppendOnly);

    foreach (var item in jobCatagories)
    {
    Console.WriteLine(item.JOBCATEGORYID);
    }

    reader.NextResult();
    var classOfStaff = ((IObjectContextAdapter)db)
    .ObjectContext
    .Translate<EGB_CLASSOFSTAFF>(reader, "Catagories", MergeOption.AppendOnly);

    foreach (var item in classOfStaff)
    {
    Console.WriteLine(item.STAFFCLASSID);
    }

    }
    finally
    {
    db.Database.Connection.Close();
    }
    }

    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.

    Tuesday, September 8, 2015 7:57 AM
  • User-693045842 posted

    Hi ,

    Please check the  DB connection is valid:

    http://stackoverflow.com/questions/19211082/testing-an-entity-framework-database-connection .

    Wednesday, September 9, 2015 4:55 AM