如何在Where条件、Select 中使用 子查询 查询分表数据？ 返回

说明：以下代码为供应商超期欠款统计功能。目前对项目表、项目成本表、供应商付款申请表，进行了按年分表。目前统计 超过应付账期 未付款的数据 ，需要在Where 条件中写子查询，

查询 供应商付款申请表 （已按年分表），但是数据可能包含2024年或2025年，这种情况该如何使用 子查询 查询分表数据？Select 中也是同种情况，求大佬指点一下！

代码说明：

ProProjects：项目表

_buySupplierRep：供应商表

ProCost：项目成本表

ProPayRequest：供应商付款申请表

未分表功能实现代码：

public async Task<SqlSugarPagedList<BuySupplierStatisticsPage>> BuySupplierOverduePayment(PageBuySupplierInput input)

{

    var time = DateTime.Now;

    var query = await _buySupplierRep.AsQueryable()

        .WhereIF(!string.IsNullOrWhiteSpace(input.Keyword), it => it.Name.Contains(input.Keyword.Trim()) || it.City.Contains(input.Keyword.Trim()))

        .WhereIF(!string.IsNullOrWhiteSpace(input.Name), it => it.Name.Contains(input.Name.Trim()))

        .WhereIF(!string.IsNullOrWhiteSpace(input.City), it => it.City.Contains(input.City.Trim()))

        .Where(it =>

            SqlFunc.IsNull((SqlFunc.Subqueryable<ProPayRequest>().InnerJoin<ProCost>((a, b) => a.Cost_Id == b.Id)

            .Where((a, b) => b.Supplier_Id == it.Id && a.Type == 0 && (a.Status == "2") && a.IsDelete == false && b.IsDelete == false && SqlFunc.DateAdd(a.PayDate.Value, it.Period.Value) < time)

            .Sum((a, b) => a.Amount)), 0) > 0)  //筛选超过应付账期 未付款的数据

        .Select(it => new BuySupplierStatisticsPage

        {

            Id = it.Id,

            Name = it.Name,

            Type = it.Type,

            City = it.City,

            DuiZhang = it.DuiZhang,

            Period = it.Period,

            Status = it.Status,

            Remarks = it.Remarks,

            DebtTotalAmount = SqlFunc.Subqueryable<ProPayRequest>().InnerJoin<ProCost>((a, b) => a.Cost_Id == b.Id)

            .Where((a, b) => b.Supplier_Id == it.Id && a.Type == 0 && (a.Status == "2") && a.IsDelete == false && b.IsDelete == false && SqlFunc.DateAdd(a.PayDate.Value, it.Period.Value) < time)

            .Sum((a, b) => a.Amount),

            BuySupplierAmount = SqlFunc.Subqueryable<ProPayRequest>().InnerJoin<ProCost>((a, b) => a.Cost_Id == b.Id).InnerJoin<ProProjects>((a, b, c) => a.Pro_Id == c.Id)

            .Where((a, b, c) => b.Supplier_Id == it.Id && a.Type == 0 && (a.Status == "2") && a.IsDelete == false && b.IsDelete == false && c.IsDelete == false && SqlFunc.DateAdd(a.PayDate.Value, it.Period.Value) < time)

            .ToList((a, b, c) => new BuySupplierAmount

            {

                Id = a.Id,

                ProId = a.Pro_Id,

                ProjectName = c.Title,

                ProjectCode = c.ProCode,

                ProjectDebtAmount = a.Amount,

                Time = a.PayDate,

                OverdueTime = a.PayDate.Value.AddDays(it.Period.Value)

            }),

        })

        .MergeTable()

        .OrderBy("DebtTotalAmount desc NULLS LAST")

        .ToPagedListAsync(input.Page, input.PageSize);

    return query;

}