Linq group for multiple tables and inner join

Question

I have a SQL query and I would like to rewrite it linq. This sql command joins only two tables and is grouped by them. The problem is in the group. When I use a group on one table, everything is fine, and linq commant returns the same result as the sql command. But when I want to join two tables, and then the group H.Ucet (for example), then it returns another result as my sql command. The result is the same as using Left Join, but I want a default built-in connection.

This is the 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 am trying to rewrite it with 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 do not see a mistake.

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)  
                };