This time we will discuss how to create a group by on a datatable to calculate COUNT using LINQ.
The example that I will use is employee attendance data. The table on the left is the raw data of employee attendance data that contains employee name and date in columns. Then the data will be displayed in a summary of the number of days of attendance as in the table on the right.
I've already had a window project with C# . The UI is designed as below:
We'll use LINQ, DataTable, and create a function to convert LINQ result into datatable, so wee need to use/import these classes:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;
Set some UI property under Form1_Load event.
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.AllowUserToAddRows = false;
button1.Text = "Show Data";
}
Function to convert LINQ result into DataTable named LINQResultToDataTable.
public static DataTable LINQResultToDataTable<T>
(IEnumerable<T> Linqlist)
{
DataTable dt = new DataTable();
PropertyInfo[] columns = null;
if (Linqlist == null) return dt;
foreach (T Record in Linqlist)
{
if (columns == null)
{
columns = ((Type)Record.GetType()).GetProperties();
foreach (PropertyInfo GetProperty in columns)
{
Type colType = GetProperty.PropertyType;
if ((colType.IsGenericType) &&
(colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dt.Columns.Add(new DataColumn(GetProperty.Name, colType));
}
}
DataRow dr = dt.NewRow();
foreach (PropertyInfo pinfo in columns)
{
dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ?
DBNull.Value : pinfo.GetValue
(Record, null);
}
dt.Rows.Add(dr);
}
return dt;
}
Then we'll put LINQ query under Button1_Click event.
private void Button1_Click(object sender, EventArgs e)
{
// datatable
DataTable table = new DataTable();
table.Columns.Add("Employee", typeof(string));
table.Columns.Add("DateIn", typeof(DateTime));
//input data
table.Rows.Add("Luki", Convert.ToDateTime("2022-04-01"));
table.Rows.Add("Luki", Convert.ToDateTime("2022-04-02"));
table.Rows.Add("Dona", Convert.ToDateTime("2022-04-01"));
table.Rows.Add("Dona", Convert.ToDateTime("2022-04-02"));
table.Rows.Add("Dona", Convert.ToDateTime("2022-04-03"));
table.Rows.Add("Emil", Convert.ToDateTime("2022-04-01"));
table.Rows.Add("Emil", Convert.ToDateTime("2022-04-02"));
table.Rows.Add("Tina", Convert.ToDateTime("2022-04-01"));
// LINQ query
var query = from row in table.AsEnumerable()
group row by row.Field<string>("Employee") into Employee
orderby Employee.Key
select new
{
Employee = Employee.Key,
TotalIn = Employee.Count()
};
dataGridView1.DataSource = LINQResultToDataTable(query);
}
Complete code for Form1.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;
namespace LinqGroup
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.AllowUserToAddRows = false;
button1.Text = "Show Data";
}
public static DataTable LINQResultToDataTable<T>
(IEnumerable<T> Linqlist)
{
DataTable dt = new DataTable();
PropertyInfo[] columns = null;
if (Linqlist == null) return dt;
foreach (T Record in Linqlist)
{
if (columns == null)
{
columns = ((Type)Record.GetType()).GetProperties();
foreach (PropertyInfo GetProperty in columns)
{
Type colType = GetProperty.PropertyType;
if ((colType.IsGenericType) &&
(colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dt.Columns.Add(new DataColumn(GetProperty.Name, colType));
}
}
DataRow dr = dt.NewRow();
foreach (PropertyInfo pinfo in columns)
{
dr[pinfo.Name] = pinfo.GetValue(Record, null) == null ?
DBNull.Value : pinfo.GetValue
(Record, null);
}
dt.Rows.Add(dr);
}
return dt;
}
private void Button1_Click(object sender, EventArgs e)
{
// datatable
DataTable table = new DataTable();
table.Columns.Add("Employee", typeof(string));
table.Columns.Add("DateIn", typeof(DateTime));
//input data
table.Rows.Add("Luki", Convert.ToDateTime("2022-04-01"));
table.Rows.Add("Luki", Convert.ToDateTime("2022-04-02"));
table.Rows.Add("Dona", Convert.ToDateTime("2022-04-01"));
table.Rows.Add("Dona", Convert.ToDateTime("2022-04-02"));
table.Rows.Add("Dona", Convert.ToDateTime("2022-04-03"));
table.Rows.Add("Emil", Convert.ToDateTime("2022-04-01"));
table.Rows.Add("Emil", Convert.ToDateTime("2022-04-02"));
table.Rows.Add("Tina", Convert.ToDateTime("2022-04-01"));
// LINQ query
var query = from row in table.AsEnumerable()
group row by row.Field<string>("Employee") into Employee
orderby Employee.Key
select new
{
Employee = Employee.Key,
TotalIn = Employee.Count()
};
dataGridView1.DataSource = LINQResultToDataTable(query);
}
}
}
Let's try to run debug.
0 Comments