Deal of the Day

Home » Main » Manning Forums » 2007 » LINQ in Action

Thread: LINQ Syntax question for Joining 2 Tables and Outputing a New Table

Reply to this Thread Reply to this Thread Search Forum Search Forum Back to Thread List Back to Thread List

Permlink Replies: 1 - Pages: 1 - Last Post: Oct 15, 2009 6:48 PM by: jwooley
Elwad

Posts: 1
From: Contra Costa County, Ca
Registered: 10/15/09
LINQ Syntax question for Joining 2 Tables and Outputing a New Table
Posted: Oct 15, 2009 6:34 PM
  Click to reply to this thread Reply

Hi,

I have a very basic LINQ help request. I am attempting to join 2 tables using LINQ and then create a new table from the join.
The equivelent SQL (in Oracle) would be:

Create TableJoined as
(Select t1.Name,t1.ID,t2.Result
From table1 t1
join table2 t2
on t1.ID=t2.ID)

With a table output of
ID Name Result
1 Fauncy Yes
2 Elwad No



However, I am doing something wrong in my LINQ syntax.
The error message that I get is shown following the code.

Can anyone suggest the correct syntax for what I want to do?

Thanks,
Wiley Osborn

Dim oDatarow As DataRow

'Create & populate first table
Dim table1 As New DataTable()
table1.TableName = "Table1"
table1.Columns.Add("Name", GetType(String))
table1.Columns.Add("ID", GetType(Integer))
oDatarow = table1.NewRow()
oDatarow.Item("Name") = "Fauncy"
oDatarow.Item("ID") = 1
table1.Rows.Add(oDatarow)
 
oDatarow = table1.NewRow()
oDatarow.Item("Name") = "Elwad"
oDatarow.Item("ID") = 2
table1.Rows.Add(oDatarow)
 
'Create & populate second table
Dim table2 As New DataTable()
table2.TableName = "Table2"
table2.Columns.Add("ID", GetType(Integer))
table2.Columns.Add("Result", GetType(String))
oDatarow = table2.NewRow()
oDatarow.Item("Result") = "Yes"
oDatarow.Item("ID") = 1
table2.Rows.Add(oDatarow)
oDatarow = table2.NewRow()
oDatarow.Item("Result") = "No"
oDatarow.Item("ID") = 2
table2.Rows.Add(oDatarow)
 
 
 
'Use LINQ to join tables
 
Dim oResult = _
From t1 In table1.AsEnumerable() _
Join t2 In table2.AsEnumerable() _
On t1.Field(Of Integer)("ID") Equals _
t2.Field(Of Integer)("ID") _
Select New With _
{ _
.id = t1.Field(Of Integer)("ID"), _
.name = t1.Field(Of String)("Name"), _
.result = t2.Field(Of String)("Result") _
} 
'Use CopyToDataTable to create a new table from the join
'!!!THIS IS WHERE I GET THE DESIGN TIME ERROR SHOW BELOW!!!
Dim oTableJoined As DataTable = oResult.CopyToDataTable()

DESIGN ERROR is:
'CopytoDataTable' is not a member of 'System.Collections.Generic.IEnumerable(Of <anonymous type>)'

jwooley

Posts: 123
From: Atlanta, GA
Registered: 11/9/07
Re: LINQ Syntax question for Joining 2 Tables and Outputing a New Table
Posted: Oct 15, 2009 6:48 PM   in response to: Elwad in response to: Elwad
  Click to reply to this thread Reply

First, have you read the bonus chapter 14 available online? It covers LINQ to Datasets. It should answer most of these questions.

Second, are you sure you need to project into a DataTable? In many cases, projecting into an anonymous type or concrete type (class you create manually) is a fine option.

Third, using the VB optimizations, you can modify your syntax a bit. You don't need AsEnumerable with VB. Also, you can use the Bang (!) syntax as follows:

Dim oResult = _
From t1 In table1 _
Join t2 In table2 _
On t1!ID Equals _
t2!ID _
Select id = t1!ID, _
name = t1!Name, _
result = t2!Result 

Jim

Legend
Gold: 300 + pts
Silver: 100 - 299 pts
Bronze: 25 - 99 pts
Manning Author
Manning Staff
Manning Developmental Editor