The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

Elwad (1) [Avatar] Offline
#1
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>smilie'
jwooley (123) [Avatar] Offline
#2
Re: LINQ Syntax question for Joining 2 Tables and Outputing a New Table
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