CODEWITHSUNDEEP
c#.netlinq
Rohit
Rohit

Reputation: 10236

How to Loop through Datatable in Linq?

I am trying to perform inner join on Datatables I have

My code is as follows

 var Result = from row1 in t1
              join row2 in t2 on row1.ID equals row2.ID
              join row3 in t3 on row1.ID equals row3.ID
              select new { Emp.ID = row1.ID, Col1 = row2.col1, Col2 = row3.col2

In this snippet there are only 3 tables Whereas my number of tables are not fixed

Can I loop through the tables and perform join.

Will it be the best approach??

Please Help

Upvotes: 3

Views: 6329

Answers (2)

Alex Filipovici
Alex Filipovici

Reputation: 32561

The following approach uses a DataTable list to address a dynamic number of datatables. The code produces a List of IEnumerable<object>. This example assumes that the column value that will be added to the final item structure for each other datatable in the join lies in the same position (in our case I used row2[1], so I take the second position).

[EDIT] I also added an example of joining more than one column / joined table

    // create the collection
    List<DataTable> dts = new List<DataTable>();

    // add some test datatables
    for (int i = 0; i < 10; i++)
    {
        var dt = new DataTable();
        dt.TableName = i.ToString();
        dt.Columns.Add("ID");
        dt.Columns.Add("col" + i.ToString());
        dt.Columns.Add("otherCol" + i.ToString());
        dt.Rows.Add(1, "x1" + i.ToString(), DateTime.Now);
        dt.Rows.Add(2, "x2" + i.ToString(), DateTime.Now);
        dts.Add(dt);
    }

    // get the ID column position in the first table
    var idPosition = dts[0].Columns["ID"].Ordinal;

    // used for storing the results
    var results = new List<IEnumerable<object>>();

    // add the columns from the first table
    results = dts[0].AsEnumerable()
        .Select(j => j.ItemArray.AsEnumerable()).ToList();

    // join all tables
    dts.Skip(1).ToList().ForEach((list) =>
    {
        results = results
        .AsEnumerable()
        .Join(
            list.AsEnumerable(),
            x => x.Skip(idPosition).First(),
            x => x["ID"],
            // select the second column
            (row1, row2) => row1.Concat(new[] { row2[1] }))
            // replace the preceding line with 
            // the following one to select the second and the third column
            //(row1, row2) => row1.Concat(new[] { row2[1], row2[2] }))
        .ToList();
    });

Upvotes: 2

Maarten
Maarten

Reputation: 22945

One way, which could be labelled as the hard way, is to build up your combination of joins using the extension methods of Linq. This way you can loop over your collections (tables), and add whichever is necessary.

For instance, this:

from t1 in Table1
join t2 in Table2 on t1.ID equals t2.ID
join t3 in Table3 on t2.ID equals t3.ID
select new { t1, t2, t3 }

Can be rewritten as (easy to do using LinqPad):

var query = Table1
   .Join (
      Table2, 
      t1 => t1.ID, 
      t2 => t2.ID, 
      (t1, t2) => new  { t1 = t1, t2 = t2 }
   )
   .Join (
      Table3, 
      temp0 => temp0.t2.ID, 
      t3 => t3.ID, 
      (temp0, t3) => new { t1 = temp0.t1, t2 = temp0.t2, t3 = t3 }
   )
;

This can be split, and I suppose you can make this work while looping over a set of collections (your t1/t2/t3):

var query1 = Table1
   .Join (
      Table2, 
      t1 => t1.ID, 
      t2 => t2.ID, 
      (t1, t2) => new  { t1 = t1, t2 = t2 }
   );
var query2 = query1
   .Join (
      Table3, 
      temp0 => temp0.t2.ID, 
      t3 => t3.ID, 
      (temp0, t3) => new { t1 = temp0.t1, t2 = temp0.t2, t3 = t3 }
   );

Not a complete solution, but it's the idea that I'm trying to explain.

So instead of 'hardcoding' query1, query2, etc, you could do the same while looping over a set of collections.

Sounds difficult, and I'm sure its going to be.

Upvotes: 1

Related Questions