c# - how to group by two left joins? -

i'm needing make 2 left joins , sum/count data these 2 left joins.

first tried 1 left join (valorefetuado). table may have data , if has data need sum it.

that's mi original query 1 left join , select generates (pretty , simple think)

var query = l in db.lancamentos                         join tl in db.tiposlancamentos on l.idtipolancamento equals tl.idtipolancamento                         join ve in db.valoresefetuados on l.idlancamento equals ve.idlancamento ve_leftjoin                         _ve in ve_leftjoin.defaultifempty()                      (l.excluido == false && l.idconta == idconta)                      group _ve new                     {                         tipolancamento = tl.descricao,                         datavencimento = l.datavencimento,                         idlancamento = l.idlancamento,                         idconta = l.idconta,                         valor = l.valor                     } grupo                     select new _lancamentogrid                     {                         idconta = grupo.key.idconta,                         idlancamento = grupo.key.idlancamento,                         descricao = grupo.key.tipolancamento,                         valor = grupo.key.valor,                         datavencimento = grupo.key.datavencimento,                         valorefetuado = grupo.sum(_vesum => _vesum.valor == null ? 0 : _vesum.valor),                     }; 

that select

select  [groupby1].[k1] [idlancamento],  [groupby1].[k2] [idconta],  [groupby1].[k5] [descricao],  [groupby1].[k3] [valor],  [groupby1].[k4] [datavencimento],  [groupby1].[a1] [c1] ( select      [filter2].[k1] [k1],      [filter2].[k2] [k2],      [filter2].[k3] [k3],      [filter2].[k4] [k4],      [filter2].[k5] [k5],      sum([filter2].[a1]) [a1]     ( select          [filter1].[idlancamento] [k1],          [filter1].[idconta] [k2],          [filter1].[valor] [k3],          [filter1].[datavencimento] [k4],          [filter1].[descricao] [k5],          case when ([extent3].[valor] null) cast(0 decimal(18)) else [extent3].[valor] end [a1]           (select [extent1].[idlancamento] [idlancamento], [extent1].[idconta] [idconta], [extent1].[valor] [valor], [extent1].[datavencimento] [datavencimento], [extent2].[descricao] [descricao]              [fin].[lancamento] [extent1]             inner join [fin].[tipolancamento] [extent2] on [extent1].[idtipolancamento] = [extent2].[idtipolancamento]             0 = [extent1].[excluido] ) [filter1]         left outer join [fin].[valorefetuado] [extent3] on [filter1].[idlancamento] = [extent3].[idlancamento]         [filter1].[idconta] = @p__linq__0     )  [filter2]     group [k1], [k2], [k3], [k4], [k5] )  [groupby1] 

there is, group , sum. problem is. when put new left join in query , group got demon query dosent work. big. , ineficient. in example

var query = l in db.lancamentos                         join tl in db.tiposlancamentos on l.idtipolancamento equals tl.idtipolancamento                         join ve in db.valoresefetuados on l.idlancamento equals ve.idlancamento ve_leftjoin                         _ve in ve_leftjoin.defaultifempty()                         join ld in db.lancamentosdocumentos on l.idlancamento equals ld.idlancamento ld_leftjoin                         _ld in ld_leftjoin.defaultifempty()                         (l.excluido == false && l.idconta == idconta)                      group new { _ve, _ld } new                     {                         tipolancamento = tl.descricao,                         datavencimento = l.datavencimento,                         idlancamento = l.idlancamento,                         idconta = l.idconta,                         valor = l.valor                     } grupo                     select new _lancamentogrid                     {                         idconta = grupo.key.idconta,                         idlancamento = grupo.key.idlancamento,                         descricao = grupo.key.tipolancamento,                         valor = grupo.key.valor,                         datavencimento = grupo.key.datavencimento,                         valorefetuado = grupo.sum(_vesum => _vesum._ve.valor == null ? 0 : _vesum._ve.valor),                         qtdcontadocumento = grupo.count(_ldcount => _ldcount._ld.idlancamento != null)                     }; 

this results in 1 ugly giant query simple dosent return asked for

