DB view in .NET Core

After much searching, reading, trial and error, I found a way to make this work.

First, I realized that the LINQ expressions were not the way I needed to go. I needed to build the rows individually. Since the data has dynamic columns, a DBSet didn't work. So I built a Pivot query in SQL;

DECLARE @cols NVARCHAR (MAX),
    @StartDate date = '03/01/2017',
    @EndDate date = '03/20/2017',
    @Id nvarchar(10) = 1

SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, entrydate, 106) + 
    ']', 
    '[' + CONVERT(NVARCHAR, entrydate, 106) + ']')
    FROM    (SELECT DISTINCT entrydate 
             FROM EmployeeTask 
             WHERE entrydate >= @StartDate AND entrydate <= @EndDate 
             AND EmployeeId = @Id) PV  
    ORDER BY entrydate DESC

DECLARE @query NVARCHAR(MAX)
SET @query = '          
          SELECT * FROM (SELECT KeyTask, acceptable, entrydate 
                         FROM EmployeeTask 
                         WHERE EmployeeId = ('+ @Id +')
                         Group by KeyTask, acceptable, entrydate) x
          PIVOT (SUM(acceptable) FOR entrydate IN (' + @cols + ')) p
             '     
EXEC SP_EXECUTESQL @query

That gives me the table I want to display, but how to get it to display in MVC 6?

First, I found this SO post and the answer by Petr Vobornik was just what I needed. I had to modify it and added it to my controller:

private static List<Dictionary<string, object>> LoadData(string sqlSelect, 
    params object[] sqlParameters)
    {
        var table = new List<Dictionary<string, object>>();
        using (var ctx = new DBContext())
        {
            ctx.Database.GetDbConnection();
            ctx.Database.OpenConnection();
            using (var cmd = ctx.Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = sqlSelect;
                foreach (var param in sqlParameters)
                    cmd.Parameters.Add(param);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var row = new Dictionary<string, object>();
                        for (int i = 0; i < reader.FieldCount; i++)
                            row[reader.GetName(i)] = reader[i];
                        table.Add(row);
                    }

                    if (table == null)
                    {
                        var row = new Dictionary<string, object>();
                        for (int i = 0; i < 1; i++)
                            row[reader.GetName(i)] = "None";
                        table.Add(row);
                    }
                }
            }
        }
        return table;
    }

The if statement is used to add a blank row so that the list is still generated, but it contains nothing. That way, if the query returns nothing, an empty view can still be loaded (see the View below).

I added the SQL connection in my controller:

public IActionResult Index(int id, string datestart, string dateend)
    {
        HttpContext.Session.SetInt32("EmployeeID", id);

        if(datestart == null)
        {
            var setdate = DateTime.Now;
            datestart = setdate.AddMonths(-3).ToString(); 
        }

        if(dateend == null)
        {
            dateend = DateTime.Now.ToString();
        }

        var start = new SqlParameter("@StartDate", datestart);
        var end = new SqlParameter("@EndDate", dateend);
        var EmpId = new SqlParameter("@Id", id);
        var tasks = LoadData("EXECUTE dbo.TaskView @StartDate, @EndDate, 
                    @Id",start,end,EmpId);

        ViewBag.start = DateTime.Parse(datestart);
        ViewBag.end = DateTime.Parse(dateend);
        return View(tasks);
    }

Next, I modified the View:

@model List<Dictionary<string, object>>

@{
    ViewData["Title"] = "Employee Tasks";
}

<h2>Employee Tasks</h2>

<div class="panel panel-default">
<div class="panel-heading">Search by dates</div>
<div class="panel-body">
    <form asp-controller="EmployeeTasks" asp-action="Index">
        <label>Start Date</label>
        <input type="date" name="datestart" id="start" [email protected]   
            />
        <label>End Date</label>
        <input type="date" name="dateend" id="end" [email protected] />
        <input type="submit" value="Search" />
    </form>
   </div>
</div>

<p>
    <a asp-action="Create">Add New Employee Task</a>
</p>

@if (Model.FirstOrDefault() != null)
{
    <table class="table table-bordered">
    @foreach (var row in Model.FirstOrDefault())
    {
        <th>@row.Key</th>
    }
    @foreach (var row in Model)
    {
        <tr>
        @foreach (var column in row)
        {
            <td>@column.Value</td>  
        }
        </tr>
    }
</table>
}

<div>
    <a asp-controller="Employees" asp-action="Index">Back to Employee 
        Search</a>
</div>

And the final result!

If anyone has an easier method, please post it as an answer so I can try it out, but this works great in .NET Core 1.1.