iT邦幫忙

0

Linq 的子查詢詢問

想請問這段Sql的子查詢要如何轉成linq語法並跟我這段linq做合併?

select  a.notice_datetime, (select count(*) from pat_schedule b where a.pat_guid=b.pat_guid and a.notice_datetime>b.dia_date) as cnt
from hd_require a
var query = (from x in _repo.HD_REQUIRE
                         where (x.CID == cid && x.NOTICE_DATETIME >= StartDate && x.NOTICE_DATETIME < EndDate)
                         select new HDRequireDto
                         {         
                         GUID =x.GUID,
                         CID =x.CID,
                         NOTICE_DATETIME =x.NOTICE_DATETIME,
                         NOTICE_EMP_ID= x.NOTICE_EMP_ID,
                         PAT_GUID=x.PAT_GUID,
                         PAT_SOURCE=x.PAT_SOURCE,
                         IPD_BED_NO =x.IPD_BED_NO,
                         HD_METHOD=x.HD_METHOD,
                         FREQ=x.FREQ,
                         MEMO=x.MEMO,
                         CRE_DATE=x.CRE_DATE,
                         STATUS=x.STATUS,
                         CRE_UID=x.CRE_UID,
                         MOD_DATE = x.MOD_DATE,
                         MOD_UID=x.MOD_UID  
                         
                         }).OrderByDescending(x=>x.NOTICE_DATETIME).ToList<HDRequireDto>();

我目前合併後是長這樣

 var query = (from x in _repo.HD_REQUIRE
                         from y in _repo.PAT_SCHEDULE where y.PAT_GUID == x.PAT_GUID && x.NOTICE_DATETIME >y.DIA_DATE
                         where (x.CID == cid && x.NOTICE_DATETIME >= StartDate && x.NOTICE_DATETIME < EndDate)
                         select new HDRequireDto
                         {         
                         GUID =x.GUID,
                         CID =x.CID,
                         NOTICE_DATETIME =x.NOTICE_DATETIME ,
                         NOTICE_EMP_ID=x.NOTICE_EMP_ID,
                         PAT_GUID=x.PAT_GUID,
                         PAT_SOURCE=x.PAT_SOURCE,
                         IPD_BED_NO =x.IPD_BED_NO,
                         HD_METHOD=x.HD_METHOD,
                         FREQ=x.FREQ,
                         MEMO=x.MEMO,
                         CRE_DATE=x.CRE_DATE,
                         STATUS=x.STATUS,
                         CRE_UID=x.CRE_UID,
                         MOD_DATE = x.MOD_DATE,
                         MOD_UID=x.MOD_UID                        
                         }).OrderByDescending(x=>x.NOTICE_DATETIME).ToList<HDRequireDto>();

我的table
https://ithelp.ithome.com.tw/upload/images/20181224/20110132WtYagO4QRs.jpg
sql查詢結果
https://ithelp.ithome.com.tw/upload/images/20181224/20110132O1i4petd3B.jpg
這樣的話請問我count該放在哪裡?

fillano iT邦超人 1 級 ‧ 2018-12-24 18:00:39 檢舉
HDRequireDto 加一個count欄位,然後在select new HDRequireDto的時候做子查詢。就是 count = (子查詢).Count(), 這樣。
tenno081 iT邦新手 4 級 ‧ 2018-12-24 18:15:50 檢舉
感謝您,我知道怎麼做了,謝謝。

尚未有邦友回答

立即登入回答