Working with DataTable and LINQ in C#

Bringing SQL-Style Power to In-Memory DataTables

In many real-world C# applications, data often arrives in the form of DataTables – from databases, APIs, or legacy systems.
While traditional DataTable operations use loops or filters, LINQ to DataSet allows you to query, filter, group, and project DataTable contents using modern, SQL-like syntax directly in C#.

This transforms DataTables from clunky, row-based structures into queryable, type-safe data sources.

🔹 What Is LINQ to DataSet?

LINQ to DataSet is a bridge that enables LINQ queries over DataTable and DataSet objects.
It converts these tables into EnumerableRowCollection<DataRow> sequences, allowing developers to write expressive, efficient queries.

Example setup:

using System;
using System.Data;
using System.Linq;

Then:

DataTable table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Department");
table.Columns.Add("Salary", typeof(int));

table.Rows.Add("Alice", "IT", 60000);
table.Rows.Add("Bob", "HR", 45000);
table.Rows.Add("Clara", "IT", 65000);
table.Rows.Add("David", "Finance", 50000);

To make it LINQ-compatible:

var query = from row in table.AsEnumerable()
            where row.Field<int>("Salary") > 50000
            select new
            {
                Name = row.Field<string>("Name"),
                Dept = row.Field<string>("Department"),
                Salary = row.Field<int>("Salary")
            };

foreach (var emp in query)
    Console.WriteLine($"{emp.Name} ({emp.Dept}) - £{emp.Salary}");

Output:

Alice (IT) - £60000
Clara (IT) - £65000

✅ The .AsEnumerable() extension converts the DataTable to a LINQ-friendly sequence.
✅ The .Field<T>() method safely retrieves typed column values.

🔹 Filtering Rows

Use where to filter rows just like SQL WHERE.

Example – Employees in IT Department:

var itEmployees =
    from r in table.AsEnumerable()
    where r.Field<string>("Department") == "IT"
    select r;

You can then access fields:

foreach (var row in itEmployees)
    Console.WriteLine(row.Field<string>("Name"));

Output:

Alice
Clara

🔹 Sorting (OrderBy and ThenBy)

You can sort DataTable rows easily with orderby.

Example – Order Employees by Salary Descending:

var sorted =
    from r in table.AsEnumerable()
    orderby r.Field<int>("Salary") descending
    select r;

Output:

Clara (£65000)
Alice (£60000)
David (£50000)
Bob (£45000)

✅ Combine orderby and thenby for multi-column sorting.

🔹 Grouping and Aggregating DataTables

You can use LINQ’s powerful grouping and aggregate functions directly on DataTables.

Example – Average Salary by Department:

var grouped =
    from r in table.AsEnumerable()
    group r by r.Field<string>("Department") into g
    select new
    {
        Department = g.Key,
        Count = g.Count(),
        AverageSalary = g.Average(x => x.Field<int>("Salary"))
    };

foreach (var dept in grouped)
    Console.WriteLine($"{dept.Department}: {dept.Count} staff, avg £{dept.AverageSalary:F0}");

Output:

IT: 2 staff, avg £62500
HR: 1 staff, avg £45000
Finance: 1 staff, avg £50000

✅ Each group is an IGrouping<string, DataRow>.
✅ Aggregation uses lambda-based Sum(), Average(), and Count().

🔹 Projecting Custom Shapes (Select New)

Just like with objects, you can project data into anonymous types for easier use in reports or UI.

Example – Project Name and Salary Only:

var projection =
    from r in table.AsEnumerable()
    select new
    {
        Employee = r.Field<string>("Name"),
        Salary = r.Field<int>("Salary")
    };

🔹 Joining Multiple DataTables

You can even join multiple DataTables together — a common task in business reporting.

Example – Join Employees with Departments:

DataTable departments = new DataTable();
departments.Columns.Add("Department");
departments.Columns.Add("Manager");

departments.Rows.Add("IT", "James");
departments.Rows.Add("HR", "Karen");
departments.Rows.Add("Finance", "Luke");

var joinQuery =
    from e in table.AsEnumerable()
    join d in departments.AsEnumerable()
    on e.Field<string>("Department") equals d.Field<string>("Department")
    select new
    {
        Name = e.Field<string>("Name"),
        Dept = e.Field<string>("Department"),
        Manager = d.Field<string>("Manager")
    };

foreach (var r in joinQuery)
    Console.WriteLine($"{r.Name} works in {r.Dept} (Manager: {r.Manager})");

Output:

Alice works in IT (Manager: James)
Bob works in HR (Manager: Karen)
Clara works in IT (Manager: James)
David works in Finance (Manager: Luke)

✅ Joins work seamlessly with .AsEnumerable() and Field<T>().

🔹 Converting Query Results Back to DataTable

Sometimes you need your LINQ results back as a DataTable (e.g., for data binding).

Example:

var filtered = from r in table.AsEnumerable()
               where r.Field<int>("Salary") > 50000
               select r;

DataTable result = filtered.CopyToDataTable();

✅ CopyToDataTable() materializes the results into a new DataTable with the same schema.

🔹 Real-World Example – Salary Report

var report =
    from r in table.AsEnumerable()
    group r by r.Field<string>("Department") into g
    orderby g.Key
    select new
    {
        Department = g.Key,
        Employees = g.Count(),
        TotalSalary = g.Sum(x => x.Field<int>("Salary")),
        MaxSalary = g.Max(x => x.Field<int>("Salary"))
    };

foreach (var dept in report)
    Console.WriteLine($"{dept.Department}: {dept.Employees} staff, total £{dept.TotalSalary}, max £{dept.MaxSalary}");

Output:

Finance: 1 staff, total £50000, max £50000
HR: 1 staff, total £45000, max £45000
IT: 2 staff, total £125000, max £65000

📚 Summary

✅ Best Practices

• Always use .AsEnumerable() before LINQ queries on DataTables.
• Use .Field<T>() instead of indexers for strong typing and null safety.
• For large datasets, prefer deferred execution and materialize only when needed.
• Avoid modifying the source DataTable during iteration.
• Use joins and projections to simplify business logic — avoid nested loops.

🧪 Challenge Task

Create two DataTables – Employees and Departments – then:

1. Join them by Department name.
2. Filter for employees with Salary > £50,000.
3. Project new objects containing:
   { Name, Department, Manager, Salary }
4. Display each as:
   “Alice (IT) – Manager: James – £60,000”

👨‍💻 Want More?

Our Advanced C# Course covers:

• LINQ to DataSet and LINQ to XML
• Complex joins and nested queries
• Converting DataTables to strongly typed models
• Integrating LINQ with databases and APIs

Master how to query, transform, and report on data – whether it’s in-memory, relational, or from external sources – using clean, expressive, and modern C# LINQ.