Join two DataTables using LINQ in C#

LINQ is a powerful language feature that allows us to query different data sources using the programming language syntax. We can use C# to query data sources whether that is Oracle,SQL Server or XML.The query we use will be same irrespective of the data source.

While working with ADO.NET a common requirement is to query the data tables for matching data. Without LINQ we need to write complex logic to iterate over the entire table and retrieve the required data. Using LINQ we can easily query the table for the appropriate data.

We can use LINQ for querying data table ,  Query DataTable using LINQ in C#.

One common requirement when working with data is to join two tables. For example, one table may contain a list of the products while another table may contain details about the products. Though we can implement this logic very easily in SQL in the case of data tables this could mean writing lots of code.

We can use LINQ queries to implement this scenario.We can easily join two tables and retrieve the data from them using a relatively simple LINQ query.

We are creating two tables a product table and a details table.Product table contains the list of products while the details table contains the details about the products.

We are using the Products class to create and populate the tables.Though the following code populates the table with some random data ,mostly we will use the data returned from the database to populate the tables.
class Products
{
//table of products
private DataTable _productsTable;
public DataTable ProductsTable
{
get
{
_productsTable = new DataTable();
_productsTable.Columns.Add("Product", typeof(string));
_productsTable.Columns.Add("Id", typeof(int));
PopulateProductsTable(_productsTable);
return _productsTable;
}
}
//table of categories
private DataTable _detailsTable;
public DataTable DetailsTable
{
get
{
_detailsTable = new DataTable();
_detailsTable.Columns.Add("Price", typeof(decimal));
_detailsTable.Columns.Add("Availability", typeof(bool));
_detailsTable.Columns.Add("Id", typeof(int));
PopulateDetailsTable(_detailsTable);
return _detailsTable;
}
}
public static DataTable PopulateProductsTable(DataTable datatable)
{
datatable.Rows.Add("laptop", 1);
datatable.Rows.Add("desktop", 2);
datatable.Rows.Add("mobile phone", 3);
datatable.Rows.Add("mp3 player", 4);
return datatable;
}
public static DataTable PopulateDetailsTable(DataTable datatable)
{
datatable.Rows.Add(500, true, 1);
datatable.Rows.Add(300, true, 2);
datatable.Rows.Add(100, false, 3);
datatable.Rows.Add(50, true, 4);
return datatable;
}
}

We can join the above table using the following LINQ query


Products obj = new Products();
DataTable products = obj.ProductsTable;
DataTable details = obj.DetailsTable;
var items= (from p in products.AsEnumerable()
join t in details.AsEnumerable()
on p.Field<int>("Id") equals t.Field<int>("Id")
where p.Field<int>("Id")==1
select new
{
ProductName = p.Field<string>("Product"),
}).ToList();
/ var dataRow=datatable.AsEnumerable().Where(x => x.Field<int>("Id") == 2).FirstOrDefault();
foreach(var item in items)
{
var name=item.ProductName;
}
The above query will return the product with id 1 as the tables are joined with the id column value “1”.As the product with id “1” is laptop so the items collection contains a single product.

Similar to SQL join the two tables should have a common column such as the id column in the above query.If you have used SQL before you will realize that the join query resembles the sql join statement.

Follow on: