title: LINQ Group By multiple table Inner Join and aggregate function
body: <p>I have the following Linq to EF query:</p>
  
  <pre><code>from envelope in ENVELOPEs.AsNoTracking()
  join spool in SPOOLs.AsNoTracking() on envelope.SPOOL_ID equals spool.SPOOL_ID
  join extract in EXTRACTs.AsNoTracking() on spool.EXTRACT_ID equals extract.EXTRACT_ID
  join extractProfile in EXTRACT_PROFILE.AsNoTracking() on extract.EXTRACT_PROFILE_ID equals extractProfile.EXTRACT_PROFILE_ID
  join box in BOXes.AsNoTracking() on envelope.BOX_ID equals box.BOX_ID
  join pallet in PALLETs.AsNoTracking() on box.PALLET_ID equals pallet.PALLET_ID
  join fakeCap in FAKECAPs.AsNoTracking() on envelope.FAKECAP_ID equals fakeCap.FAKECAP_ID
  join envWeightRange in ENVELOPE_WEIGHT_RANGE.AsNoTracking() on envelope.ENV_WEIGHT_RANGE_ID equals envWeightRange.ENV_WEIGHT_RANGE_ID
  join ptype in PTYPEs.AsNoTracking() on spool.PTYPE_ID equals ptype.PTYPE_ID
  join envType in ENVTYPEs.AsNoTracking() on ptype.ENVTYPE_ID equals envType.ENVTYPE_ID
  join boxType in BOXTYPEs.AsNoTracking() on envType.BOXTYPE_ID equals boxType.BOXTYPE_ID
  where spool.JSHEET_ID == 8
  orderby envWeightRange.ENV_WEIGHT_RANGE_CODE,
      fakeCap.FAKECAP_DEST_TARIF
  group new { envelope, envType } by
  new 
  { 
      fakeCap.FAKECAP_DEST_TARIF,
      envWeightRange.ENV_WEIGHT_RANGE_CODE    
  } into grouped
  select new
  {
      FAKECAP_DEST_TARIF = grouped.Key.FAKECAP_DEST_TARIF,
      ENV_WEIGHT_RANGE_CODE = grouped.Key.ENV_WEIGHT_RANGE_CODE,
      ENVTYPE_COD_OMOLOG = grouped.Max(item =&gt; item.ENVTYPE_COD_OMOLOG), // Error: ENVTYPE_COD_OMOLOG not defined
      TOTALE_BUSTE = grouped.Count()
  }
  </code></pre>
  
  <p>It fails saying that filed ENVTYPE_COD_OMOLOG is not defined. (Sorry I would have wished to paste there the whole exception, but it's in not inenglish ue to OS locale settings).</p>
  
  <p>However if I Include the field ENVTYPE_COD_OMOLOG in the group by without accessing it with aggregate functions, it works perfectly:</p>
  
  <pre><code>from envelope in ENVELOPEs.AsNoTracking()
  join spool in SPOOLs.AsNoTracking() on envelope.SPOOL_ID equals spool.SPOOL_ID
  join extract in EXTRACTs.AsNoTracking() on spool.EXTRACT_ID equals extract.EXTRACT_ID
  join extractProfile in EXTRACT_PROFILE.AsNoTracking() on extract.EXTRACT_PROFILE_ID equals extractProfile.EXTRACT_PROFILE_ID
  join box in BOXes.AsNoTracking() on envelope.BOX_ID equals box.BOX_ID
  join pallet in PALLETs.AsNoTracking() on box.PALLET_ID equals pallet.PALLET_ID
  join fakeCap in FAKECAPs.AsNoTracking() on envelope.FAKECAP_ID equals fakeCap.FAKECAP_ID
  join envWeightRange in ENVELOPE_WEIGHT_RANGE.AsNoTracking() on envelope.ENV_WEIGHT_RANGE_ID equals envWeightRange.ENV_WEIGHT_RANGE_ID
  join ptype in PTYPEs.AsNoTracking() on spool.PTYPE_ID equals ptype.PTYPE_ID
  join envType in ENVTYPEs.AsNoTracking() on ptype.ENVTYPE_ID equals envType.ENVTYPE_ID
  join boxType in BOXTYPEs.AsNoTracking() on envType.BOXTYPE_ID equals boxType.BOXTYPE_ID
  where spool.JSHEET_ID == 8
  orderby envWeightRange.ENV_WEIGHT_RANGE_CODE,
      fakeCap.FAKECAP_DEST_TARIF
  group new { envelope, envType } by
  new 
  { 
      fakeCap.FAKECAP_DEST_TARIF,
      envWeightRange.ENV_WEIGHT_RANGE_CODE,
      envType.ENVTYPE_COD_OMOLOG
  } into grouped
  select new
  {
      FAKECAP_DEST_TARIF = grouped.Key.FAKECAP_DEST_TARIF,
      ENV_WEIGHT_RANGE_CODE = grouped.Key.ENV_WEIGHT_RANGE_CODE,
      ENVTYPE_COD_OMOLOG = grouped.Key.ENVTYPE_COD_OMOLOG,
      TOTALE_BUSTE = grouped.Count()
  }
  </code></pre>