I have a table full of data split across year, brand and customer which I want to join with a customer master table. I want each output record to be customer, brand1 qty for the year, brand2 qty for the year, etc and a total of the brands. I created a class to recieve the output. When I view the data in the statistics table it has data which should meet the criteria used (i.e. debtorid, financialyearid and brandid.
My select currently looks like this
var Debtors = Dbs.Debtors;
var BikeSales = Dbs.SalesBikes.DefaultIfEmpty();
if (sortStatePostCode)
{
dquery = from y in Debtors
from a in BikeSales where a.DebtorID == y.ID && a.FinYearID == finYear.ID && a.BikeBrandID == 1
from b in BikeSales where b.DebtorID == y.ID && b.FinYearID == finYear.ID && b.BikeBrandID == 2
from c in BikeSales where c.DebtorID == y.ID && c.FinYearID == finYear.ID && c.BikeBrandID == 3
from d in BikeSales where d.DebtorID == y.ID && d.FinYearID == finYear.ID && d.BikeBrandID == 4
from e in BikeSales where e.DebtorID == y.ID && e.FinYearID == finYear.ID && e.BikeBrandID == 7
from f in BikeSales where f.DebtorID == y.ID && f.FinYearID == finYear.ID && f.BikeBrandID == 8
from g in BikeSales where g.DebtorID == y.ID && g.FinYearID == finYear.ID && g.BikeBrandID == 9
from h in BikeSales where 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 = a == 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 what financialyearid I send to the query my return set is empty. I am able to get results when I create a view in SQL management studio which essentially achieves the same thing I want to get but I dont want to use views.
Could anyone point me in the right direction please. (Edited code to fix a few typos).
Several other posts have addressed the question in other ways (i.e. should you use multi-select or Join On etc.). I could not find a suitable Join On with multi column equals to work from and I need the three column selection which is why I have tried this way. Other posts have shown this method as achieving a join while others suggest this can't work.
Utilising Join ... On ... equals format has not worked for me with multiple columns or I would be using it.
