ef 6 and parameterized stored procedures

I am new to entity framework and need some help with stored procs.  can anyone show me or point me to a tutorial that shows how to use ef 6 to create a model that uses parameterized stored procs and get the results into a view?

I have a stored proc that takes 3 input parameters and returns a result set built from 2 different tables.  this is what I want to call in a view.
dhenderson12Asked:
Who is Participating?
 
Ioannis ParaskevopoulosCommented:
Hi,

First of all you would need to create a model that would fit the results of your stored procedure.

For instance let's say that the stored procedure would return an id and a name. You would have to create a model (a class):

public class MySPModel
{
    public int id {get;set;}
    public string name {get;set;}
}

Open in new window


Now you may use a method in your datacontext.DataBase that enables you to run custom queries. Custom queries van be whatever you would write on an SSMS query window. A simple sql query, or exec a stored procedure.

For this do the following in your controller:

public ActionResult Index(int id)
{
    var idParam = new SqlParameter
    {
        ParameterName = "id",
        Value = id
    };
    
    var results = datacontext.DataBase.SqlQuery<MySPModel>("dbo.myStoredProcedureName @id", idParam).ToList();

    return View(results);
}

Open in new window


Now add a view:

@model List<MySPModel>

<table>
@foreach(var model in Model)
{
<tr>
    <td>@model.id</td>
    <td>@model.name</td>
</tr>
}
</table>

Open in new window


As you can understand my example only uses one param, but likewise you can add the other two. If your stored procedure returns from a specific table (lets call it simply TableName) then you could miss the creation of the model as it is already in your edml. so you could call the SqlQuery as:

var results = datacontext.DataBase.SqlQuery<TableName>("dbo.myStoredProcedureName @id", idParam).ToList();

Open in new window


Hope it helps,

Giannis
0
 
Bob LearnedCommented:
There is a way to generate the code from the context designer:

Calling Stored Procedures from Entity Framework
https://visualstudiomagazine.com/articles/2014/04/01/calling-stored-procedures-from-entity-framework.aspx

The easiest way to work with stored procedures that perform an update is to use the EF Designer...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.