How to Query a DataTable with LINQ in C#

By the end of this page, you will understand why a DataTable cannot be queried directly with standard LINQ syntax, how AsEnumerable() makes LINQ work with table rows, and how to safely read column values using Field<T>(). You will also see practical examples, common mistakes, and a small project that filters data from a DataTable.

A DataTable stores rows of data in memory, but it does not directly implement the generic sequence interface that standard LINQ queries expect.

LINQ works best with collections such as:

• IEnumerable<T>
• List<T>
• arrays
• other generic sequences

A DataTable contains rows as DataRow objects, but to use LINQ query syntax like from ... where ... select ..., you usually need to convert the table into something LINQ can enumerate as a sequence of rows.

That is why C# provides the AsEnumerable() extension method for DataTable.

var results = myDataTable.AsEnumerable();

This gives you an IEnumerable<DataRow>, which LINQ can query.

Another important part is reading values from a DataRow. A DataRow stores column values as objects, so LINQ queries often use:

row.Field<int>("RowNo")

The Field<T>() method:

• reads a typed value from a column
• avoids manual casting in many cases
• works better with nullable values than direct indexing alone

This matters in real programming because many legacy systems, reporting tools, WinForms apps, and database-heavy .NET applications still use DataTable. Knowing how to bridge DataTable and LINQ makes filtering, sorting, and projecting data much cleaner.

Think of a DataTable as a filing cabinet and each DataRow as one paper file.

LINQ expects a line of items it can walk through one by one, like files laid out on a desk. But a DataTable is not automatically presented that way for generic LINQ.

AsEnumerable() is the step that takes the files out of the cabinet and lays them on the desk so LINQ can inspect them one at a time.

Then Field<T>() is like reading a labeled field from each paper file in the correct data type:

• "RowNo" as an int
• "Name" as a string
• "Price" as a decimal

So the process is:

1. Convert the DataTable into an enumerable row sequence.
2. Read values from each row by column name.
3. Filter or transform the rows with LINQ.

The basic pattern is:

using System.Data;
using System.Linq;

var results = from row in myDataTable.AsEnumerable()
              where row.Field<int>("RowNo") == 1
              select row;

You can also write the same query with method syntax:

var results = myDataTable.AsEnumerable()
                         .Where(row => row.Field<int>("RowNo") == 1);

Example: filter rows by column value

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

DataTable table = new DataTable();
table.Columns.Add("RowNo", typeof(int));
table.Columns.Add("Name", typeof(string));

table.Rows.Add(1, "Alice");
table.Rows.Add(2, "Bob");
table.Rows.Add(1, "Charlie");

var results = from row in table.AsEnumerable()
              where row.Field<int>("RowNo") == 1
              select row;

foreach (var row in results)
{
    Console.WriteLine($"{row.Field<int>("RowNo")} - {row.Field<string>("Name")}");
}

Output:

1 - Alice
1 - Charlie

Example: select specific columns instead of full rows

var names = from row in table.AsEnumerable()
            where row.Field<int>("RowNo") == 1
            select row.Field<string>("Name");

This returns a sequence of names instead of DataRow objects.

Example: convert results back to a DataTable

If you need the filtered result as another DataTable:

var filteredRows = table.AsEnumerable()
                        .Where(row => row.Field<int>("RowNo") == 1);

DataTable filteredTable = filteredRows.CopyToDataTable();

This is useful when older APIs still expect a DataTable instead of an IEnumerable<DataRow>.

Consider this code:

DataTable table = new DataTable();
table.Columns.Add("RowNo", typeof(int));
table.Columns.Add("Name", typeof(string));

table.Rows.Add(1, "Alice");
table.Rows.Add(2, "Bob");

table.Rows.Add(1, "Charlie");

var results = table.AsEnumerable()
                   .Where(row => row.Field<int>("RowNo") == 1)
                   .Select(row => row.Field<string>("Name"));

Here is what happens step by step:

1. A DataTable is created.
2. Two columns are added:
   • RowNo as int
   • Name as string
