LINQ 3 Inner Joins with 1 Left Outer Join

Question

Wondering why LINQ doesn't have a Left Join method. I've been trying to figure this out with myriad examples on SO, but no such luck. The other examples show simple examples with one join. If I group the joins then I only get references to the TradeCountries table in the select statement.

Being new to LINQ, I could've had this done 4 hours ago with a simple SELECT statement, but here I'm am trying to figure out why the LeftJoin method was left out of LINQ.

What does the line with "LeftJoin" need to be changed to make this work?

/* 
* GetTop5Distributors 
@param  int array of series IDs
*/
public List<TopDistributors> Get5TopDistributors(IEnumerable<int> seriesIds)
{
    _context = new MySQLDatabaseContext();
    var result = _context.TradesTrades
.Join(_context.TradesSeries, tt => tt.SeriesId, ts => ts.Id, (tt, ts) => new { tt, ts })
.Join(_context.TradesTradeDistributors, tsd => tsd.tt.Id, ttd => ttd.TradeId,
    (tsd, ttd) => new { tsd, ttd })
.Join(_context.TradesOrganisations, tsdto => tsdto.ttd.DistributorId, to => to.Id,
    (tsdto, to) => new { tsdto, to })
.LeftJoin(_context.TradesCountries, tsdc => tsdc.to.CountryId, tc => tc.Id, 
    (tsdc, tc) => new {tsdc, tc})
.Where(x => seriesIds.Contains(x.tsdc.tsdto.tsd.tt.SeriesId))
.Where(x => x.tsdc.tsdto.tsd.tt.FirstPartyId == null)
.Where(x => x.tsdc.tsdto.tsd.tt.Status != "closed")
.Where(x => x.tsdc.tsdto.tsd.tt.Status != "cancelled")
.GroupBy(n => new { n.tsdc.tsdto.tsd.tt.SeriesId, n.tsdc.tsdto.ttd.DistributorId })
.Select(g =>
    new TopDistributors
    {
        SeriesId = g.Key.SeriesId,
        DistributorName = g.Select(i => i.tsdc.to.Name).Distinct().First(),
        IsinNickname = g.Select(i => i.tsdc.tsdto.tsd.ts.Nickname).Distinct().First(),
        CountryName = g.Select(i => i.tc.Name).Distinct().First(),
        CommissionTotal = Math.Ceiling(g.Sum(i => i.tsdc.tsdto.ttd.Commission))
    }
)
.OrderByDescending(x => x.CommissionTotal)
.Take(5)
.ToList();

    return result;
}

Here's the rather simple select statement that is taking orders or magnitude too long to convert to LINQ.

SELECT
trades_trades.series_id,
trades_organisations.`name`,
trades_series.nickname,
trades_countries.name as Country_Name,
SUM(trades_trade_distributors.commission) as Commission_Total
FROM
trades_trades
JOIN trades_series
ON trades_series.id = trades_trades.series_id
JOIN trades_trade_distributors
ON trades_trades.id = trades_trade_distributors.trade_id
JOIN trades_organisations
ON trades_trade_distributors.distributor_id = trades_organisations.id
LEFT JOIN trades_countries
ON trades_organisations.country_id = trades_countries.id
WHERE trades_trades.series_id   IN (
    17,
    18)
    AND trades_trades.first_party_id IS NULL
    AND trades_trades.status <> 'closed'
    AND trades_trades.status <> 'cancelled'
GROUP BY trades_trades.series_id, trades_trade_distributors.distributor_id
ORDER BY Commission_Total DESC

Answer 1

Following my recipe, here is a more or less straightforward translation of the SQL to LINQ. I moved the where to be near what it constrains, and used let to create a convenient name for the Sum, as LINQ doesn't allow you to forward reference anonymous object members.

var ans = from tt in trades_trades
          where new[] { 17, 18 }.Contains(tt.series_id) && tt.first_party_id == null &&
                tt.status != "closed" && tt.status != "cancelled"
          join ts in trades_series on tt.series_id equals ts.id
          join ttd in trades_trade_distributors on tt.id equals ttd.trade_id
          join to in trades_orginizations on ttd.distributor_id equals to.id
          join tc in trades_countries on to.country_id equals tc.id into tcj
          from tc in tcj.DefaultIfEmpty() // GroupJoin -> left join
          group new { tt, ts, ttd, to, tc } by new { tt.series_id, ttd.distributor_id } into tradeg
          let Commission_Total = tradeg.Sum(trade => trade.ttd.commission)
          orderby Commission_Total descending
          select new {
            tradeg.Key.series_id,
            tradeg.First().to.name,
            tradeg.First().ts.nickname,
            Country_Name = tradeg.First().tc == null ? null : tradeg.First().tc.name,
            Commission_Total
          };