how to use LINQ to left join 2 datatables

[Javo2004]

Hello,

I have two UNTYPED datatables: "Shelfs" and "Books". Data came from different databases.

I must create a "left join" relationship because I want a list of ALL shelfs and books (if they have any).

Dim dset As New DataSet

dset.Tables.Add(dtShelfs)
dset.Tables.Add(dtBooks)

dset.Relations.Add("Shelfs", dset.Tables("dtShelfs").Columns("shelfID"), dset.Tables("dtBooks").Columns("shelfUsed"), False)

Basic question: I can I do this, using LINQ? I want the result on a new datatable...

I spent hours googling around, but all examples I found were only applicable to strongly typed dataSets, and how to build queries (not to get the data)

I could solve this the "easy" way: by looping both datatables and sending the rows to a third one, but I want to do this the "right" way and, of course... LINQ exists to make things easier and I don't know how to use it

Thanks!

[.paul.]

i don't understand the question...
dtBooks has the shelfUsed field. isn't that enough?

what do you expect the third table to look like?

[Javo2004]

Hello .paul.,

Sorry if I was not clear.
I don't want a list of all books. I want a list of ALL shelfs. Some of the shelfs don't have any book. Other shelfs have more than one book.

The "shelfUsed" field on dtBooks datatable has just the number (code) of the shelf. Most data I want is on dtShelfs datatable (like "Location" and "ShelfClassification").

This is a visual representation of what I want:

(being: dtShelfs A, dtBooks B)



Thanks

[.paul.]

you can join 2 tables with LINQ this way, but it'll only list the shelves that contain books:

Dim query = From A In ds.Tables("dtShelfs").AsEnumerable _
              Join B In ds.Tables("dtBooks").AsEnumerable On _
              A.Field(Of Integer)("shelfID") Equals B.Field(Of Integer)("shelfUsed") _
              Select New With { _
              .ShelfID = A.Field(Of Integer)("shelfID"), _
              .ShelfLocation = A.Field(Of String)("shelfLocation"), _
              .ShelfClassification = A.Field(Of String)("shelfClassification"), _
               .BookName = B.Field(Of String)("bookName")}

[.paul.]

here's another way. not much more code than the LINQ method: