Call stored procedure in MVC

Problem:

Migrating an application with existing database into MVC 5.  Things are going great with EF model but have some reports written as stored procedures and needed a way to call them in the new design.

Solution:

The key is to call sproc using the datacontext from your model.  This can then be put in the controller and the result set is passed out to a viewmodel object which you define in your project.  The view is then run against the view model to display the results.




Controller



var spParameters = new object[5];

spParameters[0] = new SqlParameter()
{
 ParameterName = "office_IDs",
 Value = OfficeIds
};


spParameters[1] = new SqlParameter()
{
 ParameterName = "country",
 Value = country
};

spParameters[2] = new SqlParameter()
{
 ParameterName = "branchofservice",
Value = service
};
spParameters[3] = new SqlParameter()
{
ParameterName = "dateBegin",
Value = dateBegin
};
spParameters[4] = new SqlParameter()
{
ParameterName = "dateEnd",
Value = dateEnd
};

DbContext context = new DbContext("ModelContext");


var datalist = context.Database.SqlQuery<OtherOffenseItemizationModelView>("exec dbo.getOtherOffenseItemization @office_IDs,@country,@branchOfService,@dateBegin,@dateEnd", spParameters).ToList();



return View(datalist);




View


@model IEnumerable<FCJC.ModelView.OtherOffenseItemizationModelView>
<div>
    <table class="table table-striped">
        <tr>
            <th colspan="5" class="text-center table-bordered">
      TITLE
            </th>
        </tr>
        <tr class="alert-info table-bordered">
            <th>
                @Html.DisplayNameFor(model => model.OffenseOther)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.MIL)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.CIV)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.DEP)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.TOTAL)
            </th>
           
        </tr>
        @foreach (var item in Model)
        {
            <tr class="table-bordered">
                <td>
                    @Html.DisplayFor(modelItem => item.OffenseOther)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.MIL)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.CIV)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.DEP)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.TOTAL)
                </td>
            </tr>
        }
    </table>

</div>
<p>
    @Html.ActionLink("Back to List", "Index")
</p>