How to use LINQ to join multiple DataTables

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

Answer 1

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

Answer 2

This url can help you.
http://www.hookedonlinq.com/LINQtoSQL5MinuteOverview.ashx
John is right.

---

PK

Answer 3

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

Answer 4

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

Answer 5

"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

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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] };

Answer 12

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"],

Answer 13

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);

Answer 14

This link doesn't work.

---

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


My blog

My TechNet articles