iT邦幫忙

0

想請問,如何把這兩個LIST合併在一起

 public List<DrPatDto> FindOpdDrPat(string cid, string Pat_Guid, DateTime Opdate)
        {
           
            var query = (from x in _repo.PAT_SCHEDULE
                         join b in _repo.PATIENT
                         on x.PAT_GUID equals b.GUID
                         where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.PAT_SOURCE == "OPD" && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
                         select new DrPatDto
                         {

                             PAT_SOURCE = x.PAT_SOURCE,
                             BedNO = x.BED_NO,
                             PAT_NAME = b.PAT_NAME,
                             PAT_NO = b.PAT_NO,
                             OPDDate = x.DIA_DATE,
                         }).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();
                 
            var query2 = (from x in _repo.PAT_SCHEDULE
                         join c in _repo.HD_REQUIRE
                         on x.PAT_GUID equals c.PAT_GUID
                         where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
                         select new DrPatDto
                         {
                             CID = x.CID,
                             PAT_GUID = x.PAT_GUID,
                             IPD_PAT_SOURCE = c.PAT_SOURCE,
                             IpdBedNo = c.IPD_BED_NO,
                             OPDDate = x.DIA_DATE,
                         }).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();
            foreach (DrPatDto dto2 in query2)
            {
                dto2.PAT_NAME = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NAME;
                dto2.PAT_NO = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NO;
            }
}

這是我的兩段查詢

第一段執行的結果如下圖
https://ithelp.ithome.com.tw/upload/images/20190131/20110132sPbULSRh2C.jpg

第二段
https://ithelp.ithome.com.tw/upload/images/20190131/201101321e61kqnNZl.jpg

我想請問這兩段語法我該怎麼合併才能讓我這結果能一起顯示出來?

1 個回答

2
Homura
iT邦高手 1 級 ‧ 2019-01-31 11:32:00
最佳解答

SQL可以使用UNION合併
LINQ一樣也有
參考這篇
然後你在合併前不要直接ToList()
要UNION完再用

var query = (from x in _repo.PAT_SCHEDULE
             join b in _repo.PATIENT
             on x.PAT_GUID equals b.GUID
             where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.PAT_SOURCE == "OPD" && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
 select new DrPatDto
 {

     PAT_SOURCE = x.PAT_SOURCE,
     BedNO = x.BED_NO,
     PAT_NAME = b.PAT_NAME,
     PAT_NO = b.PAT_NO,
     OPDDate = x.DIA_DATE,
 });
                 
var query2 = (from x in _repo.PAT_SCHEDULE
                         join c in _repo.HD_REQUIRE
                         on x.PAT_GUID equals c.PAT_GUID
                         where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
 select new DrPatDto
 {
     CID = x.CID,
     PAT_GUID = x.PAT_GUID,
     IPD_PAT_SOURCE = c.PAT_SOURCE,
     IpdBedNo = c.IPD_BED_NO,
     OPDDate = x.DIA_DATE,
 });
                         
var unionResult = query1.Union(query2).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();
看更多先前的回應...收起先前的回應...
tenno081 iT邦新手 4 級 ‧ 2019-01-31 11:52:57 檢舉

你好,我有想過用union可是最後卻失敗,然後用您的方法有出現錯誤
The type 'Tmht.HD.Patients.Models.DrPatDto' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

這是我目前的程式碼

 public List<DrPatDto> FindOpdDrPat(string cid, string Pat_Guid, DateTime Opdate)
        {

            var query = (from x in _repo.PAT_SCHEDULE
                         join b in _repo.PATIENT
                         on x.PAT_GUID equals b.GUID
                         where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.PAT_SOURCE == "OPD" && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
                         select new DrPatDto
                         {

                             PAT_SOURCE = x.PAT_SOURCE,
                             BedNO = x.BED_NO,
                             PAT_NAME = b.PAT_NAME,
                             PAT_NO = b.PAT_NO,
                             OPDDate = x.DIA_DATE,
                         }).OrderByDescending(x => x.OPDDate);

            var query2 = (from x in _repo.PAT_SCHEDULE
                          join c in _repo.HD_REQUIRE
                          on x.PAT_GUID equals c.PAT_GUID
                          where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
                          select new DrPatDto
                          {                              
                             
                              IPD_PAT_SOURCE = c.PAT_SOURCE,
                              IpdBedNo = c.IPD_BED_NO,
                              OPDDate = x.DIA_DATE,
                          }).OrderByDescending(x => x.OPDDate);
            foreach (DrPatDto dto2 in query2)
            {
                dto2.PAT_NAME = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NAME;
                dto2.PAT_NO = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NO;
            }                                 
            var unionResult = query.Union(query2).ToList<DrPatDto>();
            return unionResult;
           
        }

