linq多表连接查询速度很慢，但我在数据库查询分析器中却是秒查，请各位大侠帮我分析下

Question

1. with temp as ( select (case when SHIFTSCHEDULEID is null then (case when TICKET_SELL_TYPE=2 then (select COMPANY_ID from TBL_YD_SHIFT where SHIFTNUMBER= (select SHIFTNUMBER from TBL_SP_TICKET where ticketid=a.REFUND_TICKET_ID)) else (select deptid from tbl_xt_user where userid=a.userid) end) else b.company_id end ) as deptid, a.userid,a.DAY, SELL_DATE,SELL_WAY,a.SHIFTNUMBER,ticketid,ticket_type,TICKET_STATE,TICKET_NO,IN_MONEY,out_money,REFUND_TICKET_ID from tbl_sp_ticket a left join TBL_YD_SHIFT b on a.SHIFTNUMBER=b.SHIFTNUMBER where TICKET_STATE not in(5,6) ) select a.ticket_no,a.shiftnumber,a.DAY,a.STARTTIME,(select username from TBL_XT_USER where userid=a.USERID)as username, (select sitename from TBL_YD_SITE where siteid=a.ENDSITE)as sitename, (case when (a.TICKET_STATE =2 and b.TICKET_NO is null)then '未检' when (a.TICKET_STATE =2 and b.jp_state=0 and c.LD_NO is null)then '已检' when (a.TICKET_STATE =2 and b.jp_state=1 )then '退检' when (a.TICKET_STATE =2 and b.jp_state=0 and c.LD_NO is not null)then '已打路单' when a.TICKET_STATE =3 then '已退' when a.TICKET_STATE =5 then '破损' when a.TICKET_STATE =6 then '已废'end)as TicketState, (case a.TICKET_TYPE when 0 then '全票'when 1 then '半票'when 3 then'后付证'when 4 then '会员票' when 5 then '团体票'when 6 then '包车票'else '其它'end) as TicketType, a.IN_MONEY as priceJe,a.OUT_MONEY, isnull(c.ld_no,'')as Ld_No, a.SELL_DATE ,a.REFUND_TICKET_ID from tbl_sp_ticket a left join TBL_BB_JP b on a.TICKET_NO=b.TICKET_NO left join TBL_BB_WAYBILL c on b.LD_ID=c.ID 这是数据库中的查询代码；

2. 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多秒，请问各位好心的大婶这段代码怎么优化，小弟感激不敬！新手求各位大婶指点！
   

Answer 1

问题可能是你用了tolist
tolist会返回所有的记录，这很恐怖。尽量把where groupby join这些放在tolist之前执行。