Introduction

DataTable is an older but still common .NET container for in-memory tabular data. LINQ makes it much easier to filter, sort, and project rows than manual loops do, but the key detail is that a DataTable must first be exposed as an enumerable sequence of DataRow values.

Why AsEnumerable Matters

DataTable.Rows is not directly a generic IEnumerable<DataRow>, so standard LINQ operators do not light up until you call AsEnumerable(). That extension method is defined in System.Data.DataSetExtensions.

1using System;
2using System.Data;
3using System.Linq;
4
5DataTable table = new DataTable();
6table.Columns.Add("Name", typeof(string));
7table.Columns.Add("Age", typeof(int));
8
9table.Rows.Add("Ava", 30);
10table.Rows.Add("Ben", 22);
11table.Rows.Add("Cara", 35);
12
13var adults = table.AsEnumerable()
14    .Where(row => row.Field<int>("Age") >= 30);
15
16foreach (DataRow row in adults)
17{
18    Console.WriteLine($"{row.Field<string>("Name")} is {row.Field<int>("Age")}");
19}

Using Field<T> is better than indexing with row["Age"] followed by a cast. It is clearer, strongly typed, and handles DBNull more predictably when used with nullable types such as int?.

Filtering and Sorting Rows

Once the table is enumerable, you can compose ordinary LINQ queries. Filtering and sorting are the most common tasks:

1using System;
2using System.Data;
3using System.Linq;
4
5DataTable table = new DataTable();
6table.Columns.Add("Department", typeof(string));
7table.Columns.Add("Salary", typeof(decimal));
8
9table.Rows.Add("Engineering", 95000m);
10table.Rows.Add("Support", 60000m);
11table.Rows.Add("Engineering", 120000m);
12
13var highEarners = table.AsEnumerable()
14    .Where(row => row.Field<string>("Department") == "Engineering")
15    .OrderByDescending(row => row.Field<decimal>("Salary"));
16
17foreach (DataRow row in highEarners)
18{
19    Console.WriteLine($"{row.Field<string>("Department")} {row.Field<decimal>("Salary")}");
20}

This pattern is much more maintainable than nested for loops with repeated casting. It also makes the intent of the code obvious to the next developer.

Projecting into Stronger Shapes

You are not limited to returning DataRow values. A common improvement is to project rows into anonymous objects or domain types:

1using System;
2using System.Data;
3using System.Linq;
4
5var employees = table.AsEnumerable()
6    .Select(row => new
7    {
8        Department = row.Field<string>("Department"),
9        Salary = row.Field<decimal>("Salary")
10    })
11    .ToList();
12
13foreach (var employee in employees)
14{
15    Console.WriteLine($"{employee.Department}: {employee.Salary}");
16}

Projection is useful when you want type-safe downstream code and do not want other methods reaching back into the raw DataRow.

Aggregation and Grouping

LINQ is also a clean way to summarize table data. Grouping by one column and computing counts or totals is concise:

1using System;
2using System.Data;
3using System.Linq;
4
5var salaryByDepartment = table.AsEnumerable()
6    .GroupBy(row => row.Field<string>("Department"))
7    .Select(group => new
8    {
9        Department = group.Key,
10        Count = group.Count(),
11        TotalSalary = group.Sum(row => row.Field<decimal>("Salary"))
12    });
13
14foreach (var item in salaryByDepartment)
15{
16    Console.WriteLine($"{item.Department} has {item.Count} employees and total salary {item.TotalSalary}");
17}

That pattern replaces a fair amount of manual dictionary bookkeeping. It is especially useful in reporting code and legacy migration work where DataTable is still the integration boundary.

Creating a Filtered DataTable Again

Sometimes the result must remain a DataTable because another API expects one. In that case, use CopyToDataTable() after filtering:

1using System;
2using System.Data;
3using System.Linq;
4
5var filteredRows = table.AsEnumerable()
6    .Where(row => row.Field<decimal>("Salary") >= 90000m);
7
8DataTable filteredTable = filteredRows.Any()
9    ? filteredRows.CopyToDataTable()
10    : table.Clone();

The Any() check matters. CopyToDataTable() throws if the sequence is empty, so table.Clone() is a safe way to preserve the schema without rows.

Dealing with Nulls and DBNull

Real DataTable values often contain database nulls. If you treat them as non-nullable types, you can trigger runtime exceptions. Use nullable fields when the column may be empty:

1using System;
2using System.Data;
3using System.Linq;
4
5table.Columns.Add("Bonus", typeof(decimal));
6table.Rows.Add("Sales", 70000m, DBNull.Value);
7
8var bonuses = table.AsEnumerable()
9    .Select(row => row.Field<decimal?>("Bonus") ?? 0m);
10
11Console.WriteLine(bonuses.Sum());

That keeps null-handling logic explicit instead of scattering DBNull.Value checks throughout your query code.

Common Pitfalls

• Forgetting AsEnumerable(), which makes the DataTable look like it does not support LINQ.
• Using row["Column"] with direct casts instead of Field<T>, which is harder to read and easier to break with nulls.
• Calling CopyToDataTable() on an empty sequence and getting an exception.
• Assuming database nulls behave like C# null without using nullable generic types.
• Running the same deferred query many times instead of materializing it with ToList() when repeated enumeration is intended.

Summary

• Start LINQ queries on DataTable with AsEnumerable().
• Prefer Field<T> for typed column access and better null handling.
• Use Where, OrderBy, Select, and GroupBy to replace manual row loops.
• Convert back to DataTable with CopyToDataTable() only after checking for empty results.
• Project into stronger shapes when later code should not depend on raw DataRow access.