How do select from two tables using Linq to SQL?
Two tables are related.
I have this code:
Dim dc As New DBDataContext
Dim q = From t In dc.taxes Join p In dc.persons On t.id Equals p.id _
Select t.rank, t.name, p.prop
For Each t In q
rowRank = t.rank
rowName = t.name
rowProp = p.prop
Next
The p.prop does not work; p is not recognized as an object.
If I write
For Each t In q
it works but the first two lines for t do not work.
How do I select data from mulitple tables?
I have this code:
Dim dc As New DBDataContext
Dim q = From t In dc.taxes Join p In dc.persons On t.id Equals p.id _
Select t.rank, t.name, p.prop
For Each t In q
rowRank = t.rank
rowName = t.name
rowProp = p.prop
Next
The p.prop does not work; p is not recognized as an object.
If I write
For Each t In q
it works but the first two lines for t do not work.
How do I select data from mulitple tables?
ASKER
Thanks,
Your suggestion works. However,
I had to change
Dim q = From t In taxes.AsEnumerable() Join p In persons On t("id") Equals p("id") _
to
Dim q = From t In taxes.AsEnumerable() Join p In persons On t.id Equals p.pid _
The results are that rows in taxes are selected ONLY if there is a row in persons with matching pid that equals the tax id column.
Perhaps I don't know how JOIN works.
I don't want persons data to affect selection of tax rows.
I only want to get persons data when it's available - if the pid matches.
Your suggestion works. However,
I had to change
Dim q = From t In taxes.AsEnumerable() Join p In persons On t("id") Equals p("id") _
to
Dim q = From t In taxes.AsEnumerable() Join p In persons On t.id Equals p.pid _
The results are that rows in taxes are selected ONLY if there is a row in persons with matching pid that equals the tax id column.
Perhaps I don't know how JOIN works.
I don't want persons data to affect selection of tax rows.
I only want to get persons data when it's available - if the pid matches.
I had to use the statement with t("id") because I just created 2 datatabels to test it.
>> The results are that rows in taxes are selected ONLY if there is a row in persons with matching pid that equals the tax id column.
Now you are using an inner join, which means that only records are returned that have in both tables the same "id".
What you want to achieve is a left (or right) join on a table, which means: take all the rows from this table and if found, take the row from the other table also
>> The results are that rows in taxes are selected ONLY if there is a row in persons with matching pid that equals the tax id column.
Now you are using an inner join, which means that only records are returned that have in both tables the same "id".
What you want to achieve is a left (or right) join on a table, which means: take all the rows from this table and if found, take the row from the other table also
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works like I wanted.
Do you know of any books or websites that explain these things?
The syntax is so "not expected".
But my project can proceed again, thanks to you!
Do you know of any books or websites that explain these things?
The syntax is so "not expected".
But my project can proceed again, thanks to you!
I don't know any books, but a good starting point is always:
How to: 101 LINQ Samples for Visual Basic.NET
http://rshelton.com/archive/2008/02/03/how-to-101-visual-basic-linq-samples.aspx
How to: 101 LINQ Samples for Visual Basic.NET
http://rshelton.com/archive/2008/02/03/how-to-101-visual-basic-linq-samples.aspx
ASKER
Thanks
Dim q = From t In taxes.AsEnumerable() Join p In persons On t("id") Equals p("id") _
Select New With {
.rank = t.rank, _
.name = t.name, _
.prop = p.prop _
}
For Each t In q
rowRank = t.rank
rowName = t.name
rowProp = t.prop
Next