How do I use and view data from SQL Group By and SQL Aggregates with Dapper?

Question

How do I use and view data from SQL Group By and SQL Aggregates with Dapper?

c# dapper

Question

If I have an SQL statement like,

SELECT Count(id) as TotalRows, transaction_date
FROM Accounts
Group By transaction_date

What do I use as a return type an display it in my View using Dapper Dot Net? I tried using ViewData["mydata"] and I tried using List<dynamic>, but I could not iterate because the return type is IActionResult and as such does not have an Enumerator. I can't cast it (I don't know how to) to a specific type with IEnumerable because there is not custom type like (Accounts, Person, etc.). None of these have a property for enumerating.

I would like to not use Linq unless it is necessary.

johnny

View more on Stack Overflow

Shyju · Accepted Answer · 2/7/2017 7:52:52 PM

Create a POCO to represent your result set item

public class GroupedData
{
   public int TotalRows { set;get;}
   public DateTime TransactionDate { set;get;}
}

Using Dapper, execute the query and load result to a list of this class and pass that to your view

public IActionResult Index()
{
   var groupedDataList = GetData();
   return View(groupedDataList);
}
private List<GroupedData> GetData()
{
   var q=@"SELECT Count(id) as TotalRows, 
               transaction_date as TransactionDate
          FROM Accounts
          Group By transaction_date";
   return yourSqlConnection.Query<GroupedData>(q).ToList();
}

Now make sure your view is strongly typed the same type you are passing from your action method (List of GroupedData). You can enumerate this list.

@model List<GroupedData>
@foreach(var item in Model)
{
  <p>@item.TransactionDate</p>
  <p>@item.TotalRows</p>
}

How do I use and view data from SQL Group By and SQL Aggregates with Dapper?

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator