Linq Group on a multi-level object with select statement

Question

I've got 3 dataset objects that are nested with each other using entity set objects. I am selecting the data like this

var newList = from s in MainTable
from a in s.SubTable1 where a.ColumnX = "value"
from b in a.Detail where b.Name = "searchValue"
select new {
    ID = s.ID,
    Company = a.CompanyName,
    Name = b.Name,
    Date = s.DueDate
    Colour = b.Colour,
    Town = a.Town
};

and this works fine, but the trouble is there are many records in the Detail object-list/table for each Name value so I get a load of duplicate rows and thus I only want to display one record per b.Name. I have tried putting

group s by b.Name into g

before the select, but then this seems to stop the select enabling me to select the columns I want (there are more, in practice). How do I use the group command in this circumstance while still keeping the output rows in a "flat" format?

Of course that if you group your results, you cant get select a column of a child, thats because there may be more than one childs and you have to specify an aggregate column for example the sum,max etx — alejandrobog, Apr 29 '10 at 22:42
I'm only selecting 1 column from the bottom child, all the other columns are from the top or middle parent, so there's no reason why there would be any ambiguity, but I see what you mean from the SQL point of view. — Mad Halfling, Apr 30 '10 at 07:37
Yes, grouping on every column sorted it out - could you put that as an answer please, Alejandrobog, so I can credit you with the correct answer and the relevant points — Mad Halfling, Apr 30 '10 at 11:31

score 0 · Accepted Answer · answered May 04 '10 at 16:41

Appending comment as answer to close question:- Of course that if you group your results, you cant get select a column of a child, thats because there may be more than one childs and you have to specify an aggregate column for example the sum,max etx –

Linq Group on a multi-level object with select statement

1 Answers1