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

You need a `GroupJoin`. Perhaps this could be useful: [Perform left outer joins](https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins) — Silvermind, Aug 19 '19 at 13:50
Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Silvermind, Aug 19 '19 at 13:51
I've seen these examples. 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. — Intrepid2020, Aug 19 '19 at 14:17
It is very tedious to use method chaining syntax and pass variables that way, because you need to pass them through with the `new {}` (anonymous object) syntax. Perhaps you can rewrite your query using the query syntax. It also makes it more readable imo. — Silvermind, Aug 19 '19 at 15:29
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? — NetMage, Aug 19 '19 at 17:02
You really should look first whether you need these LINQ joins. Unfortunately you don't tell which data library you're using but I guess it's Entity Framework. That means: don't join, use navigation properties.These navigation properties will automatically be translated to the appropriate type of SQL join, inner or outer. — Gert Arnold, Aug 19 '19 at 18:14
Watching a video about navigation properties now, Gert. Will get back to you. — Intrepid2020, Aug 19 '19 at 19:20
OK, what did you learn about navigation properties? You'll get a far more readable LINQ query if you use them. — Gert Arnold, Aug 20 '19 at 09:08

NetMage · Accepted Answer · 2019-08-19T21:38:43.963

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
          };

edited Aug 19 '19 at 21:38

answered Aug 19 '19 at 17:37

NetMage

26,163
3
34
55

1

Interesting. It's definitely in an order I haven't seen yet. Getting an error: System.NullReferenceException: Object reference not set to an instance of an object. at lambda_method(Closure , IGrouping`2 ) The country name can be null which is why the left join in the first place, so I need the null records, but I'm not sure how to return those and avoid this error. – Intrepid2020 Aug 19 '19 at 19:13
@Intrepid2020 Can you try `tradeg.First()?.tc.name` ? Are you using LINQ to SQL or EF or EF Core? – NetMage Aug 19 '19 at 20:53
That doesn't work. I'm using EF Core. Sorry, I should've included that before. – Intrepid2020 Aug 19 '19 at 20:55
@Intrepid2020 Can you verify `Country_Name` is where the error is occurring (comment out the member)? I updated my answer to try using a conditional test assuming that is the problem. – NetMage Aug 19 '19 at 21:39
Yes, that works. I wonder why neither the null coalesce feature or the null propagating operator don't work in Core. Anyway, I have a lot to learn about LINQ. Your example and your SQL to LINQ recipe are good resources that I'll use to build on. Thanks for your assistance with this one. – Intrepid2020 Aug 20 '19 at 10:00
@Intrepid2020 Sadly, C# hasn't properly updated `Expression` tree support since C# 6 when the null operators were added to the language, so LINQ queries can't support the newer functionality (yes, it is pitiful). You can see the discussion (from 2017) [here](https://github.com/dotnet/csharplang/issues/158). – NetMage Aug 20 '19 at 18:53
You've been following this for awhile, I see. I'm going to look through some of your old examples and threads to continue getting up to speed on LINQ. It's a bit of a learning curve, but once you know the how it works, it's much easier to maintain that large SQL select statements in your code. I wish I could buy you a beer. )) – Intrepid2020 Aug 21 '19 at 17:24

LINQ 3 Inner Joins with 1 Left Outer Join

1 Answers1