title: Linq Group by and inner join
body: <p>I would like to replace this SQL command :</p>
  
  <pre><code>string dotazBankUcty = @"SELECT 
       H.UCET, 
       SUM(H.KON_STAV_MD) AS KON_STAV_MD, 
       SUM(H.KON_STAV_DAL) AS KON_STAV_DAL , 
       SUM(H.KON_STAV_MD_MENA) AS KON_STAV_MD_MENA, 
       SUM(H.KON_STAV_DAL_MENA) AS KON_STAV_DAL_MENA, 
       MAX(UBUC.KOD_MENY) AS KOD_MENY 
       FROM UHLAVAKT H 
       inner join UBUC on H.UCET = UBUC.UCET GROUP BY H.UCET";
  </code></pre>
  
  <p>with a LINQ command. I tried this, but it returns a left join instead of an inner join, which I want.</p>
  
  <pre><code>var uhlavAktQuery = new XPQuery&lt;UHLAVAKT&gt;(CoreHelper.DataSession);
  
  var ubucQuery = new XPQuery&lt;UBUC&gt;(CoreHelper.DataSession);
  
  var resultBankyUcty = (from h in uhlavAktQuery
      join u in ubucQuery on h.CompoundKey1.UCET equals u.UCET
      group new { h, u } by new { h.CompoundKey1.UCET } into gUcty
                      select new
                      {
                      Ucet = gUcty.Key.UCET,
                      KON_STAV_MD = gUcty.Sum(k =&gt; k.h.KONSTAVMD),
                      KON_STAV_DAL = gUcty.Sum(k =&gt; k.h.KONSTAVDAL),
                      KON_STAV_MD_MENA = gUcty.Sum(k =&gt; k.h.KONSTAVMDMENA),
                      KON_STAV_DAL_MENA = gUcty.Sum(k =&gt; k.h.KONSTAVDALMENA),
                      KOD_MENY = gUcty.Max(k =&gt; k.u.KODMENY)
                      });
  </code></pre>
  
  <p>Can you help me, please?</p>