c# - 如何从 C# linq 查询中的表中选择最大日期

Question

我在这里，我需要有人帮忙。我想从 Refno 等于 Refno 的不同表中获取最大日期。我当前的代码为我提供了 actionrecords 表中存在的所有日期。

我当前的代码看起来像

public IQueryable<EmployerDetails> _employerProfileOverview(string refNo, string organisationname, string tradingname)
{
    var query = (from _employerdetails in _entities.Organisations
                 join _lasttransaction in _entities.LevyTransactions on _employerdetails.Refno equals _lasttransaction.Refno
                 select new EmployerDetails
                 {
                     RefNo = _employerdetails.Refno,
                     Surname = _employerdetails.Surname,
                     TradingName = _employerdetails.TradingName,
                     Status = _employerdetails.Status,
                     TransactionDate = _lasttransaction.TransactionDate
                 });
    if (string.IsNullOrEmpty(refNo).Equals(false))
        query = query.Where(x => x.RefNo.Contains(refNo.Trim()));

    if (string.IsNullOrEmpty(organisationname).Equals(false))
        query = query.Where(x => x.Surname.Contains(organisationname.Trim()));

    if (string.IsNullOrEmpty(tradingname).Equals(false))
        query = query.Where(x => x.TradingName.Contains(tradingname.Trim()));

    return query;
}

我的输出看起来像这样

 Refno      TradingName                  Status  TransactionDate
L940732953  PSG KONSULT BPK LICHTENBURG     A   2011-01-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-03-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-04-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-05-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-06-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-07-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-08-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-09-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-10-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-11-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2012-12-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-01-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-02-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-03-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-04-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-05-01 00:00:00.000
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-05-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-06-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-11-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2013-12-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-01-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-02-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-03-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-04-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-05-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-06-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-07-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-08-01 00:00:00.00  
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-09-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-10-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-11-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2014-12-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2015-01-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2015-02-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2015-03-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2015-04-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2015-05-01 00:00:00.000 
L940732953  PSG KONSULT BPK LICHTENBURG     A   2015-06-01 00:00:00.000 
L940733035  BROUGHTON AND JONCK             A   2011-05-01 00:00:00.000 
L940733035  BROUGHTON AND JONCK             A   2011-05-01 00:00:00.000 
L940733035  BROUGHTON AND JONCK             A   2011-05-01 00:00:00.000 
L940733035  BROUGHTON AND JONCK             A   2011-05-01 00:00:00.000 
L940733035  BROUGHTON AND JONCK             A   2011-05-01 00:00:00.000 
L940733035  BROUGHTON AND JONCK             A   2011-05-01 00:00:00.000 
L940736574  COVERS FOODS CC                 Y   2011-03-01 00:00:00.000 
L940736574  COVERS FOODS CC                 Y   2011-03-01 00:00:00.000 
L940736574  COVERS FOODS CC                 Y   2011-05-01 00:00:00.000 
L940736574  COVERS FOODS CC                 Y   2011-05-01 00:00:00.000 
L940738141  TEAMWORX SHOPFITTERS (PTY) LTD  A   2011-01-01 00:00:00.000
L940738141  TEAMWORX SHOPFITTERS (PTY) LTD  A   2011-02-01 00:00:00.000
L940738141  TEAMWORX SHOPFITTERS (PTY) LTD  A   2011-02-01 00:00:00.000
L940738141  TEAMWORX SHOPFITTERS (PTY) LTD  A   2011-04-01 00:00:00.000
L940738141  TEAMWORX SHOPFITTERS (PTY) LTD  A   2011-05-01 00:00:00.000

我只想显示每个 Refno 的 max(Transaction)。

请大家帮帮我。

Answer 1

你需要在这里使用group by并使用Max查找最大交易日期的扩展方法:-

 var query = (from _employerdetails in _entities.Organisations
              join _lasttransaction in _entities.LevyTransactions 
              on _employerdetails.Refno equals _lasttransaction.Refno
              group new { _employerdetails, _lasttransaction } 
              by new { _employerdetails.Refno, 
                       _employerdetails.Surname_employerdetails.TradingName, 
                       _employerdetails.Status } into g
              select new EmployerDetails
              {
                  RefNo = g.Key.RefNo ,
                  Surname = g.Key.Surname,
                  TradingName = g.Key.TradingName,
                  Status = g.Key.Status,
                  TransactionDate = g.Max(x => x._lasttransaction .TransactionDate)
              });

此外，作为自 String.IsNullOrEmpty 以来的旁注返回 bool 值，无需与 false 或 true 进行比较。你可以直接这样做:-

if (!string.IsNullOrEmpty(refNo))
        query = query.Where(x => x.RefNo.Contains(refNo.Trim()));