|
Replies:
1
-
Pages:
1
-
Last Post:
Oct 15, 2009 6:48 PM
by: jwooley
|
|
|
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
|
|
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>)'
|
|
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
|
|
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
|
|
