LINQ with Left Join and Multiple Keys

Question

My LINQ query does not give error but does not show any result. Where I made a mistake?

 using (var db = new MyEntities())
 {

   var result = (from dc in db.DiarioClasse
        join fn in db.Faltas
         on new { dc.Matricula, dc.CdDisciplina, dc.Turma }
          equals new { fn.Matricula, CdDisciplina = fn.Disciplina, Turma = fn.CdTurma } into fn_join
              from fn in fn_join.DefaultIfEmpty()
                  where dc.Turma == dadosForm.DesTurma
                            && dc.Modulo == dadosForm.Modulo
                            && dc.CodDisc == dadosForm.CdDisciplina
                        orderby dc.NrDiario
                        select new DiarioClasseMod
                        {
                            Id = dc.ID,
                            NrDiario = dc.NroDiario,
                            MatriculaAluno = dc.Matricula,
                            Turma = dc.Turma,
                            CdDisciplina = dc.CodDisc,
                            CdProfessor = dc.CodProf,
                            FaltaDoDia = fn.Falta,
                            TotalFaltas = (from f in db.Faltas
                                           where
                                             f.Matricula == dc.Matricula &&
                                             f.Disciplina == dc.CodDisciplina
                                           select new FaltaMod
                                           {
                                               Faltas = f.Falta
                                           }).Sum(x => x.Faltas)
                        }).ToList();

Below is my Select that generates the information:

    select dc.Id, 
    dc.NroDiario, 
    dc.Matricula, 
    dc.DesTurma, 
    dc.CodDisc, 
    dc.CodProf, 
    fn.Falta as FaltaDia, 
    fn.Dia, 
         (SELECT SUM(Falta) FROM FaltasNovas F WHERE F.Matricula = dc.Matricula 
             and f.Disciplina = dc.CodDisc ) AS TotalFaltas
        from DiarioClasse dc left join FaltasNovas fn on dc.Matricula = fn.Matricula and dc.CodDisc = fn.Disciplina and dc.DesTurma = fn.Desturma 
       where
        fn.dia like '2014-08-21'and
     dc.DesTurma like 'DIMA00814'
       and dc.matricula = 65374
     order by dc.NroDiario;

Answer 1

It’s too long. It’s a classic example of code spaghetti.

Try simplifying pro next:

var faltas = (from dc in db.DiarioClasse
    join fn in db.Faltas
    on new { dc.Matricula, dc.CdDisciplina, dc.Turma }
    equals new { dc.ID, dc.Matricula, dc.CodDisciplina, dc.Turma, dc.NroDiario, dc.CodDisc, dc.CodProf, fn.Falta }
    where dc.Turma == dadosForm.DesTurma
        && dc.Modulo == dadosForm.Modulo
        && dc.CodDisc == dadosForm.CdDisciplina
    orderby dc.NrDiario).ToList();

Total shortages you can popular after:

var result = (from d in faltas
    select new DiarioClasseMod {
        Id = d.ID,
        NrDiario = d.NroDiario,
        MatriculaAluno = d.Matricula,
        Turma = dc.Turma,
        CdDisciplina = d.CodDisc,
        CdProfessor = d.CodProf,
        FaltaDoDia = d.Falta,
        TotalFaltas = (from f in db.Faltas
            where f.Matricula == d.Matricula &&
            f.Disciplina == d.CodDisciplina
            select new {
                Faltas = f.Falta
            }).Sum(x => x.Faltas)
    }).ToList()

So at least you can check step by step what the LINQ command is doing.