EF中 ToList(); Count(); 数据条数不一致

第一个太长

Opened connection at 1/10/2019 9:41:18 AM +08:00
SELECT
`Project1`.`AtmStatusId`,
`Project1`.`BarCode1` AS `BarCode`,
`Project1`.`AtmNo`,
`Project1`.`ATMName`,
`Project1`.`Name`,
`Project1`.`Name1`,
`Project1`.`C1`,
`Project1`.`C2`,
`Project1`.`C3`,
`Project1`.`C4`,
`Project1`.`Enabled`,
`Project1`.`C5`,
`Project1`.`StartMDate`,
`Project1`.`Address`,
`Project1`.`C6`,
`Project1`.`C7`,
`Project1`.`BarCode` AS `BarCode1`,
`Project1`.`AtmSetupType`,
`Project1`.`UpdateBoxCount`,
`Project1`.`ProjectId`,
`Project1`.`TerminalNum`,
`Project1`.`ProducerName`,
`Project1`.`RevertPosition`,
`Project1`.`BillAddress`,
`Project1`.`C8`,
`Project1`.`C9`,
`Project1`.`C10`,
`Project1`.`C11`,
`Project1`.`C12`,
`Project1`.`C13`,
`Project1`.`C14`,
`Project1`.`C15`,
`Project1`.`C16`,
`Project1`.`C17`,
`Project1`.`C18`,
`Project1`.`C19`,
`Project1`.`C20`,
`Project1`.`C21`,
`Project1`.`C22`,
`Project1`.`C23`,
`Project1`.`C24`,
`Project1`.`C25`,
`Project1`.`C26`,
`Project1`.`PasswordLimit`,
`Project1`.`C27`,
`Project1`.`Remark`,
`Project1`.`Id`,
`Project1`.`C28`,
`Project1`.`GPScoordinate`
FROM (SELECT
`Extent1`.`Id`,
`Extent1`.`AtmNo`,
`Extent1`.`TerminalNum`,
`Extent1`.`Address`,
`Extent1`.`BillAddress`,
`Extent1`.`GPScoordinate`,
`Extent1`.`RevertPosition`,
`Extent1`.`AtmStatusId`,
`Extent1`.`StartMDate`,
`Extent1`.`Enabled`,
`Extent1`.`AtmSetupType`,
`Extent1`.`BarCode`,
`Extent1`.`UpdateBoxCount`,
`Extent1`.`PasswordLimit`,
`Extent1`.`ATMName`,
`Extent1`.`Remark`,
`Extent1`.`UpdatedDate`,
`Extent1`.`ProjectId`,
`Extent2`.`BarCode` AS `BarCode1`,
`Extent3`.`Name`,
`Extent4`.`Name` AS `Name1`,
`Extent8`.`ProducerName`,
CASE WHEN (`Extent5`.`Id` IS NULL) THEN (NULL) ELSE (`Extent5`.`BlockName`) END AS `C1`,
CASE WHEN (`Extent5`.`Id` IS NULL) THEN (NULL) ELSE (`Extent6`.`RegionName`) END AS `C2`,
CASE WHEN (`Extent7`.`Id` IS NULL) THEN (NULL) ELSE (`Extent7`.`StreetName`) END AS `C3`,
CASE WHEN (1 = `Extent1`.`Enabled`) THEN (CASE WHEN (0 = (`Extent1`.`AtmStatusId`)) THEN (@p__linq__0) WHEN (1 = (`Extent1`.`AtmStatusId`)) THEN (@p__linq__1) WHEN (2 = (`Extent1`.`AtmStatusId`)) THEN (@p__linq__2) WHEN (3 = (`Extent1`.`AtmStatusId`)) THEN (@p__linq__3) ELSE (@gp3) END) ELSE (@p__linq__4) END AS `C4`,
CASE WHEN (2 = (`Extent1`.`JobType`)) THEN (@p__linq__5) WHEN (1 = (`Extent1`.`JobType`)) THEN (@p__linq__6) WHEN (3 = (`Extent1`.`JobType`)) THEN (@p__linq__7) WHEN (4 = (`Extent1`.`JobType`)) THEN (@p__linq__8) WHEN (5 = (`Extent1`.`JobType`)) THEN (@p__linq__9) ELSE (@gp4) END AS `C5`,
CASE WHEN (1 = `Extent1`.`IsAt`) THEN (@p__linq__10) ELSE (@p__linq__11) END AS `C6`,
`Extent1`.`EndMDate` AS `C7`,
CASE WHEN (`Extent9`.`Id` IS NULL) THEN (@gp5) ELSE (`Extent9`.`GroupName`) END AS `C8`,
CASE WHEN (`Extent10`.`Id` IS NULL) THEN (@gp6) ELSE (`Extent10`.`GroupName`) END AS `C9`,
CASE WHEN (`Extent11`.`Id` IS NULL) THEN (@gp7) ELSE (`Extent11`.`GroupName`) END AS `C10`,
CASE WHEN (`Extent12`.`Id` IS NULL) THEN (@gp8) ELSE (`Extent12`.`GroupName`) END AS `C11`,
CASE WHEN (`Extent13`.`Id` IS NULL) THEN (@gp9) ELSE (`Extent13`.`BoxTypeName`) END AS `C12`,
CASE WHEN (`Extent14`.`Id` IS NULL) THEN (@gp10) ELSE (`Extent14`.`BoxTypeName`) END AS `C13`,
CASE WHEN (`Extent15`.`Id` IS NULL) THEN (@gp11) ELSE (`Extent15`.`BoxTypeName`) END AS `C14`,
CASE WHEN (`Extent16`.`Id` IS NULL) THEN (@gp12) ELSE (`Extent16`.`CurrencyItemName`) END AS `C15`,
CASE WHEN (`Extent17`.`Id` IS NULL) THEN (@gp13) ELSE (`Extent17`.`BoxTypeName`) END AS `C16`,
CASE WHEN (`Extent18`.`Id` IS NULL) THEN (@gp14) ELSE (`Extent18`.`CurrencyItemName`) END AS `C17`,
CASE WHEN (`Extent19`.`Id` IS NULL) THEN (@gp15) ELSE (`Extent19`.`BoxTypeName`) END AS `C18`,
CASE WHEN (`Extent20`.`Id` IS NULL) THEN (@gp16) ELSE (`Extent20`.`CurrencyItemName`) END AS `C19`,
CASE WHEN (`Extent21`.`Id` IS NULL) THEN (@gp17) ELSE (`Extent21`.`BoxTypeName`) END AS `C20`,
CASE WHEN (`Extent22`.`Id` IS NULL) THEN (@gp18) ELSE (`Extent22`.`CurrencyItemName`) END AS `C21`,
CASE WHEN (`Extent23`.`Id` IS NULL) THEN (@gp19) ELSE (`Extent23`.`BoxTypeName`) END AS `C22`,
CASE WHEN (`Extent24`.`Id` IS NULL) THEN (@gp20) ELSE (`Extent24`.`BarCode`) END AS `C23`,
CASE WHEN (`Extent25`.`Id` IS NULL) THEN (@gp21) ELSE (`Extent25`.`BarCode`) END AS `C24`,
CASE WHEN (`Extent26`.`Id` IS NULL) THEN (@gp22) ELSE (`Extent26`.`BarCode`) END AS `C25`,
CASE WHEN (1 = `Extent1`.`IsNon`) THEN (@p__linq__12) ELSE (@p__linq__13) END AS `C26`,
`Extent1`.`CreatedDate` AS `C27`,
CASE WHEN (`Extent1`.`gpsconfirm` = 1) THEN (@gp23) ELSE (@gp24) END AS `C28`
FROM `T_ATM_ATM` AS `Extent1` INNER JOIN `T_ATM_BARCODE` AS `Extent2` ON `Extent1`.`AtmBarCodeId` = `Extent2`.`Id` INNER JOIN `T_ATM_ATMTYPE` AS `Extent3` ON `Extent1`.`AtmTypeId` = `Extent3`.`Id` INNER JOIN `T_ATM_CUSTOMER` AS `Extent4` ON `Extent1`.`CustomerId` = `Extent4`.`Id` LEFT OUTER JOIN `T_BLOCKMANAGEMENT` AS `Extent5` ON `Extent1`.`BlockManagementId` = `Extent5`.`Id` LEFT OUTER JOIN `T_CITYREGION` AS `Extent6` ON `Extent5`.`CityRegionId` = `Extent6`.`Id` LEFT OUTER JOIN `T_STREETMANAGEMENT` AS `Extent7` ON `Extent1`.`StreetManagementId` = `Extent7`.`Id` INNER JOIN `T_ATM_PRODUCER` AS `Extent8` ON `Extent1`.`ManufacturerID` = `Extent8`.`Id` LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent9` ON `Extent1`.`province` = `Extent9`.`Id` LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent10` ON `Extent1`.`District` = `Extent10`.`Id` LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent11` ON `Extent1`.`AreaId` = `Extent11`.`Id` LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent12` ON `Extent1`.`GroupId` = `Extent12`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent13` ON `Extent1`.`dpsbin` = `Extent13`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent14` ON `Extent1`.`PurBin` = `Extent14`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent15` ON `Extent1`.`RetractBin` = `Extent15`.`Id` LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent16` ON `Extent1`.`CoinItem1` = `Extent16`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent17` ON `Extent1`.`CoinTrip1` = `Extent17`.`Id` LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent18` ON `Extent1`.`CoinItem2` = `Extent18`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent19` ON `Extent1`.`CoinTrip2` = `Extent19`.`Id` LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent20` ON `Extent1`.`CoinItem3` = `Extent20`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent21` ON `Extent1`.`CoinTrip3` = `Extent21`.`Id` LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent22` ON `Extent1`.`CoinItem4` = `Extent22`.`Id` LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent23` ON `Extent1`.`CoinTrip4` = `Extent23`.`Id` LEFT OUTER JOIN `T_ATM_BARCODE` AS `Extent24` ON `Extent1`.`ATMkeyCodeID` = `Extent24`.`Id` LEFT OUTER JOIN `T_ATM_BARCODE` AS `Extent25` ON `Extent1`.`ATMSPKeyCodeID` = `Extent25`.`Id` LEFT OUTER JOIN `T_ATM_BARCODE` AS `Extent26` ON `Extent1`.`ATMKeyCardCodeID` = `Extent26`.`Id`
WHERE ((0 = `Extent1`.`Deleted`) AND (@gp1 = `Extent1`.`EnterpriseId`)) AND (@gp2 = `Extent1`.`ProjectId`)) AS `Project1`
ORDER BY
`Project1`.`UpdatedDate` DESC
-- p__linq__0: 'Enable' (Type = String, Size = 6)
-- p__linq__1: 'Disable' (Type = String, Size = 7)
-- p__linq__2: 'Shutdown' (Type = String, Size = 8)
-- p__linq__3: 'Null' (Type = String, Size = 4)
-- p__linq__4: 'Shutdown' (Type = String, Size = 8)
-- p__linq__5: 'ATM' (Type = String, Size = 3)
-- p__linq__6: 'CDM' (Type = String, Size = 3)
-- p__linq__7: 'RATM' (Type = String, Size = 4)
-- p__linq__8: '3INI' (Type = String, Size = 4)
-- p__linq__9: 'Other ATMs' (Type = String, Size = 10)
-- p__linq__10: 'Yes' (Type = String, Size = 3)
-- p__linq__11: 'NO' (Type = String, Size = 2)
-- p__linq__12: 'Yes' (Type = String, Size = 3)
-- p__linq__13: 'NO' (Type = String, Size = 2)
-- @gp1: '8477a11e-0916-4a22-870e-2dba082e1e48' (Type = Guid, IsNullable = false)
-- @gp2: '0024fbc8-072e-42f8-af5c-82da23ec8f07' (Type = Guid, IsNullable = false)
-- @gp3: '' (Type = String, IsNullable = false)
-- @gp4: '' (Type = String, IsNullable = false)
-- @gp5: '' (Type = String, IsNullable = false)
-- @gp6: '' (Type = String, IsNullable = false)
-- @gp7: '' (Type = String, IsNullable = false)
-- @gp8: '' (Type = String, IsNullable = false)
-- @gp9: '' (Type = String, IsNullable = false)
-- @gp10: '' (Type = String, IsNullable = false)
-- @gp11: '' (Type = String, IsNullable = false)
-- @gp12: '' (Type = String, IsNullable = false)
-- @gp13: '' (Type = String, IsNullable = false)
-- @gp14: '' (Type = String, IsNullable = false)
-- @gp15: '' (Type = String, IsNullable = false)
-- @gp16: '' (Type = String, IsNullable = false)
-- @gp17: '' (Type = String, IsNullable = false)
-- @gp18: '' (Type = String, IsNullable = false)
-- @gp19: '' (Type = String, IsNullable = false)
-- @gp20: '' (Type = String, IsNullable = false)
-- @gp21: '' (Type = String, IsNullable = false)
-- @gp22: '' (Type = String, IsNullable = false)
-- @gp23: 'true' (Type = String, IsNullable = false, Size = 4)
-- @gp24: 'false' (Type = String, IsNullable = false, Size = 5)
-- Executing at 1/10/2019 9:41:18 AM +08:00
-- Completed in 472 ms with result: EFMySqlDataReader

Closed connection at 1/10/2019 9:41:23 AM +08:00

where 前面的不替换也行只是全当null处理

-- Opened connection at 1/10/2019 9:41:18 AM +08:00
SELECT
`Project1`.`AtmStatusId`,
`Project1`.`BarCode1` AS `BarCode`,
`Project1`.`AtmNo`,
`Project1`.`ATMName`,
`Project1`.`Name`,
`Project1`.`Name1`,
`Project1`.`C1`,
`Project1`.`C2`,
`Project1`.`C3`,
`Project1`.`C4`,
`Project1`.`Enabled`,
`Project1`.`C5`,
`Project1`.`StartMDate`,
`Project1`.`Address`,
`Project1`.`C6`,
`Project1`.`C7`,
`Project1`.`BarCode` AS `BarCode1`,
`Project1`.`AtmSetupType`,
`Project1`.`UpdateBoxCount`,
`Project1`.`ProjectId`,
`Project1`.`TerminalNum`,
`Project1`.`ProducerName`,
`Project1`.`RevertPosition`,
`Project1`.`BillAddress`,
`Project1`.`C8`,
`Project1`.`C9`,
`Project1`.`C10`,
`Project1`.`C11`,
`Project1`.`C12`,
`Project1`.`C13`,
`Project1`.`C14`,
`Project1`.`C15`,
`Project1`.`C16`,
`Project1`.`C17`,
`Project1`.`C18`,
`Project1`.`C19`,
`Project1`.`C20`,
`Project1`.`C21`,
`Project1`.`C22`,
`Project1`.`C23`,
`Project1`.`C24`,
`Project1`.`C25`,
`Project1`.`C26`,
`Project1`.`PasswordLimit`,
`Project1`.`C27`,
`Project1`.`Remark`,
`Project1`.`Id`,
`Project1`.`C28`,
`Project1`.`GPScoordinate`
FROM
(SELECT
`Extent1`.`Id`,
`Extent1`.`AtmNo`,
`Extent1`.`TerminalNum`,
`Extent1`.`Address`,
`Extent1`.`BillAddress`,
`Extent1`.`GPScoordinate`,
`Extent1`.`RevertPosition`,
`Extent1`.`AtmStatusId`,
`Extent1`.`StartMDate`,
`Extent1`.`Enabled`,
`Extent1`.`AtmSetupType`,
`Extent1`.`BarCode`,
`Extent1`.`UpdateBoxCount`,
`Extent1`.`PasswordLimit`,
`Extent1`.`ATMName`,
`Extent1`.`Remark`,
`Extent1`.`UpdatedDate`,
`Extent1`.`ProjectId`,
`Extent2`.`BarCode` AS `BarCode1`,
`Extent3`.`Name`,
`Extent4`.`Name` AS `Name1`,
`Extent8`.`ProducerName`,
CASE
WHEN (`Extent5`.`Id` IS NULL)
THEN (NULL)
ELSE (`Extent5`.`BlockName`)
END AS `C1`,
CASE
WHEN (`Extent5`.`Id` IS NULL)
THEN (NULL)
ELSE (`Extent6`.`RegionName`)
END AS `C2`,
CASE
WHEN (`Extent7`.`Id` IS NULL)
THEN (NULL)
ELSE (`Extent7`.`StreetName`)
END AS `C3`,
CASE
WHEN (1 = `Extent1`.`Enabled`)
THEN (
CASE
WHEN (0 = (`Extent1`.`AtmStatusId`))
THEN (@p__linq__0)
WHEN (1 = (`Extent1`.`AtmStatusId`))
THEN (@p__linq__1)
WHEN (2 = (`Extent1`.`AtmStatusId`))
THEN (@p__linq__2)
WHEN (3 = (`Extent1`.`AtmStatusId`))
THEN (@p__linq__3)
ELSE (@gp3)
END
)
ELSE (@p__linq__4)
END AS `C4`,
CASE
WHEN (2 = (`Extent1`.`JobType`))
THEN (@p__linq__5)
WHEN (1 = (`Extent1`.`JobType`))
THEN (@p__linq__6)
WHEN (3 = (`Extent1`.`JobType`))
THEN (@p__linq__7)
WHEN (4 = (`Extent1`.`JobType`))
THEN (@p__linq__8)
WHEN (5 = (`Extent1`.`JobType`))
THEN (@p__linq__9)
ELSE (@gp4)
END AS `C5`,
CASE
WHEN (1 = `Extent1`.`IsAt`)
THEN (@p__linq__10)
ELSE (@p__linq__11)
END AS `C6`,
`Extent1`.`EndMDate` AS `C7`,
CASE
WHEN (`Extent9`.`Id` IS NULL)
THEN (@gp5)
ELSE (`Extent9`.`GroupName`)
END AS `C8`,
CASE
WHEN (`Extent10`.`Id` IS NULL)
THEN (@gp6)
ELSE (`Extent10`.`GroupName`)
END AS `C9`,
CASE
WHEN (`Extent11`.`Id` IS NULL)
THEN (@gp7)
ELSE (`Extent11`.`GroupName`)
END AS `C10`,
CASE
WHEN (`Extent12`.`Id` IS NULL)
THEN (@gp8)
ELSE (`Extent12`.`GroupName`)
END AS `C11`,
CASE
WHEN (`Extent13`.`Id` IS NULL)
THEN (@gp9)
ELSE (`Extent13`.`BoxTypeName`)
END AS `C12`,
CASE
WHEN (`Extent14`.`Id` IS NULL)
THEN (@gp10)
ELSE (`Extent14`.`BoxTypeName`)
END AS `C13`,
CASE
WHEN (`Extent15`.`Id` IS NULL)
THEN (@gp11)
ELSE (`Extent15`.`BoxTypeName`)
END AS `C14`,
CASE
WHEN (`Extent16`.`Id` IS NULL)
THEN (@gp12)
ELSE (`Extent16`.`CurrencyItemName`)
END AS `C15`,
CASE
WHEN (`Extent17`.`Id` IS NULL)
THEN (@gp13)
ELSE (`Extent17`.`BoxTypeName`)
END AS `C16`,
CASE
WHEN (`Extent18`.`Id` IS NULL)
THEN (@gp14)
ELSE (`Extent18`.`CurrencyItemName`)
END AS `C17`,
CASE
WHEN (`Extent19`.`Id` IS NULL)
THEN (@gp15)
ELSE (`Extent19`.`BoxTypeName`)
END AS `C18`,
CASE
WHEN (`Extent20`.`Id` IS NULL)
THEN (@gp16)
ELSE (`Extent20`.`CurrencyItemName`)
END AS `C19`,
CASE
WHEN (`Extent21`.`Id` IS NULL)
THEN (@gp17)
ELSE (`Extent21`.`BoxTypeName`)
END AS `C20`,
CASE
WHEN (`Extent22`.`Id` IS NULL)
THEN (@gp18)
ELSE (`Extent22`.`CurrencyItemName`)
END AS `C21`,
CASE
WHEN (`Extent23`.`Id` IS NULL)
THEN (@gp19)
ELSE (`Extent23`.`BoxTypeName`)
END AS `C22`,
CASE
WHEN (`Extent24`.`Id` IS NULL)
THEN (@gp20)
ELSE (`Extent24`.`BarCode`)
END AS `C23`,
CASE
WHEN (`Extent25`.`Id` IS NULL)
THEN (@gp21)
ELSE (`Extent25`.`BarCode`)
END AS `C24`,
CASE
WHEN (`Extent26`.`Id` IS NULL)
THEN (@gp22)
ELSE (`Extent26`.`BarCode`)
END AS `C25`,
CASE
WHEN (1 = `Extent1`.`IsNon`)
THEN (@p__linq__12)
ELSE (@p__linq__13)
END AS `C26`,
`Extent1`.`CreatedDate` AS `C27`,
CASE
WHEN (`Extent1`.`gpsconfirm` = 1)
THEN (@gp23)
ELSE (@gp24)
END AS `C28`
FROM
`T_ATM_ATM` AS `Extent1`
INNER JOIN `T_ATM_BARCODE` AS `Extent2`
ON `Extent1`.`AtmBarCodeId` = `Extent2`.`Id`
INNER JOIN `T_ATM_ATMTYPE` AS `Extent3`
ON `Extent1`.`AtmTypeId` = `Extent3`.`Id`
INNER JOIN `T_ATM_CUSTOMER` AS `Extent4`
ON `Extent1`.`CustomerId` = `Extent4`.`Id`
LEFT OUTER JOIN `T_BLOCKMANAGEMENT` AS `Extent5`
ON `Extent1`.`BlockManagementId` = `Extent5`.`Id`
LEFT OUTER JOIN `T_CITYREGION` AS `Extent6`
ON `Extent5`.`CityRegionId` = `Extent6`.`Id`
LEFT OUTER JOIN `T_STREETMANAGEMENT` AS `Extent7`
ON `Extent1`.`StreetManagementId` = `Extent7`.`Id`
INNER JOIN `T_ATM_PRODUCER` AS `Extent8`
ON `Extent1`.`ManufacturerID` = `Extent8`.`Id`
LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent9`
ON `Extent1`.`province` = `Extent9`.`Id`
LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent10`
ON `Extent1`.`District` = `Extent10`.`Id`
LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent11`
ON `Extent1`.`AreaId` = `Extent11`.`Id`
LEFT OUTER JOIN `gf_BaseData_mgr` AS `Extent12`
ON `Extent1`.`GroupId` = `Extent12`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent13`
ON `Extent1`.`dpsbin` = `Extent13`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent14`
ON `Extent1`.`PurBin` = `Extent14`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent15`
ON `Extent1`.`RetractBin` = `Extent15`.`Id`
LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent16`
ON `Extent1`.`CoinItem1` = `Extent16`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent17`
ON `Extent1`.`CoinTrip1` = `Extent17`.`Id`
LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent18`
ON `Extent1`.`CoinItem2` = `Extent18`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent19`
ON `Extent1`.`CoinTrip2` = `Extent19`.`Id`
LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent20`
ON `Extent1`.`CoinItem3` = `Extent20`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent21`
ON `Extent1`.`CoinTrip3` = `Extent21`.`Id`
LEFT OUTER JOIN `T_SYS_CURRENCYITEM` AS `Extent22`
ON `Extent1`.`CoinItem4` = `Extent22`.`Id`
LEFT OUTER JOIN `T_ATM_BOXTYPE` AS `Extent23`
ON `Extent1`.`CoinTrip4` = `Extent23`.`Id`
LEFT OUTER JOIN `T_ATM_BARCODE` AS `Extent24`
ON `Extent1`.`ATMkeyCodeID` = `Extent24`.`Id`
LEFT OUTER JOIN `T_ATM_BARCODE` AS `Extent25`
ON `Extent1`.`ATMSPKeyCodeID` = `Extent25`.`Id`
LEFT OUTER JOIN `T_ATM_BARCODE` AS `Extent26`
ON `Extent1`.`ATMKeyCardCodeID` = `Extent26`.`Id`
WHERE (
(0 = `Extent1`.`Deleted`)
AND ('8477a11e-0916-4a22-870e-2dba082e1e48' = `Extent1`.`EnterpriseId`)
)
AND ('0024fbc8-072e-42f8-af5c-82da23ec8f07' = `Extent1`.`ProjectId`)) AS `Project1`
ORDER BY `Project1`.`UpdatedDate` DESC -- p__linq__0: 'Enable' (Type = String, Size = 6)
-- p__linq__1: 'Disable' (Type = String, Size = 7)
-- p__linq__2: 'Shutdown' (Type = String, Size = 8)
-- p__linq__3: 'Null' (Type = String, Size = 4)
-- p__linq__4: 'Shutdown' (Type = String, Size = 8)
-- p__linq__5: 'ATM' (Type = String, Size = 3)
-- p__linq__6: 'CDM' (Type = String, Size = 3)
-- p__linq__7: 'RATM' (Type = String, Size = 4)
-- p__linq__8: '3INI' (Type = String, Size = 4)
-- p__linq__9: 'Other ATMs' (Type = String, Size = 10)
-- p__linq__10: 'Yes' (Type = String, Size = 3)
-- p__linq__11: 'NO' (Type = String, Size = 2)
-- p__linq__12: 'Yes' (Type = String, Size = 3)
-- p__linq__13: 'NO' (Type = String, Size = 2)
-- @gp1: '8477a11e-0916-4a22-870e-2dba082e1e48' (Type = Guid, IsNullable = false)
-- @gp2: '0024fbc8-072e-42f8-af5c-82da23ec8f07' (Type = Guid, IsNullable = false)
-- @gp3: '' (Type = String, IsNullable = false)
-- @gp4: '' (Type = String, IsNullable = false)
-- @gp5: '' (Type = String, IsNullable = false)
-- @gp6: '' (Type = String, IsNullable = false)
-- @gp7: '' (Type = String, IsNullable = false)
-- @gp8: '' (Type = String, IsNullable = false)
-- @gp9: '' (Type = String, IsNullable = false)
-- @gp10: '' (Type = String, IsNullable = false)
-- @gp11: '' (Type = String, IsNullable = false)
-- @gp12: '' (Type = String, IsNullable = false)
-- @gp13: '' (Type = String, IsNullable = false)
-- @gp14: '' (Type = String, IsNullable = false)
-- @gp15: '' (Type = String, IsNullable = false)
-- @gp16: '' (Type = String, IsNullable = false)
-- @gp17: '' (Type = String, IsNullable = false)
-- @gp18: '' (Type = String, IsNullable = false)
-- @gp19: '' (Type = String, IsNullable = false)
-- @gp20: '' (Type = String, IsNullable = false)
-- @gp21: '' (Type = String, IsNullable = false)
-- @gp22: '' (Type = String, IsNullable = false)
-- @gp23: 'true' (Type = String, IsNullable = false, Size = 4)
-- @gp24: 'false' (Type = String, IsNullable = false, Size = 5)
-- Executing at 1/10/2019 9:41:18 AM +08:00
-- Completed in 472 ms with result: EFMySqlDataReader
-- Closed connection at 1/10/2019 9:41:23 AM +08:00

为什么数据条数不一致

因为生成sql语句不一致

inner join

left join 问题

修改下生成的sql 就正确了

Include也是inner join

http://www.bubuko.com/infodetail-1843473.html

项目中用EF实现外键查询出的数据, 查询数量正确, 但实现返回数据集数量不对

//DbContext.cs
HasRequired(s => s.ClassRoom)
            .WithMany()
            .HasForeignKey(student => student.ClassRoomId);

//查询语句
dbRead.Set<Student>().Include(x=>x.ClassRoom);

查询 .Count()和.ToList()结果数量不一致

经调试后发现生成的Sql语句为 inner join

正确的结果应该是 left join

此时应该如下定义外键

HasOptional(s => s.ClassRoom)
             .WithMany()
             .HasForeignKey(student => student.ClassRoomId);

此时返回的结果就正确了!

var sdsds = (from a in model
join b in _iAtmBarCodeService.GetAllEnts()
on a.AtmBarCodeId equals b.Id into c
from cc in c.DefaultIfEmpty()
select a).ToList();

linq 左链接一下也可以

Include

SELECT
`Extent1`.`ArriveMin`,
`Extent1`.`Id`,
`Extent1`.`AtmNo`,
`Extent1`.`TerminalNum`,
`Extent1`.`AtmBarCodeId`,
`Extent1`.`CustomerId`,
`Extent1`.`ManufacturerID`,
`Extent1`.`AtmTypeId`,
`Extent1`.`BlockManagementId`,
`Extent1`.`StreetManagementId`,
`Extent1`.`OperationTime`,
`Extent1`.`Address`,
`Extent1`.`BillAddress`,
`Extent1`.`GPScoordinate`,
`Extent1`.`RevertPosition`,
`Extent1`.`YXArriveMin`,
`Extent1`.`IsAt`,
`Extent1`.`Pictures`,
`Extent1`.`NodeId`,
`Extent1`.`AtmStatusId`,
`Extent1`.`JobType`,
`Extent1`.`StartMDate`,
`Extent1`.`StartDates`,
`Extent1`.`IsStopCar`,
`Extent1`.`EndMDate`,
`Extent1`.`EndDate`,
`Extent1`.`AlarmMaxMoney`,
`Extent1`.`AlarmMinMoney`,
`Extent1`.`BillTrip1`,
`Extent1`.`CurrencyItemID1`,
`Extent1`.`BillTrip2`,
`Extent1`.`CurrencyItemID2`,
`Extent1`.`BillTrip3`,
`Extent1`.`CurrencyItemID3`,
`Extent1`.`BillTrip4`,
`Extent1`.`CurrencyItemID4`,
`Extent1`.`Enabled`,
`Extent1`.`DisnableCount`,
`Extent1`.`ExternalId`,
`Extent1`.`AtmSetupType`,
`Extent1`.`BarCode`,
`Extent1`.`UpdateBoxCount`,
`Extent1`.`Extension`,
`Extent1`.`ATMkeyCodeID`,
`Extent1`.`ATMSPKeyCodeID`,
`Extent1`.`ATMKeyCardCodeID`,
`Extent1`.`CoinTrip1`,
`Extent1`.`CoinItem1`,
`Extent1`.`CoinTrip2`,
`Extent1`.`CoinItem2`,
`Extent1`.`CoinTrip3`,
`Extent1`.`CoinItem3`,
`Extent1`.`CoinTrip4`,
`Extent1`.`CoinItem4`,
`Extent1`.`PasswordTime`,
`Extent1`.`Password`,
`Extent1`.`NewPassword`,
`Extent1`.`PasswordLimit`,
`Extent1`.`PurBin`,
`Extent1`.`RetractBin`,
`Extent1`.`OperateHour`,
`Extent1`.`dpsbin`,
`Extent1`.`province`,
`Extent1`.`District`,
`Extent1`.`AreaId`,
`Extent1`.`GroupId`,
`Extent1`.`CombinationType`,
`Extent1`.`CombinationChangeBy`,
`Extent1`.`Cencon`,
`Extent1`.`ATMName`,
`Extent1`.`SOI`,
`Extent1`.`Category`,
`Extent1`.`PasswordUpdateUser`,
`Extent1`.`PasswordUpdateTime`,
`Extent1`.`IsNon`,
`Extent1`.`gpsconfirm`,
`Extent1`.`Remark`,
`Extent1`.`CreatedDate`,
`Extent1`.`UpdatedDate`,
`Extent1`.`ProjectId`,
`Extent1`.`EnterpriseId`,
`Extent1`.`UserId`,
`Extent1`.`UpdatedUserId`,
`Extent1`.`Deleted`,
`Extent2`.`Id` AS `Id1`,
`Extent2`.`Name`,
`Extent2`.`Brand`,
`Extent2`.`AtmProducerId`,
`Extent2`.`AtmCustomerId`,
`Extent2`.`BadBoxId`,
`Extent2`.`DepositBoxId`,
`Extent2`.`WithdrawBoxId`,
`Extent2`.`MaintenanceUnit`,
`Extent2`.`MaintenanceContact`,
`Extent2`.`MaintenanceTel`,
`Extent2`.`Remark` AS `Remark1`,
`Extent2`.`CreatedDate` AS `CreatedDate1`,
`Extent2`.`UpdatedDate` AS `UpdatedDate1`,
`Extent2`.`ProjectId` AS `ProjectId1`,
`Extent2`.`EnterpriseId` AS `EnterpriseId1`,
`Extent2`.`UserId` AS `UserId1`,
`Extent2`.`UpdatedUserId` AS `UpdatedUserId1`,
`Extent2`.`Deleted` AS `Deleted1`,
`Extent3`.`Id` AS `Id2`,
`Extent3`.`BarCode` AS `BarCode1`,
`Extent3`.`CodeType`,
`Extent3`.`RFID`,
`Extent3`.`ItemId`,
`Extent3`.`ExternalId` AS `ExternalId1`,
`Extent3`.`FLG`,
`Extent3`.`Remark` AS `Remark2`,
`Extent3`.`CreatedDate` AS `CreatedDate2`,
`Extent3`.`UpdatedDate` AS `UpdatedDate2`,
`Extent3`.`ProjectId` AS `ProjectId2`,
`Extent3`.`EnterpriseId` AS `EnterpriseId2`,
`Extent3`.`UserId` AS `UserId2`,
`Extent3`.`UpdatedUserId` AS `UpdatedUserId2`,
`Extent3`.`Deleted` AS `Deleted2`
FROM
`T_ATM_ATM` AS `Extent1`
INNER JOIN `T_ATM_ATMTYPE` AS `Extent2`
ON `Extent1`.`AtmTypeId` = `Extent2`.`Id`
INNER JOIN `T_ATM_BARCODE` AS `Extent3`
ON `Extent1`.`AtmBarCodeId` = `Extent3`.`Id`
WHERE (
(0 = `Extent1`.`Deleted`)
AND (
'8477a11e-0916-4a22-870e-2dba082e1e48' = `Extent1`.`EnterpriseId`
)
)
AND (
'0024fbc8-072e-42f8-af5c-82da23ec8f07' = `Extent1`.`ProjectId`
)
ORDER BY `Extent1`.`UpdatedDate` DESC -- @gp1: '8477a11e-0916-4a22-870e-2dba082e1e48' (Type = Guid, IsNullable = false)
-- @gp2: '0024fbc8-072e-42f8-af5c-82da23ec8f07' (Type = Guid, IsNullable = false)
-- Executing at 1/10/2019 10:56:51 AM +08:00
-- Completed in 106 ms with result: EFMySqlDataReader

ToList() 和 Count()
不一致生成的sql语句不一致通过where 使用一下某个字段使它生成的sql 一致就行了

posted @ 2019-01-10 09:53 ~雨落忧伤~ 阅读(899) 评论(1) 收藏举报

刷新页面返回顶部

~雨落忧伤~

EF中 ToList(); Count(); 数据条数不一致

公告