none
How to use LINQ to join multiple DataTables RRS feed

  • Question

  • Hello,

    have 2 DataTables with unknown (at design time) number of columns. No PK. The only relationship between these tables would be the row index.
    I'm trying to get a DataTable that has ALL columns from both tables in one row.

    Table1

    col11   col12

    Table2

    col21   col22

    Resulting Table

    col11 col12 col21 col22

    I thought I could use LINQ to do this. Here's my code

    var results = ( from a in ds.Tables[0].AsEnumerable()
    join b in ds.Tables[1].AsEnumerable()
    on ds.Tables[0].Rows.IndexOf(a) equals ds.Tables[1].Rows.IndexOf(b)
    select new { a, b });

    DataTable
    resultsTable = results.CopyToDataTable();

    Problem 1: select statement returns 2 rows instead of one.
    Problem 2. not sure how to convert the result into the DataTable, can't use CopyToDataTable() on it.

    Please help.

    Thank you.



    Isolda

    • Edited by Isolda Saturday, May 23, 2009 2:36 AM
    Saturday, May 23, 2009 2:34 AM

Answers

  • I think the easiest solution would be to use the Merge functionality of DataSet

    http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx

    What I would do is take table1 and use copy to copy its data into another table called table1'. Then merge table2 into table1' following the sample from msdn above. It seems like using Linq is alot of work. I don't really understand what you are trying to join on here either.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 26, 2009 4:05 PM

