C# LINQ to SQL Multiple entity join -

i have table full of data split across year, brand , customer want join customer master table. want each output record customer, brand1 qty year, brand2 qty year, etc , total of brands. created class recieve output. when view data in statistics table has data should meet criteria used (i.e. debtorid, financialyearid , brandid.

my select looks this

            var debtors = dbs.debtors;             var bikesales = dbs.salesbikes.defaultifempty();             if (sortstatepostcode)             {                 dquery = y in debtors                          in bikesales a.debtorid == y.id && a.finyearid == finyear.id && a.bikebrandid == 1                          b in bikesales b.debtorid == y.id && b.finyearid == finyear.id && b.bikebrandid == 2                          c in bikesales c.debtorid == y.id && c.finyearid == finyear.id && c.bikebrandid == 3                          d in bikesales d.debtorid == y.id && d.finyearid == finyear.id && d.bikebrandid == 4                          e in bikesales e.debtorid == y.id && e.finyearid == finyear.id && e.bikebrandid == 7                          f in bikesales f.debtorid == y.id && f.finyearid == finyear.id && f.bikebrandid == 8                          g in bikesales g.debtorid == y.id && g.finyearid == finyear.id && g.bikebrandid == 9                          h in bikesales h.debtorid == y.id && h.finyearid == finyear.id && h.bikebrandid == 10                           orderby y.sortdelstate == null ? "zzzz" : y.sortdelstate, y.sortdelpcode == null ? "9999" : y.sortdelpcode, y.customername                          select new dealerbikeresult                          {                              longyear = finyear,                              customercode = y.customercode,                              customername = y.customername,                              city = y.sortdelcity,                              postcode = y.sortdelpcode,                              state = y.sortdelstate,                              terms = y.termscode,                              total = (                                  (f == null ? 0 : (int)f.totalqty) +                                  (g == null ? 0 : (int)g.totalqty) +                                  (a == null ? 0 : (int)a.totalqty) +                                  (b == null ? 0 : (int)b.totalqty) +                                  (c == null ? 0 : (int)c.totalqty) +                                  (d == null ? 0 : (int)d.totalqty) +                                  (e == null ? 0 : (int)e.totalqty) +                                  (h == null ? 0 : (int)h.totalqty)                              ),                              bombtrack = f == null ? 0 : (int)f.totalqty,                              fairdale = g == null ? 0 : (int)g.totalqty,                              mirraco = == null ? 0 : (int)a.totalqty,                              radio = b == null ? 0 : (int)b.totalqty,                              redline = c == null ? 0 : (int)c.totalqty,                              sunday = d == null ? 0 : (int)d.totalqty,                              united = e == null ? 0 : (int)e.totalqty,                              wtp = h == null ? 0 : (int)h.totalqty,                              dealerbo = y.dealerbombtrack == null ? 3 : (int)y.dealerbombtrack,                              dealerfa = y.dealerfairdale == null ? 3 : (int)y.dealerfairdale,                              dealermi = y.dealermirraco == null ? 3 : (int)y.dealermirraco,                              dealerra = y.dealerradio == null ? 3 : (int)y.dealerradio,                              dealerrl = y.dealerredline == null ? 3 : (int)y.dealerredline,                              dealersu = y.dealersunday == null ? 3 : (int)y.dealersunday,                              dealerun = y.dealerunited == null ? 3 : (int)y.dealerunited,                              dealerwp = y.dealerwtp == null ? 3 : (int)y.dealerwtp                          };             } 

no matter financialyearid send query return set empty. able results when create view in sql management studio achieves same thing want dont want use views.

could point me in right direction please. (edited code fix few typos).

several other posts have addressed question in other ways (i.e. should use multi-select or join on etc.). not find suitable join on multi column equals work , need 3 column selection why have tried way. other posts have shown method achieving join while others suggest can't work.

utilising join ... on ... equals format has not worked me multiple columns or using it.

apart fact not doing joins properly, query clause not seems okay. wasn't supposed ? -

from in dbs.salesbikes.defaultifempty() a.debtorid == y.id && finyear.id == a.finyearid && a.bikebrandid == 1 b in dbs.salesbikes.defaultifempty() b.debtorid == y.id && b.finyearid == finyear.id && b.bikebrandid == 2 c in dbs.salesbikes.defaultifempty() c.debtorid == y.id && c.finyearid == finyear.id && c.bikebrandid == 3 d in dbs.salesbikes.defaultifempty() d.debtorid == y.id && d.finyearid == finyear.id && d.bikebrandid == 4 e in dbs.salesbikes.defaultifempty()  e.debtorid == y.id && e.finyearid == finyear.id && e.bikebrandid == 7 f in dbs.salesbikes.defaultifempty()  f.debtorid == y.id && f.finyearid == finyear.id && f.bikebrandid == 8 g in dbs.salesbikes.defaultifempty()  g.debtorid == y.id && g.finyearid == finyear.id && g.bikebrandid == 9 h in dbs.salesbikes.defaultifempty()  h.debtorid == y.id && h.finyearid == finyear.id && h.bikebrandid == 10