LINQ: Help with groupby, doesn't seem to work like SQL :-) Only lets me return .Key and nothing else?

I have the following linq query which is grouped

  var results = from c in C
     join h in h on c.Code equals
        h.Code
     join m in M on c.Code equals
        m.Code
          group c by c.Code into g
            select
              new 
              {
                 Group = g.Key,
                 Total = g.Count()
              }

works great, return 2 records, 1 item has a Total of 4 records and the other has a total of 2. Excellent its grouped! but i am only returning the grouped field...

There开发者_运维百科 is also field called tDate and on the H table and pCode on the M table.

now i tried changing my select to be like say but linqpad complains about the m and h variables because i presume they are not included in the group by... But this is where i am lost.. how do i return more fields on the group by. Of course both pCode and tDate are the same for every record within the group by... hence i should still end up with 2 records but with more columns.

            select
              new 
              {
                 Group = g.Key,
                 Total = g.Count(),
                 PCode = m.pCode,
                 TDate = h.tDate
              }

Any body give me a helping hand....

Thanks in advance

SQL would give you an error about the columns not being included as part of an aggregate function. To include them, you have to include them in your group by, or join back onto the data to get them related to the grouped data.

I think you need to use a aggregate function to get the value. Have you tried h.Max(y => y.tDate) and m.First(y => y.pCode)? I think that might work but I'm not sure. Not all functions are aggregatefunctions.

When you say "into", you remove all variables from scope that were declared before the into, and are left with only the variable declared after the into.

var results =
  from c in C 
  group c by c.Code into g 
  select new
  { 
    Group = g.Key, 
    Total = g.Count() 
  } into x
  from c2 in x
  join h in h on c2.Group equals h.Code 
  join m in M on c2.Group equals m.Code
  select new
  { 
    Group = x.Group, 
    Total = x.Total,
    PCode = m.pCode, 
    TDate = h.tDate
  };

Of course both pCode and tDate are the same for every record within the group by.

If this is true, you can make them part of the key to get the results you want:

var results = 
      from c in C
      join h in H on c.Code equals h.Code
      join m in M on c.Code equals m.Code
      group c by new { cCode = c.Code, pCode = m.pCode, tDate = h.tDate } 
          into g
      select
          new
              {
                  Group = g.Key,
                  Total = g.Count()
              };

Now, for example, you can say

var firstcCode = results.First().Group.cCode;
var firstpCode = results.First().Group.pCode;
var firsttDate = results.First().Group.tDate;

If the statement I've quoted isn't true, then as suggested in other answers, just as in SQL, you need to invoke an aggregate function to say what to do with the (possibly many) pCode values for a given cCode.