How To Join Multiple Datatables Using LINQ To Dataset

[chibbie23]

Hi,

I have been searching for quite a while about joining multiple datatables via LINQ to dataset (When i say multiple, i don't mean just 2!!!), and it seems there aren't lot of examples in the net. I have seen examples of joining two datatables, done that, no problem there. Now i want to join three datatables. And there's nothing i can find out there.

For example i have a 3 datatables:

PERSON (columns: person_id, name, gender_code, ethnic_code)
GENDER (columns: gender_code, gender_description)
ETHNICITY (cols: ethnic_code, ethnic_description)

with sample records of:

PERSON:
p001, John B. Jones, m, 1
p002, Misa L. Campo, f, 3

GENDER:
m, Male
f, Female

ETHNICITY:
1, White American
2, African American
3, Asian

I'm hoping to display something like this:
p001, John B. Jones, Male, White American
p002, Misa L. Campo, Female, Asian

I have the ff codes:

 Dim joinedResult = _
            From p In PERSON.AsEnumerable() _
            Join g In GENDER.AsEnumerable() _
            On g.Field(Of string)("gender_code") Equals p.Field(Of string)("gender_code") _
            Join e In ETHNICITY.AsEnumerable() _
            On e.Field(Of STRING)("ethnic_code") Equals p.Field(Of string)("ethnic_code") _
            Select _
            id = p.Field(Of String)("person_id"), _
            name = p.Field(Of String)("name"), _
            gender = g.Field(Of String)("gender_description"), _
            ethnicity = e.Field(Of String)("ethnic_description")
 

I believe i'm doing this right up to this point, now the problem is i cant use the CopyToDataTable method on joinedResult. Any help would be greatly appreciated.

Thanks guys,

[chibbie23]

It seems that when i try to explicitly select the fields i want, it returns an iEnumerable(of anonymous) type and CopyToDataTable method expects an iEnumerable(of Datarow) type. So the workaround is to create a datatable with columns representing the fields you want to select and then loop through the rows in the results and then add the records one at a time.

 
Dim dt as new datatable
dt.columns.add("ID",gettype(string))
dt.columns.add("Name",gettype(string))
dt.columns.add("Gender",gettype(string))
dt.columns.add("Ethnicity",gettype(string))
 
For Each row in joinedResults
    Dim dr As DataRow = dt.NewRow()
    dr("ID") = row.ID
    dr("Name") = row.Name
    dr("Gender") = row.Gender
    dr("Ethnicity") = row.Ethnicity
    dt.Rows.Add(dr)
Next
 
 

I just couldn't verify if this is the only way around it. :D