There are two DataTable objects datatable1 and datatable2. How to get the output of a LINQ query similar to a SQL SELECT * FROM table1 INNER JOIN table2 ON condition in a new DataTable? How to do the INNER JOIN and even the OUTER JOIN clear.

 from table1 in datatable1.AsEnumerable() join table2 in datatable2.AsEnumerable() on (int)table1["anID"] equals (int)table2["anID"] select new { A = (int)table1["A"], B = (int)table1["B"], C = (int)table2["C"], D = (int)table2["D"] };

But how to get the result in the new DataTable, while not listing the example field does not come across.

PS The question is not about working with the database, but about the possibilities of c # + linq

  • @ 3per Do you really need to complete an INNER JOIN between tables from different datatable1 and datatable2 ? - Oleg
  • 2
    maximum select new { table1, table2} otherwise only manually specify fields - Grundy
  • @Bald INNER JOIN between tables from different datatable1 and datatable2 , from different SQL servers is a task that can be solved either through AsEnumerable, which is ineffective, or by creating, for example, Linked Servers . So what is there to find out by chance here is datatable1 and datatable2 - Oleg
  • @Oleg, something seems to me both of you in the wrong direction to go somewhere :-D - Grundy
  • one
    from a in db.Orders join b in db.Users on a.UserId equals b. Id select new {a, b} - from one db database - Oleg

3 answers 3

Since DataTable is needed, you first need to create it and add columns. Judging from the question you want to add all the columns from the first table and from the second. To do this, you can use the following code.

 DataTable table = new DataTable(); foreach(DataColumn column in table1.Columns){ table.Columns.Add("table1_"+column.ColumnName, column.DataType) } foreach(DataColumn column in table2.Columns){ table.Columns.Add("table2_"+column.ColumnName, column.DataType) }

Now in the new table the columns are from both tables and you can add rows.

 var rows = from table1 in datatable1.AsEnumerable() join table2 in datatable2.AsEnumerable() on (int)table1["anID"] equals (int)table2["anID"] select table1.itemArray.Concat(table2.itemArray);

Got a list of arrays that can be added to DataTable.

 foreach(var row in rows){ table.Rows.Add(row); }

As a result, we got a table with all the necessary rows.

    You can use the DataTable.Merge method to join tables.

    For example, there is a table t1:

     id c11 ----------- 1 11

    and table t2:

     id c21 ----------- 1 21

    In order to combine these tables we write

     var t3 = new DataTable("t3"); t3.Merge(t1); t3.Merge(t2);

    Result

     id c11 c21 ------------------- 1 11 - 1 - 21

    Code for example

     // #r "System.Data" using System.Data; DataTable table(string name, params DataColumn[] columns) { var t = new DataTable(); t.Columns.Add(new DataColumn("id", typeof(int)) { AutoIncrement=true, Unique=true }); foreach (var c in columns) t.Columns.Add(c); return t; } void print(DataTable t) { Console.WriteLine( String.Join( "\t\t", t.Columns.OfType<DataColumn>().Select(c => c.ColumnName))); foreach (var r in t.Rows.OfType<DataRow>()) Console.WriteLine( String.Join( "\t\t", r.ItemArray.Select(v => v != DBNull.Value ? v : "-"))); } var t1 = table("t1", new DataColumn("c11", typeof(int))); var t2 = table("t2", new DataColumn("c21", typeof(int))); t1.Rows.Add(1, 11); print(t1); t2.Rows.Add(1, 22); print(t2); var t3 = new DataTable("t3"); t3.Merge(t1, true); t3.Merge(t2, true); print(t3);

      A good answer to the question is here: https://stackoverflow.com/questions/15215233/return-all-columns-in-anonymous-linq-join

      Those. sampling is carried out like this:

       var result = (from table1 in datatable1.AsEnumerable() join table2 in datatable2.AsEnumerable() on (int)table1["anID"] equals (int)table2["anID"] into allColumns from rows in allColumns select new { first = table1, second = rows });

      And the data is processed like this:

       foreach (var item in result) { Console.WriteLine ( "{0} {1}", item.first["Имя ΠΊΠΎΠ»ΠΎΠ½ΠΊΠΈ ΠΈΠ· 1-ΠΉ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹"], item.second["Имя ΠΊΠΎΠ»ΠΎΠ½ΠΊΠΈ ΠΈΠ· 2-ΠΉ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹"] ); }