Linq with Left Join and multiples Key not Work

Question

I need help please!

When I perform the following LINQ query, it works perfectly (shows results)

   using (var db = new MyEntities())
   {
    var result = (from dc in db.ClassDiary
                            where dc.DesTurm == dataForm.DesTurma
                            && dc.Module == dataForm.Module
                            && dc.CodDisc == dataForm.CdDisc
                        orderby dc.NrDiary
                        select new ClassDiaryMod
                        {
                            Id = dc.ID,
                            NrDiary = dc.NrDiary,
                            NrStudant = dc.NrStudant,
                            DesTurma = dc.DesTurma,
                            CdDisc = dc.CodDisc,
                            CdTeac = dc.CodTeac,
                            TotalFoult = (from f in db.Foult
                                           where
                                             f.NrStudant == dc.NrStudant &&
                                             f.Disc == dc.CodDisc
                                           select new FoultMod
                                           {
                                               Foults = f.Foult
                                           }).Sum(x => x.Foults)
                        }).ToList();

  return result;

When I try to apply the left join with multiple key does not display results

    using (var db = new FamespEntities())
    {

      var result = (from dc in db.ClassDiary
                              join fn in db.Foult
                                      on new { dc.NrStudant, dc.CodDisc, dc.DesTurm }
                                  equals new { fn.NrStudant, CodDisc = fn.Disc, DesTurm = fn.Desturm } into fn_join
                              from fn in fn_join.DefaultIfEmpty()
                            where dc.DesTurm == dataForm.DesTurm
                            && dc.Module == dataForm.Module
                            && dc.CodDisc == dataForm.CdDisc
                        orderby dc.NroDiary
                        select new ClassDiaryMod
                        {
                            Id = dc.Id,
                            NrDiary = dc.NroDiary,
                            NrStudant = dc.NrStudant,
                            DesTurm = dc.DesTurm,
                            CdDisc = dc.CodDisc,
                            CdTeac = dc.CodTeac,
                            FoultOfDay = fn.Foult,
                            TotalFoults = (from f in db.Foult
                                           where
                                             f.NrStudent == dc.NrStudant &&
                                             f.Disc == dc.CodDisc
                                           select new FoultMod
                                           {
                                               Foults = f.Foult
                                           }).Sum(x => x.Foults)
                        }).ToList();

Like to understand why the first code works and the second does not. Thank you so much

DaniCE · Accepted Answer · 2014-09-18T15:32:37.927

0

Your equals

on new { dc.NrStudant, dc.CodDisc, dc.DesTurm }
equals new { fn.NrStudant, CodDisc = fn.Disc, DesTurm = fn.Desturm }

Is not correct, it should be

on new { NrStudant = dc.NrStudant, CodDisc = dc.CodDisc, DesTurm = dc.DesTurm }
equals new { NrStudant = fn.NrStudant, CodDisc = fn.Disc, DesTurm = fn.Desturm }

so field comparison could work.

edited Sep 18 '14 at 15:32

answered Sep 18 '14 at 15:26

DaniCE

2,412
1
19
27

The error changed. Now Visual Studio asks me to do one GroupJoin. Could you help me with this, I have no knowledge. Thank you! – Rolim Sep 18 '14 at 16:30
I'm not sure but i think you are already doing a group join, see [this](http://stackoverflow.com/questions/15595289/linq-to-entities-join-vs-groupjoin). – DaniCE Sep 18 '14 at 16:39

Linq with Left Join and multiples Key not Work

1 Answers1