0

I have three tables A, B and C. C has many B's which has many A's.

I want to display all this data in a tree so I bound db.A to my nested control which had a three layer hierarchy. The only problem is I get too many rows because it's not doing an inner join between B and C.

So how can I do something like this in linq:

SELECT A.name, B.name
FROM A
INNER JOIN B ON A.id = B.AID 
INNER JOIN C ON B.id = C.BID
GROUP BY A.Name, B.Name
ORDER BY A.Name

I've tried the following to no avail:

from a in A
join b in B on a.id equals b.AID
join c in C on b.id equals c.BID
select c

Many thanks

Jon
  • 85
  • 1
  • 8
  • Your LINQ query should be absolutely fine in terms of the join. (You don't have any grouping yet, of course.) It's not clear whether this is LINQ to SQL or something else... which will change the diagnostic steps. – Jon Skeet Jun 12 '12 at 09:55
  • Oh yes, of course! I was getting the results of the query (which should have read `select a` at the end) then doing `result.B` and getting too many rows again. I need to put in the grouping then all should be ok. Thanks for your help, much appreciated. – Jon Jun 12 '12 at 10:23

1 Answers1

0

Try this :

from a in A 
join b in B on a.id equals b.AID 
join c in C on b.id equals c.BID 
group new {a,b} by new {a.name,b.name} into g
select g

Above join is the same as you did in sql query but what you want is :

from a in A 
join b in B on a.id equals b.AID 
join c in C on b.id equals c.BID 
group c by c.name into g
select g
hoang
  • 1,887
  • 1
  • 24
  • 34
Manvinder
  • 4,495
  • 16
  • 53
  • 100
  • Almost, that seems to create a list of `A`'s which each have a list of `{B, C}`'s inside. Is there anyway to get the `C`'s inside the corresponding `B`'s? Thanks – Jon Jun 12 '12 at 13:12
  • I'm really after a tree structure as follows: List of A's, each A element contains a list of B's with each B element containing a list of C's. For example result[0].B[0].C[0].name, would access an element in C. Is that possible? Thanks for the reply anyway. – Jon Jun 12 '12 at 13:36
  • Don't worry MegaMind, I've figured out a solution. – Jon Jun 12 '12 at 14:48