实体框架,为什么我的被忽略的地方?
问题描述:
关于下面的查询,为什么我的条件是ingored?实体框架,为什么我的被忽略的地方?
:
public static List<Sys.Entities.Hms206> Hms206Get(DateTime startDate, DateTime endDate, string courseNumber, bool sOnly) {
//select distinct
// p.DEPTID [Rc],
// p.NAME [EmployeeName],
// p.EMPLID [EmployeeId],
// x.XLATSHORTNAME [Ran]
//from
// tablep p
// inner join tablex x on p.CM_RAN = x.FIELDVALUE
// inner join tablet t on p.EMPLID = t.EMPLID
//where
// p.DEPTID not like '%R'
// and p.EMPL_STATUS in('A','L','P','S','W')
// and t.COURSE_END_DT between '7/1/1960' and '12/27/2012'
// and t.COURSE = 'C00005'
// and t.ATTENDANCE = 'C'
// and x.FIELDNAME = 'CM_RAN'
// and p.CM_IND = 'S'
// --and p.CM_IND in ('S','C') -- all
//order by
// p.DEPTID, p.NAME
string[] stats = new string[]{"A","L","P","S","W"};
using (var context = new Sys.EntityModels.LCEntities()) {
var query = (from p in context.PsCmSummaries
join x in context.TableX on p.CM_RAN equals x.FIELDVALUE
join t in context.TableT on p.EMPLID equals t.EMPLID
where !p.DEPTID.EndsWith("R")
&& stats.Contains(p.EMPL_STATUS)
&& t.COURSE_END_DT >= startDate && t.COURSE_END_DT <= endDate
&& t.COURSE == courseNumber
&& t.ATTENDANCE == "C"
&& x.FIELDNAME == "CM_Ran"
select new {
p.DEPTID,
p.NAME,
p.EMPLID,
x.XLATSHORTNAME,
p.CM_IND
});
// this conditional where is not being applied
if (sOnly) {
query.Where(x => x.CM_IND == "S");
}
else {
query.Where(x => x.CM_IND == "S" || x.CM_IND == "C");
}
return query.Distinct().OrderBy(x => x.DEPTID).ThenBy(x => x.NAME)
.Select(
x => new Sys.Entities.Hms206 {
Rc = x.DEPTID, EmployeeName = x.NAME, EmployeeId = x.EMPLID,
Rank = x.XLATSHORTNAME, S_Indicator = x.CM_IND
})
.ToList();
}
}
答
你打电话Where
但完全无视返回值 - 这使得调用无用(注注释掉sql语句是什么,我试图在这个LINQ查询复制)。你想要的东西,如:
if (sOnly) {
query = query.Where(x => x.CM_IND == "S");
}
else {
query = query.Where(x => x.CM_IND == "S" || x.CM_IND == "C");
}
LINQ调用,如Where
,Select
等不修改现有查询 - 它们通过合成新方面前面的查询返回新查询。
答
你可以试试这个来代替你的conditon:
if (sOnly) {
query.Where(x => x.CM_IND == "S");
}
else {
query.Where(x => x.CM_IND == "S" || x.CM_IND == "C");
}
有了答案下面:
query = sOnly ? query.Where(x => x.CM_IND == "S")
: query.Where(x => x.CM_IND == "S" || x.CM_IND == "C");
+0
谢谢你对使用三元运算符的建议。 – 2013-04-25 06:00:26
+0
没问题,我刚刚用@Jon Skeet的回答来提示它 – lexeRoy 2013-04-25 06:17:49
笑,我笨!谢谢! – 2013-04-25 06:00:44