Modern .NET development often relies on LINQ (Language Integrated Query) to retrieve, filter, and transform data efficiently. LINQ provides a clean and readable way to work with collections, databases, XML files, and many other data sources. However, there are situations where applications still require traditional ADO.NET objects such as DataSet and DataTable for reporting, legacy integrations, or data binding purposes.
Understanding how to fill a DataSet or DataTable from a LINQ query result set is an important skill when working with both modern and legacy .NET applications.
Why Convert LINQ Results to DataTable or DataSet?
LINQ queries typically return collections such as IEnumerable, IQueryable, or List. While these structures are excellent for application logic, some components require DataTable or DataSet objects.
Common scenarios include:
Reporting tools that accept DataTable as a data source.
Legacy applications built around ADO.NET.
Exporting data to Excel or CSV.
Binding data to older UI controls.
Data exchange between different application layers.
Converting LINQ results into DataTable structures bridges the gap between modern querying techniques and traditional data handling mechanisms.
Understanding LINQ Query Results
Consider a simple list of employees:
var employees = new List
{
new Employee { Id = 1, Name = "John", Department = "IT" },
new Employee { Id = 2, Name = "Sarah", Department = "HR" },
new Employee { Id = 3, Name = "David", Department = "IT" }
};
A LINQ query might look like:
var result = from emp in employees
where emp.Department == "IT"
select emp;
This query returns a collection of Employee objects. To use this data in older systems, we may need to populate a DataTable.
Creating a DataTable from LINQ Results
The first step is creating the structure of the DataTable.
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Department", typeof(string));
After defining the columns, populate rows from the LINQ result.
foreach (var employee in result)
{
dt.Rows.Add(
employee.Id,
employee.Name,
employee.Department
);
}
This approach gives complete control over the schema and data population process.
Using CopyToDataTable()
When LINQ operates directly on DataTable rows, .NET provides a built-in extension method called CopyToDataTable().
Example:
DataTable employeesTable = GetEmployeesTable();
var filteredRows =
from row in employeesTable.AsEnumerable()
where row.Field<string>("Department") == "IT"
select row;
DataTable resultTable = filteredRows.CopyToDataTable();
This method automatically creates a new DataTable and copies matching rows.
Benefits include:
Less code.
Faster implementation.
Preserves schema automatically.
CopyToDataTable() is often the easiest solution when querying existing DataTable objects.
Filling a DataSet with LINQ Results
A DataSet can contain multiple DataTables.
Create a DataSet:
DataSet ds = new DataSet();
Add the DataTable:
ds.Tables.Add(dt);
You can also add multiple tables:
DataTable employeeTable = GetEmployeeData();
DataTable departmentTable = GetDepartmentData();
DataSet companyDataSet = new DataSet();
companyDataSet.Tables.Add(employeeTable);
companyDataSet.Tables.Add(departmentTable);
DataSets are useful when managing related tables and complex reporting structures.
Generic Method for Converting LINQ Results to DataTable
A reusable generic solution can save development time.
public static DataTable ToDataTable(IEnumerable data)
{
DataTable table = new DataTable(typeof(T).Name);
PropertyInfo[] properties =
typeof(T).GetProperties();
foreach (PropertyInfo property in properties)
{
table.Columns.Add(
property.Name,
Nullable.GetUnderlyingType(property.PropertyType)
?? property.PropertyType);
}
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyInfo property in properties)
{
row[property.Name] =
property.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return table;
}
Usage:
var employeesDataTable = ToDataTable(result);
This generic approach works with virtually any LINQ result collection.
Performance Considerations
When dealing with large datasets, performance becomes important.
Best practices include:
Filter data at the database level whenever possible.
Avoid unnecessary DataTable conversions.
Use projections to select only required columns.
Minimize reflection usage in high-performance scenarios.
Cache metadata when repeatedly converting objects.
Example projection:
var result =
employees.Select(e => new
{
e.Id,
e.Name
});
This reduces memory consumption and processing overhead.
Common Challenges
Developers often encounter issues such as:
Empty Query Results
if(result.Any())
{
DataTable table = result.CopyToDataTable();
}
Calling CopyToDataTable() on an empty collection can throw an exception.
Null Values
Always handle null values properly:
row["Name"] =
employee.Name ?? DBNull.Value;
Data Type Mismatches
Ensure DataTable column types match source object properties to prevent runtime errors.
Careful validation helps maintain data integrity and application stability.
Real-World Applications
Many enterprise applications still rely heavily on DataTables and DataSets.
Typical use cases include:
Financial reporting systems.
Inventory management platforms.
Business intelligence dashboards.
Legacy Windows Forms applications.
Data export utilities.
Integration with third-party reporting engines.
LINQ provides modern querying capabilities, while DataTable and DataSet maintain compatibility with established enterprise workflows.
Conclusion
Filling a DataSet or DataTable from a LINQ query result set allows developers to combine the readability and flexibility of LINQ with the compatibility and structure of ADO.NET. Whether using manual row population, CopyToDataTable(), or generic conversion methods, understanding these techniques can significantly improve data processing workflows.
As organizations continue modernizing their applications, developers frequently encounter environments where both LINQ and traditional ADO.NET coexist. Mastering the conversion between these technologies ensures smoother integrations, cleaner code, and more maintainable enterprise applications.
Sumita
Web Developer