Sql to Linq using left join and sum

Question

I've been playing around with Linq for a couple of days but i'm stuck with a bit of a problem here. The query is this which follows:

select s.Nombre, emp.RFC,
isnull(sum(case when f.EsCancelada=0 then 1 else 0 end),0) emitidas,
isnull(sum(case when f.EsCancelada=1 then 1 else 0 end),0) canceladas
from serie s
inner join Empresa emp 
    on emp.EmpresaUID= s.EmpresaUID
left join Folio fo 
    on s.SerieID=fo.SerieID
left join Factura f 
    on f.FolioID = fo.FolioID
        and (f.FechaEmision >= "2010-07-1" 
            and f.FechaEmision <= "2010-08-1" )
group by s.Nombre, emp.RFC

right now, i've got:

   mensual = (from s in bd.Series
         join emp in bd.Empresas on s.EmpresaUID equals emp.EmpresaUID
         join fo in bd.Folios on s.SerieID equals fo.SerieID
         join f in bd.Facturas on fo.FolioID equals f.FolioID /*left join on dates*/

         select new ReporteMimoss
            {
               Nombre = s.Nombre,
               RFC = emp.RFC,
               emitidas =   (f.EsCancelada == false ? 1 : 0),
               canceladas = (f.EsCancelada == true ? 1 : 0), /*missing sum*/
            }).ToList();

Thanks for your help!

Answer 1

The date clause isn't a join condition, it's a where condition. Also, I'm going to assume that you have all the proper Foreign key relationships set up, so LINQ does the joins automatically.

  mensual = (from s in bd.Series 
             let facturas = s.Folio.Facturas
                  .Where(fa => fa.FechaEmision >= "2010-07-1" 
                          &&   fa.FechaEmision <= "2010-08-1" )  
             select new ReporteMimoss 
             { 
                Nombre = s.Nombre, 
                RFC = s.Empresa.RFC, 
                emitidas = facturas .Count(fa=> !fa.EsCancelada),
                canceladas = facturas .Count(fa=> fa.EsCancelada)
             }).ToList();