CODEWITHSUNDEEP
c#linqjoindatatable
Markku Rintala
Markku Rintala

Reputation: 149

How to throuhly join two datatables using linq without column names

This is propably answered somewhere else, but I haven't found working solution yet.

I have two datatables and I want to join them into one datatable containing all data from both of them, or at least from the first of them and some columns from the second datatable.

I don't want to list all columns (totally 180) from the first datatable. I have tried eg. this

var JoinedResult = from t1 in table1.Rows.Cast<DataRow>()
                   join t2 in table2.Rows.Cast<DataRow>() 
                      on Convert.ToInt32(t1.Field<string>("ProductID")) equals t2.Field<int>("FuelId")
                    select t1;

but that gives only the columns from table1. How to get colums from table2 too to my result? Finally, I need to add my result to a dataset.

ResultSet.Tables.Add(JoinedResult.CopyToDataTable());

EDIT:

I ended up with this as the solution. This follows an example given here Create join with Select All (select *) in linq to datasets

 DataTable dtProduct = dsProduct.Tables[0];
 DataTable dtMoistureLimits = ds.Tables[0];

 //clone dt1, copies all the columns to newTable 
 DataTable dtProductWithMoistureLimits = dtProduct.Clone();

 //copies all the columns from dt2 to newTable 
foreach (DataColumn c in dtMoistureLimits.Columns)
   dtProductWithMoistureLimits.Columns.Add(c.ColumnName, c.DataType);

   var ProductsJoinedWithMoistureLimits = dtProduct.Rows.Cast<DataRow>()
       .Join(dtMoistureLimits.Rows.Cast<DataRow>(),// join table1 and table2
       t1 => new { ProductID = t1.Field<int>("ProductID"), DelivererID = t1.Field<int>("DelivererID") },
       t2 => new { ProductID = t2.Field<int>("MoistureLimits_ProductID"), DelivererID = t2.Field<int>("MoistureLimits_DelivererID") },
       (t1, t2) =>     // when they match 
       {    // make a new object
            // containing the matching t1 and t2
           DataRow row = dtProductWithMoistureLimits.NewRow();
           row.ItemArray = t1.ItemArray.Concat(t2.ItemArray).ToArray();
           dtProductWithMoistureLimits.Rows.Add(row);
           return row;
       });

However, in dtMoistureLimits there is not rows for all "ProductID" and "DelivererID" in dtProduct. Currently my solution returns only matching rows.

How to improve solution to return also those rows where there is not data for "ProductID" and "DelivererID" in dtMoistureLimits?

Upvotes: 2

Views: 1803

Answers (2)

Ajay Gupta
Ajay Gupta

Reputation: 1845

    var JoinedResult = (from t1 in table1.Rows.Cast<DataRow>()
               join t2 in table2.Rows.Cast<DataRow>() 
               on Convert.ToInt32(t1.Field<string>("ProductID")) equals t2.Field<int>("FuelId")
               select new { T1 = t1,
                            T2 = t2.column_name // all columns needed can be listed here
                          }).ToList();

EDIT: To convert the above result to a DataTable, use the following method:

    DataTable dataTable = new DataTable();

    //Get all the properties
    PropertyInfo[] Props = JoinedResult.Select(y=>y.T1).First().GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Defining type of data column gives proper data table 
        var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
        //Setting column names as Property names
        dataTable.Columns.Add(prop.Name, type);
    }

    dataTable.Columns.Add(t2_column_name, t2_column_type);

    foreach (var item in JoinedResult)
    {
       var values = new object[Props.Length];
       for (int i = 0; i < Props.Length; i++)
       {
            //inserting property values to datatable rows
            values[i] = Props[i].GetValue(item.T1, null);
       }
       values[Props.Length] = item.T2;
       dataTable.Rows.Add(values);
   }

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

Solution using method syntax, without having to mention all columns

var result = table1.Rows.Cast<DataRow>()
   .Join(table2.Rows.Cast<DataRow>(),                      // join table1 and table2
      t1 => Convert.ToInt32(t1.Field<string>("ProductID")) // from every t1 get the productId
      t2 => t2.Field<int>("FuelId")                        // from every t2 get the fuelId,
      (t1, t2) => new                                  // when they match 
      {                                                // make a new object
           T1 = t1,                                    // containing the matching t1 and t2
           T2 = t2,
      }

Upvotes: 1

Related Questions