LINQ 3 个内连接和 1 个左外连接

Question

想知道为什么 LINQ 没有 Left Join 方法。我一直试图用无数的例子来解决这个问题，但没有那么幸运。其他示例显示了带有一个连接的简单示例。如果我对联接进行分组，那么我只会在 select 语句中获得对 TradeCountries 表的引用。

作为 LINQ 的新手，我本可以在 4 小时前使用简单的 SELECT 语句完成此操作，但在这里我试图找出为什么 LeftJoin 方法被排除在 LINQ 之外。

需要更改“LeftJoin”行才能使其正常工作？

/* 

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

}

这是相当简单的 select 语句，其数量级或数量级太长，无法转换为 LINQ。

Answer 1

按照我的食谱，这里是 SQL 到 LINQ 的或多或少简单的转换。我将 移至where接近其约束的位置，并用于let为 创建一个方便的名称Sum，因为 LINQ 不允许您转发引用匿名对象成员。var ans = from tt in trades_trades&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where new[] { 17, 18 }.Contains(tt.series_id) && tt.first_party_id == null &&&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tt.status != "closed" && tt.status != "cancelled"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join ts in trades_series on tt.series_id equals ts.id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join ttd in trades_trade_distributors on tt.id equals ttd.trade_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join to in trades_orginizations on ttd.distributor_id equals to.id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join tc in trades_countries on to.country_id equals tc.id into tcj&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from tc in tcj.DefaultIfEmpty() // GroupJoin -> left join&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; group new { tt, ts, ttd, to, tc } by new { tt.series_id, ttd.distributor_id } into tradeg&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let Commission_Total = tradeg.Sum(trade => trade.ttd.commission)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; orderby Commission_Total descending&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select new {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tradeg.Key.series_id,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tradeg.First().to.name,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tradeg.First().ts.nickname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Country_Name = tradeg.First().tc == null ? null : tradeg.First().tc.name,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Commission_Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; };