在项目中,使用EF的orm框架,但是EF的查询不支持像sqlsugar一样的写法。例如在查询中不允许出现c#代码的写法,否则会报错,告诉你GroupJoin等报错信息:
无法将表达"Table(Doc_ExpressArrive).GroupJoin(Table(InStore_Order),a=>a.OrderNo,b=>b.OrderNo)…“
这个报错信息我一直以为是多表链接时候的问题报错,后来发现是字段查询用了string.IsNullOrEmpty()的原因。
错误代码:
var result=(from ea in db.Doc_ExpressArrive
join od in db.InStore_Order on ea.CaiGouNo equals od.OrderNo into temp
from od in temp.DefaultIfEmpty()
join cs in db.Doc_Customer on od.CustomerGuid equals cs.ID into temp1
from cs in temp1.DefaultIfEmpty()
join pro in db.Doc_Proprietor on ea.ProprietorGuid equals pro.ID into temp2
from pro in temp2.DefaultIfEmpty()
where (ea.CreateTime >= StartDate && ea.CreateTime <= EndDate)
&& ((string.IsNullOrEmpty(OrderNo)) ? true : ea.CaiGouNo.Contains(OrderNo))
&& ((string.IsNullOrEmpty(ProprietorGuid)) ? true : ea.ProprietorGuid == ProprietorGuid)
&& ((string.IsNullOrEmpty(KuaiDiNo)) ? true : ea.KuaiDiNo == KuaiDiNo)
&& ((string.IsNullOrEmpty(IsDelete)) ? true : ea.IsDelete == (IsDelete == "1" ? true : false))
&& (OrderStateNo == "3" ? true : ea.StateNo == OrderStateNo)
select new Doc_ExpressArriveModel
{
CaiGouOrderNo = od.OrderNo,
CreateTime = ea.CreateTime,
ID = ea.ID,
KuaiDiNo = ea.KuaiDiNo,
CustomerName = cs.CustomerName,
CustomerNo = cs.CustomerNo,
KuaiDiReceiver = ea.KuaiDiReceiver,
Proprietor = pro.ProprietorName,
OperatorName = od.OperatorName,
UpdateTime = string.IsNullOrEmpty(ea.UpdateTime) ? string.Empty : ea.UpdateTime,
StateName = ea.StateNo,
OrderType = od.OrderTypeNo,
AddDateTime = ea.AddDateTime,
KuaiDiReceiveTime = ea.KuaiDiReceiveTime,
ExpressName = ea.ExpressName,
PictureTag = ea.IsPicture ? "已上传" : "未上传",
JianShu = ea.JianShu,
Remark = ea.Remark,
FileExtension = ea.FileExtension,
IsPicture = ea.IsPicture,
IsDelete = ea.IsDelete
}
).ToList();
原因:string.IsNullOrEmpty(ea.UpdateTime)和string.Empty是.net后台代码的写法,在Linq to Sql的时候,镜像没法正确转换,但是三目运算是可以的,因为sql里面就可以用,所以就是sql语句不能用的,在Linq中也不能写
正确代码:
只需要把UpdateTime = string.IsNullOrEmpty(ea.UpdateTime) ? string.Empty : ea.UpdateTime,
改成UpdateTime = ea.UpdateTime==null ? "" : ea.UpdateTime,