LINQ : understanding and executing Groupby to bring back unique rows?

Question

Hi there,

i am trying to do a groupby in linq, basically i have a list ( along list - around 1000 entries) and i wish to groupby Description.

The entries are vehicles, so hence there are 50 or so Ford Mondeos

My query is pretty simple, no joins (yet :-) ) but it brings back a list including 50 Ford Mondeos, i wanted it to group them so there is only 1 entry.

I am only selecting Description, i am not selecting the IDs which would be different, but in LinqPad it returns the desc and i can see 50 ford mondeos that are all the same in description - letter for letter.

What am i doing wrong?

from v in dc.Vehicles
   group v by v.Description into g1
from y in g1
   orderby y.Description 
select new
{
     Desc = y.Description 
};

EDIT

It now brings back just 1 record for each ford mondeo, this was my test to ensure it worked but i need to expand on this, again it should only bring back 1 record each for ford mondeo as i have checked they all have same number of doors, categor, model id etc..

from v in dc.Vehicles
  group v by v.Description into g1
orderby g1.Key
  select new
  {
     Desc = g1.Key,
     CategoryId = g1.CategoryId,
     MakeId = g1.MakeId,
     ModelId = g1.ModelId,
     Doors = g1.Doors,
  };

Of the course the above doesn't work it doesn't find all the other fields i.e. CategoryId... i tried separating the group by and adding a comma for the other fields..

I think i have a little confusion over the key, i understand that this is the key but if you are grouping on more than 1 fields then potentially you would have more than 1 key..

Any ideas?

Answer 1

You're using a second "from" which is causing the problem. Try this:

from v in dc.Vehicles
group v by v.Description into g1
orderby g1.Key
select new
{
     Desc = g1.Key
};

Conceptually, a group like this consists of a "sequence of sequences" - one subsequence per group, with all the items matching that group. Each subsequence has a key - the description in this case.

Your "from" clause was basically saying, "for each group, get me all the elements in the group" - effectively ungrouping them again :) Typically you'd perform some sort of aggregation on the group, e.g. for the final part:

select new
{
     Desc = g1.Key,
     Oldest = g1.Min(x => x.Date),
     Newest = g1.Max(x => x.Date)
};

EDIT: To group on more than one field, use an anonymous type:

group v by new { v.Description, v.Doors, v.Model } into g1

That anonymous type will then form the key.

To get any field within a record, you could try:

select new { RandomField = g1.First().RandomField, Description = g1.Key, // etc }

That logically takes the first record in the subsequence. I don't know whether it'll work for LINQ to SQL though - I know it would in LINQ to Objects, but I've no idea what the SQL equivalent would be. Even if it does work, it may well be incredibly expensive. I suspect that grouping by a composite key (via an anonymous type) is the way to go here.

Answer 2

Group bys are not used to get unique rows, but to group columns when there are aggregate expressions like sum, avg, count, and so forth.

Answer 3

If you just want the keys (the fields you're grouped on) you need to do something like this...

from v in dc.Vehicles
   group v by v.Description into g1
select g1.Key;