Using SqlQuery in ASP.NET MVC

Question

I've a query in my controller which fetches two columns from the database and I'm storing it in a variable to return it to the view. I'm displaying the data using table. Following is the code for that:

Controller:

public ActionResult DisplayData(LabourTimeModel ltm)
    {

        LabourTimeEntities db = new LabourTimeEntities();
        string query = "Select ProcessItemName, sum(DATEDIFF(MINUTE, LaborTimeOut,LaborTimeIn)) AS 'TimeSpent' FROM LaborTime group by ProcessItemName";

        var data = db.Database.SqlQuery<LabourTimeModel>(query).ToList();

        return View(data);
    }

View:

@model List<LabourTime.Models.LabourTimeModel>

@{
    ViewBag.Title = "Labour Time - Home";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

    <h2><centre>Display Data</h2><hr />

            <table>
                  <tr>
                     <th colspan="2">Time Spent For Process Item</th>
                  </tr>
                 <tr>
                   <th style="padding:0 15px 0 15px;"> Process Item Name </th>
                      <th style="padding:0 15px 0 15px;"> Time Spent </th>
                   </tr>
                @foreach (LabourTime.Models.LabourTimeModel obj in Model)
                    {
                  <tr>
                   <td style="padding:0 15px 0 15px;">@obj.processItemName.ToString()</td>
                   <td style="padding:0 15px 0 15px;">@obj.TimeSpent</td>
                    </tr>
                    }
           </table>

This gives me the output I want. However, I want to write one more query and pass it to the same view and display one more table like this. How do I achieve this? Any help is appreciated, thanks!

Output that I'm getting now:

Column1     Column2
   A          20
   B          10
   C          45

I want one more table to be displayed below this.

Controller that I tried:

    public ActionResult DisplayData(LabourTimeModel ltm)
    {

        LabourTimeEntities db = new LabourTimeEntities();
        string query = "Select ProcessItemName, sum(DATEDIFF(MINUTE, LaborTimeOut,LaborTimeIn)) AS 'TimeSpent' FROM LaborTime group by ProcessItemName";

string query1 = "ONE MORE QUERY"

        var data = db.Database.SqlQuery<LabourTimeModel>(query).ToList();
        var data1 = db.Database.SqlQuery<LabourTimeModel>(query1).ToList();

        return View(data);
    }

But, I cannot pass two results using return view. I'm stuck here.

Answer 1

You can use anonymous types also to send back to your view

   public ActionResult DisplayData(LabourTimeModel ltm)
{

    LabourTimeEntities db = new LabourTimeEntities();
    string query = "Select ProcessItemName, sum(DATEDIFF(MINUTE, LaborTimeOut,LaborTimeIn)) AS 'TimeSpent' FROM LaborTime group by ProcessItemName";

    string query1 = "ONE MORE QUERY"

    var data = db.Database.SqlQuery<LabourTimeModel>(query).ToList();
    var data1 = db.Database.SqlQuery<LabourTimeModel>(query1).ToList();

    dynamic viewModel = new ExpandoObject();
    viewModel.Query1= data;
    viewModel.Query2=data1;
    return View(viewModel);
}

In your view use it like

@Model.Query1.ColumnName

Answer 2

You need to create view model (a class that has the two lists) and pass that object to the view.

var model = new MyModel {
   Data1 = your first query,
   Data2 = your second query
}

return View(model);

In the view:

@model MyModel