实体框架中的Where子句未返回正确结果
本文关键字:返回 结果 子句 框架 Where 实体 | 更新日期: 2023-09-27 18:25:25
对于初学者来说,EF这件事似乎不是一条容易的途径:(.我有下面的SQL,它提供了20条记录:
SELECT
Suppliers.SupplierName,
srma.Id,
srma.CreatedOn,
srmaStatuses.StatusName,
srma.Status,
srma.PONumber,
srma.PONumber,
PurchaseOrders.PODate,
PurchaseOrders.suppliersOrderNumber
FROM
SRMAs srma
join srmastatuses on srma.status = srmastatuses.id
JOIN PurchaseOrders on SRMA.PONumber = PurchaseOrders.PONumber
JOIN Suppliers on Suppliers.SupplierID = PurchaseOrders.SupplierID
WHERE
srma.Status in (0,1)
AND(PurchaseOrders.suppliersOrderNumber LIKE '%2002%' OR srma.PONumber = '2002')
我制作了以下LINQ版本,没有返回任何记录:
var searchQuery = collection["query"].Trim();
DBContext_Model db = new DBContext_Model();
int[] ids = new int[] { 0, 1 };
//LINQ way to JOIN tables
var srmas = (
from SRMAs in db.SRMAs
join SRMAStatus in db.SRMAStatus on SRMAs.Id equals SRMAStatus.Id
join PurchaseOrders in db.PurchaseOrders on SRMAs.PONumber equals PurchaseOrders.PONumber
join Suppliers in db.Suppliers on PurchaseOrders.SupplierID equals Suppliers.SupplierID
join SRMADetails in db.SRMADetails on SRMAs.Id equals SRMADetails.SRMAId
where
(
ids.Contains(SRMAs.Status)
&&
(
searchQuery.Contains(PurchaseOrders.suppliersOrderNumber)
||
searchQuery.Contains(SqlFunctions.StringConvert((decimal)SRMAs.PONumber))
)
)
select new
{
SRMAs.Status,SRMAs.Id,
SRMAs.PONumber,
SRMAs.CreatedOn,
Suppliers.SupplierName,
SRMAStatus.StatusName,
PurchaseOrders.PODate,
PurchaseOrders.suppliersOrderNumber
}).ToList();
如果我仍然删除这个部分,它只返回4个记录,尽管它应该返回20个。
&&
(
searchQuery.Contains(PurchaseOrders.suppliersOrderNumber)
||
searchQuery.Contains(SqlFunctions.StringConvert((decimal)SRMAs.PONumber))
)
有可能使用LINQ而不是扰乱我的生活吗?
1。on
子句应该将FK与PK进行比较,当将SRMA
与SRMAStatus
连接时,您正在比较PK与PK。将SRMAs.Id equals SRMAStatus.Id
更改为SRMAs.StatusId equals SRMAStatus.Id
。
2.删除此部分join SRMADetails in db.SRMADetails on SRMAs.Id equals SRMADetails.SRMAId
,因为sql查询没有SRMADetails的联接子句。
3。您当前的搜索查询将生成以下内容:
CAST(CHARINDEX(PurchaseOrders.suppliersOrderNumber, '2002') AS int) > 0
or
CAST(CHARINDEX(cast(SRMAs.PONumber as varchar(max)), '2002') AS int) > 0
应该在哪里:
PurchaseOrders.suppliersOrderNumber.Contains(searchQuery)
||
SqlFunctions.StringConvert((decimal)SRMAs.PONumber).Contains(searchQuery)
这样它就产生了类似于的东西
PurchaseOrders.suppliersOrderNumber LIKE '%2002%'
||
srma.PONumber LIKE '%2002%'