using multiple IEnumerable Joins

Question

If i have 3 tables. How can i use IEnumerable LINQ Joins to join all 3 tables together? here is my current query and I want to join another IEnumerable called BudgetTargets using the GPRow id = Budgettargets id

var rows = _context.GpRows.Join(
            _context.ShopInfos,
            GpRow => GpRow.ShopId,
            ShopInfo => ShopInfo.ShopId,
            (GpRow, ShopInfo) => new{ 
                ShopName = ShopInfo.ShopName,
                State = ShopInfo.State,
                YearMonth = String.Concat(GpRow.Year, GpRow.Month),
                GpRow.Year,
                GpRow.Month,
                GpRow.Id,
                GpRow.ShopId,
                GpRow.PaintSale,
                GpRow.PanelSale,
                GpRow.PartsSale,
                GpRow.OtherSale,
                GpRow.PaintCost,
                GpRow.PanelCost,
                GpRow.PartsCost,
                GpRow.OtherCost,
                GpRow.PanelWages,
                GpRow.PaintWages,
                GpRow.Depreciation,
                GpRow.ForecastedSales,
                GpRow.Expenses
            }
        )
        .Where(GpRow => shopId_Array.Contains(GpRow.ShopId))
        .OrderByDescending(a => a.Year).OrderByDescending(b => b.Month).ThenBy(c => c.State).ThenBy(c => c.ShopName)
        .ToList();

If this is EF I'd suggest looking into using Navigation properties assuming the tables you want to join have proper FKs setup. That or you might find this easier to write in query syntax, for example `from x in table1 join y in table2 on x.Id equals y.XId join z in table3 on y.Id equals z.YId` — juharr, Aug 26 '20 at 03:39
Thanks Juharr however that join only returns matches I want a left join :( — Michael Zerofive Jenkins, Aug 26 '20 at 07:30
@MichaelZerofiveJenkins, please check if [this](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) may be helpful. In case of left join, you probably need to think about `DefaultIfEmpty`. — Arsen Khachaturyan, Aug 26 '20 at 09:24
@MichaelZerofiveJenkins You should include in your question that you want left joins. Those are done like this `from x in table1 join y in table2 on x.Id equals y.XId into grp from y in grp.DefaultIfEmpty()` — juharr, Aug 26 '20 at 11:42

score 1 · Accepted Answer · answered Aug 26 '20 at 04:13

I agree with @juharr's comment. The LINQ query format is sometimes clearer about intent than the fluent API style. Here is how the query would be written.

var rows = from GpRow in _context.GpRows
    join ShopInfo in _context.ShopInfos on GpRow.ShopId equals ShopInfo.ShopId
    orderby GpRow.ShopId descending, GpRow.Month descending, ShopInfo.State, ShopInfo.ShopName
    select new { 
        ShopName = ShopInfo.ShopName,
        State = ShopInfo.State,
        YearMonth = String.Concat(GpRow.Year, GpRow.Month),
        GpRow.Year,
        GpRow.Month,
        GpRow.Id,
        GpRow.ShopId,
        GpRow.PaintSale,
        GpRow.PanelSale,
        GpRow.PartsSale,
        GpRow.OtherSale,
        GpRow.PaintCost,
        GpRow.PanelCost,
        GpRow.PartsCost,
        GpRow.OtherCost,
        GpRow.PanelWages,
        GpRow.PaintWages,
        GpRow.Depreciation,
        GpRow.ForecastedSales,
        GpRow.Expenses
    };

using multiple IEnumerable Joins

1 Answers1