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 = https://www.cnblogs.com/Wellian/p/query.OrderByDescending(r => r.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
var page = new PageResult2>
{
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:
- You don’t want to write the
joinexplicitly - Your database table has no real foreign keys
- 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
}
]
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/499987.html
標籤：其他