3. Three rows are inserted.
4. AsEnumerable() exposes the rows as IEnumerable<DataRow>.
5. Where(...) checks each row:
   • Row 1: RowNo is 1 → keep it
   • Row 2: RowNo is 2 → skip it
   • Row 3: RowNo is 1 → keep it
6. Select(...) transforms the remaining rows into just the Name values.
7. The final result is a sequence containing:

Alice
Charlie

A key detail is that LINQ often uses deferred execution. The query is not fully executed when you define results. It runs when you iterate over it, for example with foreach or by calling .ToList().

LINQ on DataTable is useful in many existing .NET applications.

Filtering imported data

If you load CSV or Excel data into a DataTable, you can use LINQ to find rows matching conditions:

• invalid records
• duplicate IDs
• rows missing required values

Working with database results

Some older systems still use SqlDataAdapter to fill a DataTable. LINQ makes it easier to:

• filter active users
• sort invoice rows
• extract only needed columns

Reporting and dashboards

A report may gather raw data into a DataTable, then use LINQ to:

• group values
• compute totals
• display only a subset of rows

Validation pipelines

Before saving data, LINQ can help detect:

• missing fields
• out-of-range values
• duplicate business keys

In real projects, developers often use LINQ on DataTable in small, focused ways rather than building huge query expressions.

Common patterns

Guard clauses before querying

if (table == null || table.Rows.Count == 0)
{
    return;
}

This prevents null-reference problems and pointless queries.

Validation before reading typed values

var validRows = table.AsEnumerable()
                     .Where(row => !row.IsNull("RowNo"));

This is helpful when database data may contain DBNull.

Early filtering

var activeRows = table.AsEnumerable()
                      .Where(row => row.Field<bool>("IsActive"));

Filter early so later logic works on a smaller set.

Projection into models

Instead of passing DataRow around, many codebases map rows into objects:

var users = table.AsEnumerable()
                 .Select(row => new
                 {
                     Id = row.Field<int>("Id"),
                     Name = row.Field<string>("Name")
                 });

This makes business logic cleaner and easier to test.

Convert back only when necessary

If some older API requires a DataTable, developers may filter with LINQ and then call CopyToDataTable(). But if possible, they keep the result as a sequence or map it into domain objects.

1. Querying the DataTable directly

Broken code:

var results = from row in myDataTable
              where row.Field<int>("RowNo") == 1
              select row;

Why it fails:

• DataTable is not directly used as IEnumerable<DataRow> for this query syntax.

Fix:

var results = from row in myDataTable.AsEnumerable()
              where row.Field<int>("RowNo") == 1
              select row;

2. Using the wrong variable name inside the query

Broken code:

var results = from myRow in myDataTable.AsEnumerable()
              where results.Field<int>("RowNo") == 1
              select results;

Why it fails:

• results is the query result variable, not the current row.
• Inside the query, use myRow.

Fix:

var results = from myRow in myDataTable.AsEnumerable()
              where myRow.Field<int>("RowNo") == 1
              select myRow;

3. Forgetting the type in Field<T>()

Broken code:

where row.Field("RowNo") == 1

Why it fails:

• Field is generic and needs the type.

Fix:

where row.Field<int>("RowNo") == 1

4. Using the wrong type

Broken code:

where row.Field<string>("RowNo") == "1"

If the column is actually an integer, use int.

where row.Field<int>("RowNo") == 1

5. Ignoring DBNull

Broken code:

var ids = table.AsEnumerable()
               .Where(row => row.Field<int>("RowNo") > 0);

If RowNo can be null in the table, this may fail.

Safer version:

var ids = table.AsEnumerable()
               .Where(row => !row.IsNull("RowNo"))
               .Where(row => row.Field<int>("RowNo") > 0);

Or with nullable types:

var ids = table.AsEnumerable()
               .Where(row => row.Field<int?>("RowNo") == 1);

6. Calling CopyToDataTable() on an empty result

Broken code:

DataTable filtered = table.AsEnumerable()
                          .Where(row => row.Field<int>("RowNo") == 99)
                          .CopyToDataTable();

