如何基于用户输入日期使用LINQ生成整月报表？

核心问题定位 #

你的代码中使用Where(x => x.LcOpenDate == id)进行日期匹配，这会严格筛选出日期完全一致的数据，因此只能返回当日报表。要生成整月报表，需将日期过滤条件修改为匹配输入日期所在的整月范围。

修改后的完整代码 #

[HttpGet("inner-join/{id}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public IActionResult GetReport(DateTime id)
{
  try
  {
    // 计算输入日期所在月份的起始与结束时间
    var startOfMonth = new DateTime(id.Year, id.Month, 1);
    var endOfMonth = startOfMonth.AddMonths(1).AddTicks(-1);

    IEnumerable<BTBPending> objBTBPendingList = _unitOfWork.BTBPending.GetAll(includeProperties: "ProformaInvoice,ContractList,SupplierList,CountryList,ItemList,BuyerList,StyleList,TradeTermList,ErpRemarksList,StatusList,LcNoList,UdAmendList");

    IEnumerable<ProformaInvoice> objProformaInvoiceList = _unitOfWork.ProformaInvoice.GetAll(includeProperties: "ActualContract,ContractList,SupplierList,CountryList,ItemList,BuyerList,StyleList,TradeTermList");


    var query = objBTBPendingList
        // 替换为当月范围匹配条件
        .Where(x => x.LcOpenDate >= startOfMonth && x.LcOpenDate <= endOfMonth)
        .Where(x => x.CountryListId == 26)
        .Where(x => x.StatusListId == 12 || x.StatusListId == 13 || x.StatusListId == 14)
        .Join(objProformaInvoiceList,
              btbPending => btbPending.ContractListId,
              pi => pi.ContractListId, 
              (btbPending, pi) => new
              {
                LcNo = btbPending.LcNoList,
                Value = btbPending.PiValue,

                ContractNo = pi.ContractList,
                Buyer = pi.BuyerList,
                PiNo = pi.PINo,
                Supplier = pi.SupplierList,
                Item = pi.ItemList
              }).ToList();


     return Ok(query);
   }
 catch (Exception ex)
   {
     return StatusCode(500, "Internal Server Error, Please Try Again Later!");
   }
}

关键修改说明 #

1. 日期范围计算：通过startOfMonth获取当月1号的0点时间，endOfMonth获取当月最后一天的23:59:59.9999999，确保覆盖当月所有时段的数据
2. 过滤条件替换：将原精确日期匹配x.LcOpenDate == id改为范围匹配，筛选出当月所有符合条件的数据
3. 拼写修正：修复了错误提示中的拼写错误（Leter改为Later）

额外优化提示 #

• 若LcOpenDate为可空类型DateTime?，需添加x.LcOpenDate.HasValue判断，避免空引用异常
• 可将日期范围计算逻辑封装为工具方法，提升代码复用性

内容的提问来源于stack exchange，提问作者Masum Rayhan