Linq group by on multiple table and inner join

Question

I have SQL query and i would like to rewrite it by linq. This sql command only joins two table and do group by on them. Problem is in group by. When i use group by on one table, all is ok and linq commant returns the same result as sql command. But when i want to join two table and then group by H.Ucet (for example) then it returns other result as my sql command. The result is the same as when using a Left Join, but i want default inner join.

This is sql command:

string dotazBankUcty = @"SELECT 
 H.UCET, 
 SUM(H.MD) AS MD, 
 SUM(H.DAL) AS DAL , 
 SUM(H.MD_M) AS MD_M, 
 SUM(H.DAL_ENA) DAL_M, 
 MAX(UBUC.KOD) AS KOD 
 FROM ACCOUNT H 
 inner join UBU on H.UCET = UBU.UCET GROUP BY H.UCET";

I try to rewrite it by this linq command.

var accountQuery = new XPQuery<ACCOUNT >(CoreHelper.DataSession);

var ubuQuery = new XPQuery<UBU>(CoreHelper.DataSession);

var resultBankyUcty = (from h in accountQuery 
    join u in ubuQuery on h.CompoundKey1.UCET equals u.UCET
    group new { h, u } by new { h.CompoundKey1.UCET } into gUcty
                    select new
                    {
                    Ucet = gUcty.Key.UCET,
                    MD = gUcty.Sum(k => k.h.MD),
                    DAL = gUcty.Sum(k => k.h.DAL),
                    MD_M = gUcty.Sum(k => k.h.MDM),
                    DAL_M = gUcty.Sum(k => k.h.DALM),
                    KOD = gUcty.Max(k => k.u.KOD)
                    });

Can anyone help me ? I can't see an error.

Answer 1

from t in (
           from h in accountquery
           join u in ubuQuery on h.CompoundKey1.UCET equals u.UCET
           select new {h,u}
           ) group t by new {t.h.CompoundKey1.UCET} into g 

            select new {
                    Ucet = g.Key.UCET,
                    MD = g.Sum(k => k.h.MD),
                    DAL = g.Sum(k => k.h.DAL),
                    MD_M = g.Sum(k => k.h.MDM),
                    DAL_M = g.Sum(k => k.h.DALM),
                    KOD = g.Max(k => k.u.KOD)  
                };