Why it fails:

• CopyToDataTable() throws if there are no rows.

Safer approach:

var filteredRows = table.AsEnumerable()
                        .Where(row => row.Field<int>("RowNo") == 99);

DataTable filtered = filteredRows.Any()
    ? filteredRows.CopyToDataTable()
    : table.Clone();

DataTable.Select() vs LINQ

DataTable.Select()

DataRow[] rows = table.Select("RowNo = 1");

Pros:

• short for simple expressions
• built into DataTable

Cons:

• string-based conditions
• less compile-time checking
• harder to refactor

LINQ

var rows = table.AsEnumerable()
                .Where(row => row.Field<int>("RowNo") == 1);

Pros:

• type-safe access with Field<T>()
• easier to combine with Select, OrderBy, GroupBy
• more readable in larger codebases

Cons:

• needs AsEnumerable()
• may feel more verbose at first

DataRow vs custom object

In many real applications, developers query the DataTable first, then project the rows into typed objects.

// Required namespaces
using System.Data;
using System.Linq;

// Basic LINQ query on a DataTable
var results = from row in table.AsEnumerable()
              where row.Field<int>("RowNo") == 1
              select row;

// Method syntax
var results2 = table.AsEnumerable()
                    .Where(row => row.Field<int>("RowNo") == 1);

// Select one column
var names = table.AsEnumerable()
                 .Select(row => row.Field<string>("Name"));

// Handle nullable values
var rows = table.AsEnumerable()
                .Where(row => row.Field<int?>("RowNo") == 1);

// Check for DBNull
var safeRows = table.AsEnumerable()
                    .Where(row => !row.IsNull("RowNo"));

// Convert filtered rows back to DataTable
var filteredRows = table.AsEnumerable()
                        .Where(row => row.Field<int>("RowNo") == 1);

DataTable filteredTable = filteredRows.Any()
    ? filteredRows.CopyToDataTable()
    : table.Clone();

Rules to remember

• Use AsEnumerable() before querying a DataTable with LINQ.
• Use Field<T>("ColumnName") to read typed column values.
• Use the correct generic type in Field<T>().
• Watch for DBNull values.
• CopyToDataTable() fails on empty sequences.
• Prefer projecting into typed objects when possible.

Why can't I use LINQ directly on a DataTable?

DataTable is not directly queried the same way as a generic IEnumerable<T> source in normal LINQ syntax. Use AsEnumerable() to expose its rows as IEnumerable<DataRow>.

What namespace do I need for AsEnumerable()?

You typically need System.Data and System.Linq. In many projects, the extension method support for DataTable also comes from the DataSet extensions assembly available in .NET.

What does Field() do in a DataRow?

It reads a column value from a DataRow and returns it as the specified type, such as int, string, or DateTime.

Can I return a DataTable after filtering with LINQ?

Yes. After filtering rows, call CopyToDataTable(). If the result may be empty, check with Any() first or return table.Clone().

Is DataTable.Select() better than LINQ?

Not usually. DataTable.Select() is fine for simple string-based filters, but LINQ is generally more readable, composable, and type-safe.

How do I handle null values in DataTable LINQ queries?

Use row.IsNull("ColumnName") or read with a nullable type like Field<int?>().

Should I keep using DataTable in new code?

If possible, strongly typed models are usually easier to maintain. But when you already have a DataTable, LINQ is a clean way to work with it.

• IEnumerable<T> — LINQ queries are built around enumerable sequences.
• DataRow — each row in a DataTable is queried as a DataRow.
• Field<T>() — used to read typed values from DataRow columns.
• AsEnumerable() — the key extension method that enables LINQ over DataTable rows.
• Where — filters rows based on a condition.
• Select — projects rows into values or objects.
• CopyToDataTable() — converts a LINQ result of rows back into a DataTable.
• DBNull — important when table columns may contain database null values.
• Deferred execution — explains when LINQ queries actually run.
• Anonymous types — useful when projecting selected columns into lightweight objects.