EF Core 2.2 分页查询, 总数Count与分页数据不一致的问题,与解决方案

 public PageResult2<List<Order>> PageAdminOrders(string customerId, List<string> customerIds, int? productId, OrderStatus? status, string userName, int pageIndex, int pageSize)
        {
            var query = _context.Orders.Include(r => r.Config).Include(r => r.Config.Product).Include(r => r.Config.ContractOrder.Contract).AsQueryable();
            if (productId.HasValue)
                query = query.Where(r => r.Config.Product.Id == productId);
            if (status.HasValue)
                query = query.Where(r => r.Status == status.Value.ToString());
            if (!string.IsNullOrEmpty(userName))
                query = query.Where(r => r.PeopleName == userName);
            query.OrderByDescending(r => r.Id);

            if (customerId.IsNullOrEmpty() == false)
                query = query.Where(r => r.CustomerId == customerId);
            if (customerIds.IsNullOrEmpty() == false)
                query = query.Where(r => customerIds.Contains(r.CustomerId));

            var count = query.Count();
            var data = query.OrderByDescending(r => r.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

            var page = new PageResult2<List<Order>>
            {
                Count = count,
                Data = data,
            };
            return page;
        }

得到的结果,Count=1548 Data只有一条数据, 相当崩溃

{
  "code": 0,
  "msg": "",
  "count": 1548,
  "pageSize": 20,
  "pageIndex": 1,
  "data": [
    {
      "orderId": 1755,
      "orderCode": "",
      "customerId": null,
      "customerName": "ATM公司",
      "peopleId": "440508****4919",
      "peopleName": "We***is",
      "contractCode": "FYU-HC-202206011445",
      "orderStatusStr": "待提交",
      "productName": "Test套餐2",
      "reserveCount": 0
    }
  ]
}

获得Count的SQL

SELECT COUNT(*)
FROM `Orders` AS `e`
WHERE (`e`.`IsDelete` = FALSE) AND `e`.`CustomerId` IN ('CU2019****001', 'CU2021***001', 'CU202****0001')

获得Data的SQL

SELECT `e`.`Id`, `e`.`Age`,*****, `t0`.`SignedDate`, `t0`.`Status`, `t0`.`Title`
FROM `Orders` AS `e`
INNER JOIN `ProductConfigs` AS `r.Config` ON `e`.`ConfigId` = `r.Config`.`Id`
INNER JOIN (
    SELECT `b`.`Id`, ***,`b`.`TypeId`
    FROM `Products` AS `b`
    WHERE `b`.`IsDelete` = FALSE
) AS `t` ON `r.Config`.`ProductId` = `t`.`Id`
INNER JOIN `ContractOrders` AS `r.Config.ContractOrder` ON `r.Config`.`ContractOrderId` = `r.Config.ContractOrder`.`Id`
INNER JOIN (
    SELECT `e0`.`Id`, ***, `e0`.`Title`
    FROM `Contracts` AS `e0`
    WHERE `e0`.`IsDeleted` = FALSE
) AS `t0` ON `r.Config.ContractOrder`.`ContractId` = `t0`.`Id`
WHERE (`e`.`IsDelete` = FALSE) AND `e`.`CustomerId` IN ('CU2019***001', 'CU2021***001', 'CU2021***01')
ORDER BY `e`.`Id` DESC
LIMIT @__p_2 OFFSET @__p_1

SQL不一样,导致数据不一致.

找到的相关解释

.Include() .Count() not using inner join for count · Issue #8201 · dotnet/efcore · GitHub

There are multiple facets of problem here.

Include is EF specific API which tells EF to load navigation property when creating object of mapped entity type. In reference navigation case it creates such a simple join. For collection navigations, it creates multiple queries to support streaming. Include by no means a pointer to tell EF to perform a join. If final projection is not entity type whose navigations are eagerly loaded, Include will be ignored.

EF会根据实际情况进行优化,忽略了include,因为include的是父表, 统计子表数量, 当然不用再考虑父表的数据, 因为理论上父表数据必须存在

但由于我的情况是开发数据库数据比较乱,再上公司要求删除外键

网友的方案,真棒

I found a solution, if:

1. You don’t want to write the join explicitly
2. Your database table has no real foreign keys
3. You want to calculate the count after the inner join

我改造后的EF, 达到目的

var query = _context.Orders.Include(r => r.Config).Include(r => r.Config.Product).Include(r => r.Config.ContractOrder.Contract).Where(r => r.Config.Id > 0 && r.Config.Product.Id > 0 && r.Config.ContractOrder.Id > 0 && r.Config.ContractOrder.Contract.Id > 0);

生成的SQL

SELECT COUNT(*)
FROM `Orders` AS `e`
INNER JOIN `ProductConfigs` AS `r.Config` ON `e`.`ConfigId` = `r.Config`.`Id`
INNER JOIN (
    SELECT `b`.*
    FROM `Products` AS `b`
    WHERE `b`.`IsDelete` = FALSE
) AS `t` ON `r.Config`.`ProductId` = `t`.`Id`
INNER JOIN `ContractOrders` AS `r.Config.ContractOrder` ON `r.Config`.`ContractOrderId` = `r.Config.ContractOrder`.`Id`
INNER JOIN (
    SELECT `e0`.*
    FROM `Contracts` AS `e0`
    WHERE `e0`.`IsDeleted` = FALSE
) AS `t0` ON `r.Config.ContractOrder`.`ContractId` = `t0`.`Id`
WHERE ((`e`.`IsDelete` = FALSE) AND ((((`e`.`ConfigId` > 0) AND (`t`.`Id` > 0)) AND (`r.Config.ContractOrder`.`Id` > 0)) AND (`t0`.`Id` > 0))) AND `e`.`CustomerId` IN ('CU201904180001', 'CU202109240001', 'CU202110150001')

返回的数据

{
  "code": 0,
  "msg": "",
  "count": 1,
  "pageSize": 20,
  "pageIndex": 1,
  "data": [
    {
      "orderId": 1755,
      "orderCode": "",
      "customerId": "CU202110150001",
      "customerName": "ATM公司",
      "peopleId": "440508*****19",
      "peopleName": "We**is",
      "contractCode": "FYU-HC-202206011445",
      "orderStatusStr": "待提交",
      "productName": "Test套餐2",
      "reserveCount": 0
    }
  ]
}