LINQ GROUP BY問題

linq

tenno081 2021-09-09 15:37:41 ‧ 1052 瀏覽

想請問一下我寫了一個方法

public List<MedecineDto> FindHistoryData2(string patGuid, string cid,DateTime StrDate, DateTime EndDate)
        {
            var query = (from x in _repo.MEDECINE
                         join z in _repo.HD_ORDER
                         on x.ORDER_GUID equals z.GUID                        
                         where
                         (patGuid == z.PAT_GUID && x.CID == cid)   
                         group x by new {x.HISTORY_GUID} into a
                         select new MedecineDto
                         {
                             GUID = a.FirstOrDefault().GUID,
                             CID = a.FirstOrDefault().CID,
                             ORDER_GUID = a.FirstOrDefault().ORDER_GUID,   
                             OWN_EXPENCE = a.FirstOrDefault().OWN_EXPENCE,
                             METHOD = a.FirstOrDefault().METHOD,
                             POUR = a.FirstOrDefault().POUR,
                             DELIVER = a.FirstOrDefault().DELIVER,
                             NOTICE = a.FirstOrDefault().NOTICE,
                             HISTORY_GUID = a.FirstOrDefault().HISTORY_GUID,
                             CRE_DATE = a.FirstOrDefault().CRE_DATE,
                             MOD_DATE = a.FirstOrDefault().MOD_DATE,      }).OrderByDescending(x=>x.CRE_DATE).ToList<MedecineDto>();
}

這樣寫第一次載入時會延遲數秒

public List<MedecineDto> FindHistoryData2(string patGuid, string cid,DateTime StrDate, DateTime EndDate)
{
  var query = (from x in _repo.MEDECINE
                         join z in _repo.HD_ORDER
                         on x.ORDER_GUID equals z.GUID                         
                         where
                         (patGuid == z.PAT_GUID && x.CID == cid)                       
                         select new MedecineDto
                         {                   
                             GUID = a.FirstOrDefault().GUID,
                             CID = a.FirstOrDefault().CID,
                             ORDER_GUID = a.FirstOrDefault().ORDER_GUID,   
                             OWN_EXPENCE = a.FirstOrDefault().OWN_EXPENCE,
                             METHOD = a.FirstOrDefault().METHOD,
                             POUR = a.FirstOrDefault().POUR,
                             DELIVER = a.FirstOrDefault().DELIVER,
                             NOTICE = a.FirstOrDefault().NOTICE,
                             HISTORY_GUID = a.FirstOrDefault().HISTORY_GUID,
                             CRE_DATE = a.FirstOrDefault().CRE_DATE,
                             MOD_DATE = a.FirstOrDefault().MOD_DATE, 
                         }).OrderByDescending(x=>x.CRE_DATE).ToList<MedecineDto>();
var Group = query.GroupBy(x => new {x.HISTORY_GUID}).Select(group=>group.FirstOrDefault()).ToList<MedecineDto>();
}

這樣反而沒問題
我能請問是差別在哪嗎?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

bachikevin

iT邦新手 5 級 ‧ 2021-09-10 10:17:16

最佳解答

你的上下兩段程式碼所產生的SQL語法不一樣

上面的程式碼會將group by交給SQL去做
下面的程式碼則是將查詢回來的結果再用LINQ來GROUP

我是這樣看自動產生的SQL語法

            // 將EF產生的SQL語法輸出在Console裡
            db.Database.Log = (log) => Console.WriteLine(log);

            var query1 = db.Stock2020.GroupBy(g => g.StockCode).ToList();

            var query2 = db.Stock2020.ToList().GroupBy(g => g.StockCode);