All replies

  • You don't need LINQ for that.

    Create a DataSet, push both tables in the DataSet. Create a DataRelation between them. Add a column for both DataTables capturing a column, call it "ID" or something like that and set it to its PrimaryKey.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Saturday, May 23, 2009 5:00 PM
  • This url can help you.
    http://www.hookedonlinq.com/LINQtoSQL5MinuteOverview.ashx
    John is right.
    PK
    Saturday, May 23, 2009 9:50 PM
  • Hmm, I'm sorry but I still don't see how creating PK and relationship btw 2 tables will help me get a resulting table which is a join of Table1 and Table2 on "ID".
    I can get a ChildRow, but it's still a separate DataRow... Could you clarify?

    Also, I'm curious why I can't do it with the above LINQ code... what do I put in "select" to get joined rows and not the separate rows from 2 tables?

    Thanx.
    Isolda
    Monday, May 25, 2009 5:01 PM
  • Well the purpose of a join between two tables is to be able to get its corresponding rows. I was just suggesting you can do this easily without LINQ.

    If you are insistent on using LINQ as your solution you can do this.

    //Example
    var ret = from p in Table1.AsEnumerable()
              join q in Table2.AsEnumerable() on p.Field<int>("ID") equals q.Field<int>("ID") into UP
              from q in UP.DefaultIfEmpty()
              select new
              {
                  ID = p.Field<int>("ID"),
                  Type = p.Field<string>("Type"),
                  Part = q.Field<int>("Part"),
                  Quantity = q.Field<int>("Quantity")
              };

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Monday, May 25, 2009 5:26 PM
    Monday, May 25, 2009 5:10 PM
  • "Also, I'm curious why I can't do it with the above LINQ code... "

    I didn't say you could not do it in LINQ, I said you can do it without LINQ.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by CORHIO Tuesday, March 24, 2015 8:32 PM
    Monday, May 25, 2009 5:25 PM
  • Tables are created dynamically, I don't know the number of columns or their names at design time. I can't do
    select new
              {
                  ID = p.Field<int>("ID"),
                  Type = p.Field<string>("Type"),
                  Part = q.Field<int>("Part"),
                  Quantity = q.Field<int>("Quantity")
              };


    and "select new {a, b}" gives me result with 2 separate rows (assuming each table has 1 row). I need result to have ONE row with all the columns from a and b.
    Same with your earlier DataRelationship suggestion - I'll get 2 separate DataRows. I need them combined into one possibly without looping thru all the rows.


    Isolda
    Tuesday, May 26, 2009 3:42 PM
  • Hopefully someone else can chime in and help you. Your index grouping sounds brittle to me and doesn't sound like anything I would use as it stands.

    EDIT:
    Let me edit that response. I meant create an additional row in the DataTable and put the index in there that way a natural join can be established.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Edited by JohnGrove Tuesday, May 26, 2009 4:07 PM
    • Marked as answer by Chris Robinson- MSFT Friday, August 21, 2009 4:51 PM
    • Unmarked as answer by Chris Robinson- MSFT Friday, August 21, 2009 4:51 PM
    • Proposed as answer by renlex Thursday, March 29, 2012 5:01 PM
    • Unproposed as answer by renlex Thursday, March 29, 2012 5:01 PM
    Tuesday, May 26, 2009 4:01 PM
  • I think the easiest solution would be to use the Merge functionality of DataSet

    http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx

    What I would do is take table1 and use copy to copy its data into another table called table1'. Then merge table2 into table1' following the sample from msdn above. It seems like using Linq is alot of work. I don't really understand what you are trying to join on here either.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 26, 2009 4:05 PM
  • I think the easiest solution would be to use the Merge functionality of DataSet

    http://msdn.microsoft.com/en-us/library/system.data.datatable.merge.aspx

    What I would do is take table1 and use copy to copy its data into another table called table1'. Then merge table2 into table1' following the sample from msdn above. It seems like using Linq is alot of work. I don't really understand what you are trying to join on here either.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.

    This probably makes better sense.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Tuesday, May 26, 2009 4:18 PM
  • Hi,

    I have been described this in my blog at http://yasser-zamani.spaces.live.com/blog/cns!5AAB8D00414B403D!318.entry (How To: Joining multiple DataTable using “LINQ to DataSet”). I hope you enjoy.

    Regards,

    Yasser


    LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
    Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
    Tuesday, June 1, 2010 5:35 AM
  • I'm very new at .net (not at programming, just .net) but I was able to select the link and try the Merge functionality.  It gave me a union of my two datatables when I wanted a join. 

    I'm using very simple examples to try to figure this out.  My DataTables, queryResuts1 and queryResults2 come from these two query strings:

    string sql1 = "select event_code, event_name from event where event_code in ('EXP','A01', 'WT', 'A03')";

    string sql2 = "select event_code, event_name from event where event_code in ('WT', 'BT')";

    I then merge and check my results like this:

    queryResults1.PrimaryKey =

     new DataColumn

    queryResults2.Merge(queryResults1);

     

    foreach (DataRow row in

    queryResults2.Rows)

    {

    Console.WriteLine(row.Field<string>("event_code") + " " + row.Field<string>("event_name" ));

    }

    That's where it looks like a merge.  However, I want a join, which I can do with linq:

     

    var

    results2 = from events in queryResults1.AsEnumerable()

     

    join events2 in queryResults2.AsEnumerable()

     

    on events.Field<string>("event_code") equals events2.Field<string>("event_code")

     

    into finalResults

     

    select finalResults;

     

    Where I run into trouble is accessing the results:  This:

    foreach

     

     

    (DataRow row in

    }

     

    gives me this:

    results2)

    {

     Console.WriteLine(row.Field<string>("event_name" ).ToString());

    System.InvalidCastException was unhandled
      Message=Unable to cast object of type 'Grouping[System.String,System.Data.DataRow]' to type 'System.Data.DataRow'.
      Source=DanBracukTestConsole
      StackTrace:
           at DanBracukTestConsole.Program.Main(String[] args) in D:\Projects\DanBracukTestConsole\DanBracukTestConsole\Program.cs:line 72
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

    Any hints?

    [] { queryResults1.Columns[0] };

    queryResults2.PrimaryKey =

     new DataColumn [] { queryResults2.Columns[0] };

    • Edited by Dan Bracuk Monday, December 19, 2011 7:20 PM
    Monday, December 19, 2011 4:41 PM
  • Sorry for all that whitespace.  I'm also new to this forum.  In any event, I found my answer.  Here is the join:

    var 

    results2 = from events1 in queryResults1.AsEnumerable()

     

    join events2 in queryResults2.AsEnumerable()

    on (string)events1["event_code"] equals (string)events2["event_code" ] select  new

     

    {

    f2 = (

    string)events2["event_name"]

    };

    and here is the presentation

    Console.WriteLine(results2.Count().ToString());

     foreach (var row in

    results2)

    {

     Console

    .WriteLine(row.f2.ToString());

    }

     

    f1 = (

    string)events1["event_code"],
    • Edited by Dan Bracuk Monday, December 19, 2011 7:24 PM
    Monday, December 19, 2011 7:22 PM
  • The Merge functionality in the dataset table would work like a join if the parameter 'preserveChanges' is set to 'false'.

    Eg:

    table1.Merge(table2,

    false);
    Thursday, March 29, 2012 5:00 PM
  • This link doesn't work.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, August 15, 2013 9:43 PM