public List GetSpRecordMxInfo(int deptid, DateTime startRq, DateTime endRq)
{
List list = new List();
using (var dbContext = new SKJTDBContext())
{
var spUsers = from u in dbContext.Set<User>()
select new
{
userid = u.USERID,
username = u.USERNAME,
usercode = u.USERCODE,
deptid = u.DEPTID,
zwgsid=u.USERUNIT
};
var tempusers = spUsers.Where(u => u.zwgsid == deptid);
//查询只属于指定站务公司下卖出的车票记录，通过班次号关联班次档案信息来判断
var shifts = dbContext.Set<Shift>().Where(s => s.COMPANY_ID == deptid);
if (shifts == null || shifts.Count() == 0) return null;
var spRecords = (from sp in dbContext.Set<Sp_Ticket>().Where(
s => s.SELL_DATE <= endRq && s.SELL_DATE >= startRq).Where(
s => !string.IsNullOrEmpty(s.SHIFTNUMBER))
join shift in shifts
on sp.SHIFTNUMBER equals shift.SHIFTNUMBER
select sp).ToList();
if (spRecords == null || spRecords.Count() == 0) return null;
var nullshiftRecords = dbContext.Set<Sp_Ticket>().Where(
s => s.SELL_DATE <= endRq && s.SELL_DATE >= startRq).Where(
s => string.IsNullOrEmpty(s.SHIFTNUMBER)).ToList();
//单独处理班次号为空的情况
if (nullshiftRecords != null || nullshiftRecords.Count() > 0)
{
foreach (var sp in nullshiftRecords)
{
if (sp.TICKET_SELL_TYPE == TICKET_SELL_TYPE.退票)
{
var tempshift = dbContext.Set<Sp_Ticket>().Where(
r => r.TICKETID == sp.REFUND_TICKET_ID).FirstOrDefault().SHIFTNUMBER;
if (shifts.Any(s => s.SHIFTNUMBER == tempshift))
spRecords.Add(sp);
else continue;
}
else
{
if (tempusers.Any(r => r.userid == sp.USERID))
spRecords.Add(sp);
else continue;
}
}
}
var results = from t in spRecords
join jp in dbContext.Set<Bb_Jp>().Where(w => w.OPER_TIME > startRq)
on t.TICKETID equals jp.TICKETID
into results1
from t_jp in results1.DefaultIfEmpty()
join ld in dbContext.Set<Bb_Waybill>().Where(w => w.OPER_TIME > startRq)
on t_jp == null ? new Bb_Jp().LD_ID : t_jp.LD_ID equals ld.ID
into results2
from t_jp_ld in results2.DefaultIfEmpty()
select new SpTicketRecordMx
{
TicketNO = t.TICKET_NO == null ? string.Empty : t.TICKET_NO,
ShiftNumber = t.SHIFTNUMBER == null ? string.Empty : t.SHIFTNUMBER,
StartRq = t.STARTTIME,
time = t.STARTTIME == null ? string.Empty : ((DateTime)t.STARTTIME).ToString("t"),
TicketState = t_jp == null ? t.TICKET_STATE.ToString() : (t_jp.JP_STATE == JP_STATE.已检 ? (t_jp.LD_ID == null ? "已检未打路单" : "已打路单") :(t.TICKET_STATE==TICKET_STATE.已退? "已退": "退检")),
EndSite = t.ENDSITE == null ? string.Empty : dbContext.Set<Site>().Where(
s => s.SITEID == t.ENDSITE).FirstOrDefault().SITENAME,
PriceJe = t.IN_MONEY,
Out_Money = t.OUT_MONEY,
RefoundTicketID = t.REFUND_TICKET_ID,
Ld_No = t_jp_ld == null ? string.Empty : t_jp_ld.LD_NO,
TicketType = t.TICKET_SELL_TYPE.ToString(),
UserName = spUsers.Where(u => u.userid == t.USERID).FirstOrDefault() == null ? "SPYA" :
spUsers.Where(u => u.userid == t.USERID).FirstOrDefault().username,
OperateRq = (DateTime)t.SELL_DATE
};
// var liettemp = results.ToList();
return results.ToList();
}
}
这是c#写的代码，这代码运行时查询得20多秒，请问各位好心的大婶这段代码怎么优化，小弟感激不敬！新手求各位大婶指点！
问题可能是你用了tolist
tolist会返回所有的记录，这很恐怖。尽量把where groupby join这些放在tolist之前执行。
谢谢一楼的回答，这是我第一次在csdn发帖，不太熟悉额，我貌似只在两个位置用了两个tolist，一处是在代码注释语句“//单独处理班次号为空的情况”上面，另一处是方法最后，因为这是该方法的类型，结合你说的，我还是不大明白
补充一下：不以为方便的组件，性能就一定好，在进行真正的数据库查询前后，linq还要执行很多的解释性代码和转换代码，而这些事会有个触发点，
就是ToList\ToArray....
我个人感觉linq在多表链接查询时速度不是蛮理想，可能是我对linq的理解不够，希望各位大虾大婶指导下，谢谢了
个人觉得你可能是在foreach遍历得时候，造成得查询速度慢