select  [project4].[idlancamento] [idlancamento],  [project4].[idconta] [idconta],  [project4].[descricao] [descricao],  [project4].[valor] [valor],  [project4].[datavencimento] [datavencimento],  [project4].[c1] [c1],  [project4].[c2] [c2] ( select      [project3].[idlancamento] [idlancamento],      [project3].[idconta] [idconta],      [project3].[valor] [valor],      [project3].[datavencimento] [datavencimento],      [project3].[descricao] [descricao],      [project3].[c1] [c1],      (select          count(1) [a1]           (select [extent9].[idlancamento] [idlancamento1], [extent9].[idconta] [idconta], [extent9].[valor] [valor1], [extent9].[datavencimento] [datavencimento], [extent10].[descricao] [descricao]               [fin].[lancamento] [extent9]             inner join [fin].[tipolancamento] [extent10] on [extent9].[idtipolancamento] = [extent10].[idtipolancamento]             left outer join [fin].[valorefetuado] [extent11] on [extent9].[idlancamento] = [extent11].[idlancamento]             0 = [extent9].[excluido] ) [filter4]         inner join [fin].[lancamentodocumento] [extent12] on [filter4].[idlancamento1] = [extent12].[idlancamento]         ([filter4].[idconta] = @p__linq__0) , (([project3].[descricao] = [filter4].[descricao]) or (([project3].[descricao] null) , ([filter4].[descricao] null))) , ([project3].[datavencimento] = [filter4].[datavencimento]) , ([project3].[idlancamento] = [filter4].[idlancamento1]) , ([project3].[idconta] = [filter4].[idconta]) , ([project3].[valor] = [filter4].[valor1]) , ([extent12].[idlancamento] not null)) [c2]     ( select          [project2].[idlancamento] [idlancamento],          [project2].[idconta] [idconta],          [project2].[valor] [valor],          [project2].[datavencimento] [datavencimento],          [project2].[descricao] [descricao],          (select              sum([filter3].[a1]) [a1]             ( select                  case when ([extent7].[valor] null) cast(0 decimal(18)) else [extent7].[valor] end [a1]                    [fin].[lancamento] [extent5]                 inner join [fin].[tipolancamento] [extent6] on [extent5].[idtipolancamento] = [extent6].[idtipolancamento]                 left outer join [fin].[valorefetuado] [extent7] on [extent5].[idlancamento] = [extent7].[idlancamento]                 left outer join [fin].[lancamentodocumento] [extent8] on [extent5].[idlancamento] = [extent8].[idlancamento]                 (0 = [extent5].[excluido]) , ([extent5].[idconta] = @p__linq__0) , (([project2].[descricao] = [extent6].[descricao]) or (([project2].[descricao] null) , ([extent6].[descricao] null))) , ([project2].[datavencimento] = [extent5].[datavencimento]) , ([project2].[idlancamento] = [extent5].[idlancamento]) , ([project2].[idconta] = [extent5].[idconta]) , ([project2].[valor] = [extent5].[valor])             )  [filter3]) [c1]         ( select              [distinct1].[idlancamento] [idlancamento],              [distinct1].[idconta] [idconta],              [distinct1].[valor] [valor],              [distinct1].[datavencimento] [datavencimento],              [distinct1].[descricao] [descricao]             ( select distinct                  [filter1].[idlancamento2] [idlancamento],                  [filter1].[idconta] [idconta],                  [filter1].[valor2] [valor],                  [filter1].[datavencimento] [datavencimento],                  [filter1].[descricao] [descricao]                   (select [extent1].[idlancamento] [idlancamento2], [extent1].[idconta] [idconta], [extent1].[valor] [valor2], [extent1].[datavencimento] [datavencimento], [extent2].[descricao] [descricao]                       [fin].[lancamento] [extent1]                     inner join [fin].[tipolancamento] [extent2] on [extent1].[idtipolancamento] = [extent2].[idtipolancamento]                     left outer join [fin].[valorefetuado] [extent3] on [extent1].[idlancamento] = [extent3].[idlancamento]                     0 = [extent1].[excluido] ) [filter1]                 left outer join [fin].[lancamentodocumento] [extent4] on [filter1].[idlancamento2] = [extent4].[idlancamento]                 [filter1].[idconta] = @p__linq__0             )  [distinct1]         )  [project2]     )  [project3] )  [project4] 

the differences between 2 queries:

• the second 1 has new left join _ld
• the second has ... group new {_ve,_ld} ... instead of ... group _ve ...
• in end make count operation thing wanna qtdcontadocumento = grupo.count(_ldcount => _ldcount._ld.idlancamento != null)

i think misunderstanding left joins or group in entity. can't hard make work right