請問這問題可能是哪一種?

Homura iT邦高手 1 級 ‧ 2019-01-31 11:57:06 檢舉

tenno081
你要先搞懂ToList()這個方法
他是把資料拉出來的動作
所以你在用之前資料並沒被查詢出來
所以你的foreah不能使用....
要把合併的結果拉到foreach之前
然後改成讀取UNION的結果

var unionResult = query.Union(query2).ToList<DrPatDto>();
            
foreach (DrPatDto dto2 in unionResult)
{
    dto2.PAT_NAME = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NAME;

    dto2.PAT_NO = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NO;
}                                 

tenno081 iT邦新手 4 級 ‧ 2019-01-31 12:05:29 檢舉

不好意思,是我的疏忽,但是我就算沒用foreach迴圈他還是有一樣的錯誤,我照著您的方法再修改後結果還是不變@@

Homura iT邦高手 1 級 ‧ 2019-01-31 13:08:52 檢舉

tenno081

but only if the same properties are set in both places and those properties are set in the same order.

好像說做了一樣排序
你把ORDER拉下來試試
先Union在ORDER看看

tenno081 iT邦新手 4 級 ‧ 2019-01-31 13:31:32 檢舉

結果還是不變,不好意思,我可能打算先這樣做

 var query3 = (from a in query
                          join b in query2
                          on a.PAT_GUID
                          equals b.PAT_GUID                         
                          select new DrPatDto
                          {
                              PAT_SOURCE = a.PAT_SOURCE,
                              IPD_PAT_SOURCE = b.IPD_PAT_SOURCE,
                              BedNO = a.BedNO,
                              IpdBedNo = b.IpdBedNo,
                              PAT_NAME = a.PAT_NAME,

                          }).ToList<DrPatDto>();
            return query3;

雖然沒查到,但想請問這個做法有用嗎?

tenno081 iT邦新手 4 級 ‧ 2019-01-31 13:45:44 檢舉

阿,真是感謝,我後來是這樣做

List<DrPatDto> dto = query.Union(query2).ToList<DrPatDto>();

結果這樣就可以了@@

Homura iT邦高手 1 級 ‧ 2019-01-31 13:50:05 檢舉

tenno081
你中間有改什麼嗎?
還是只是宣告變了而已@@

tenno081 iT邦新手 4 級 ‧ 2019-01-31 14:16:13 檢舉
 public List<DrPatDto> FindOpdDrPat(string cid, string Pat_Guid, DateTime Opdate)
        {

            var query = (from x in _repo.PAT_SCHEDULE
                         join b in _repo.PATIENT
                         on x.PAT_GUID equals b.GUID
                         where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.PAT_SOURCE == "OPD" && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
                         select new DrPatDto
                         {
                             PAT_SOURCE = x.PAT_SOURCE,
                             BedNO = x.BED_NO,
                             PAT_NAME = b.PAT_NAME,
                             PAT_NO = b.PAT_NO,
                             OPDDate = x.DIA_DATE,
                         }).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();

            var query2 = (from x in _repo.PAT_SCHEDULE
                          join c in _repo.HD_REQUIRE
                          on x.PAT_GUID equals c.PAT_GUID
                          where (x.CID == cid && x.PAT_GUID == Pat_Guid && x.DIA_DATE == Opdate || x.DIA_DATE == new DateTime(1900, 1, 1))
                          select new DrPatDto
                          {
                              CID = x.CID,
                              PAT_GUID = x.PAT_GUID,
                              IPD_PAT_SOURCE = c.PAT_SOURCE,
                              IpdBedNo = c.IPD_BED_NO,
                              OPDDate = x.DIA_DATE,
                          }).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();
            foreach (DrPatDto dto2 in query2)
            {
                dto2.PAT_NAME = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NAME;
                dto2.PAT_NO = _repo.PATIENT.Where(x => x.CID == dto2.CID && x.GUID == dto2.PAT_GUID).FirstOrDefault<PATIENT>().PAT_NO;
            }
            List<DrPatDto> dto = query.Union(query2).Distinct().ToList<DrPatDto>();         
            return dto;

        } 
    }

這樣做,還是先每一個查詢都tolist出來,然後加入 我剛剛PO上的那行

Homura iT邦高手 1 級 ‧ 2019-01-31 14:20:15 檢舉

tenno081
恩恩~OK就行

我要發表回答

立即登入回答