AGrant AGrant - 3 months ago 14
ASP.NET (C#) Question

Return Parameterized Query Results with MVC and EF

I am trying to use Visual Studio 2013, MVC and Entity Framework to return results of a stored proc to display data to a user. I've already added the EDMX model to my solution and completed the Function Import Wizard for the stored procedure I am looking to execute. What I am stuck on is how to code the Model and Controller and return the data to the View. I don't want to use the generated CRUD operations and scaffolded views EF creates.

Stored Proc:

SELECT C.ClassName, G.Grade
FROM Classes as C
INNER JOIN Grade as G on C.ClassID = G.ClassID
WHERE C.UserID = @UserID


Model:

public class TestModel
{
public string ClassName {get; set;}
public string Grade{get;set;}
}


Controller:

public class TestController : Controller{
public ActionResult Index()
{
TestEntities db = new TestEntities();
var result = db.TestEntities ("@username");
ViewBag.Results = result.ToList();
return View(result.ToList());
}
}


View:

<table>
<h>ViewBag.Results.ClassName</h>
<h>ViewBag.Results.Grade</h>
</table>


This is just the simple code. My references are all in tact.

Answer

If you already map the stored procedure in your EDMX, you must have a method like this on your context:

 [DbFunction("TestEntities", "YourStoreProcedureName")]
 public virtual IQueryable<TestModel> YourStoreProcedureName(int UserID)
 {
        var userIDParameter = UserID != 0?
            new ObjectParameter("UserID", UserId) :
            new ObjectParameter("UserID", typeof(int));

        return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<TestModel>("[TestEntities].[YourStoreProcedureName](@UserId)", userIDParameter);
 }

So, you can call it from your context this way:

TestEntities db = new TestEntities();
var result = db.YourStoreProcedureName(1).ToList();

Check this page to see more details about this.

Now, if you want to call a parametrized query using EF, you can do it as I show below:

 var query =String.Format( @"SELECT C.ClassName, G.Grade
                            FROM Classes as C
                            INNER JOIN Grade as G on C.ClassID = G.ClassID
                            WHERE C.UserID = {0}",UserId);
TestEntities db = new TestEntities();
var result = db.Database.SqlQuery<TestModel>(query).ToList();

And, if you want to call a stored procedure that is not mapped in your EDMX, you can call it this way:

  TestEntities db = new TestEntities();
  var result = db.Database.SqlQuery<TestModel>("YourStoreProcedureName", new SqlParameter("UserId",1)).ToList();