想請問我的LINQ語法是否有錯誤

c# asp.net mvc linq

tenno081 2019-01-30 11:18:00 ‧ 1783 瀏覽

分享至

是這樣的我想做一個查詢的功能，如下圖

執行後會顯示資料

LINQ語法

 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_SOURCE == "OPD" && x.PAT_GUID == Pat_Guid && x.DIA_DATE == Opdate)                       
                         select new DrPatDto
                         {                             
                             BedNO = x.BED_NO,
                             PAT_NAME = b.PAT_NAME,
                             PAT_NO = b.PAT_NO,
                             OPDDate = x.DIA_DATE,                                                 
                         }).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();
            return query;
        }

接下來我想再做下面的查詢功能，雖然第二的成功做出來了，但卻也影響到第一個查詢
的結果，最終變下圖

不知可否幫我看一下，我的LINQ語法是否有錯誤?謝謝。

 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                      
                         join c in _repo.HD_REQUIRE
                         on x.PAT_GUID equals c.PAT_GUID
                         where (x.CID == cid && x.PAT_SOURCE == "OPD" && c.PAT_SOURCE == "IPD" && x.PAT_GUID == Pat_Guid && x.DIA_DATE == Opdate)
                         select new DrPatDto
                         {
                             IpdBedNo =c.IPD_BED_NO,
                             BedNO = x.BED_NO,
                             PAT_NAME = b.PAT_NAME,
                             PAT_NO = b.PAT_NO,
                             OPDDate = x.DIA_DATE,                                                 
                         }).OrderByDescending(x => x.OPDDate).ToList<DrPatDto>();
            return query;
        }

優悠 iT邦新手 3 級 ‧ 2019-01-30 15:44:53 檢舉

你可以在TOLIST()前面加 .Distinct()，以去除重複

登入發表討論

直播研討會

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

直播中

1 個回答

jakeuj

iT邦新手 5 級 ‧ 2019-01-31 21:21:38

你兩份不同資料為什麼想放在同一個結果?

public class FindDrPatInput
{
	public string cid { get; set; }
	public string Pat_Guid { get; set; }
	public DateTime Opdate { get; set; }
}

public class FindDrPatAllOutput
{
	public List<DrPatDto> DrPatIPD { get; set; }
	public List<DrPatDto> DrPatOPD { get; set; }
}

public List<DrPatDto> FindDrPatOpd(FindDrPatInput input)
{
	var query = PAT_SCHEDULES.Where(x => 
			x.CID == input.cid 
			&& x.DIA_DATE == input.Opdate 
			&& x.PAT_SOURCE == "OPD")
		.Join(PATIENTS, x => x.PAT_GUID, b => b.GUID, 
			(x, b) => new DrPatDto {
			BedNO = x.BED_NO,
			PAT_NAME = b.PAT_NAME,
			PAT_NO = b.PAT_NO,
			OPDDate = x.DIA_DATE,})
		.OrderByDescending(x => x.OPDDate).ToList();
	return query;
}

public List<DrPatDto> FindDrPatIpd(FindDrPatInput input)
{
	var query = HD_REQUIRES.Where(x => 
			x.CID == input.cid && 
			x.DIA_DATE == input.Opdate && 
			x.PAT_SOURCE == "IPD")
		.Join(PATIENTS, x => x.PAT_GUID, b => b.GUID, 
			(x, b) => new DrPatDto {
			BedNO = x.IPD_BED_NO,
			PAT_NAME = b.PAT_NAME,
			PAT_NO = b.PAT_NO,
			OPDDate = x.DIA_DATE,})
		.OrderByDescending(x => x.OPDDate).ToList();
	return query;
}

public FindDrPatAllOutput FindDrPatAll(FindDrPatInput input)
{
	FindDrPatAllOutput Output = new FindDrPatAllOutput();
	Output.DrPatOPD = FindDrPatOpd(input);
	Output.DrPatIPD = FindDrPatIpd(input);
	return Output;
}

回應 1
分享
檢舉

tenno081 iT邦研究生 4 級 ‧ 2019-02-01 09:43:17 檢舉

你好，這個寫法適用MVC嗎?我的controller目前是長這樣

 public ActionResult DrPatList(DrPatQueryViewModel model)
        {
            if (!String.IsNullOrWhiteSpace(model.OPDDateString))
            {
                model.OPDDate = DateHelper.ToDate(model.OPDDateString);
            }
            PatInfoSession pat = SessionPatInfo();
            DrPatService svc = new DrPatService();
            model.Drpats = svc.FindOpdDrPat(SessionUserInfo().CID, pat.PatGuid, model.OPDDate);
       
             if (model.Drpats == null) model.Drpats = new List<DrPatDto>();
            model.OPDDateString = DateHelper.ToDate(model.OPDDate);
            return View(model);
        }

query語法

public List<DrPatDto> FindOpdDrPat(string cid, string Pat_Guid, DateTime Opdate)
        {
            DateTime sDate = new DateTime(Opdate.Year, Opdate.Month, Opdate.Day, 0, 0, 0);
            DateTime eDate = sDate.AddDays(1);
            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 >= sDate && x.DIA_DATE < eDate)
                         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).Distinct().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 >= sDate && x.DIA_DATE < eDate)
                          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).ToList<DrPatDto>();
            return dto;

        }

你好，這個寫法適用MVC嗎?我的controller目前是長這樣
```
 public ActionResult DrPatList(DrPatQueryViewModel model)
        {
            if (!String.IsNullOrWhiteSpace(model.OPDDateString))
            {
                model.OPDDate = DateHelper.ToDate(model.OPDDateString);
            }
            PatInfoSession pat = SessionPatInfo();
            DrPatService svc = new DrPatService();
            model.Drpats = svc.FindOpdDrPat(SessionUserInfo().CID, pat.PatGuid, model.OPDDate);
       
             if (model.Drpats == null) model.Drpats = new List<DrPatDto>();
            model.OPDDateString = DateHelper.ToDate(model.OPDDate);
            return View(model);
        }          
```

query語法
```
public List<DrPatDto> FindOpdDrPat(string cid, string Pat_Guid, DateTime Opdate)
        {
            DateTime sDate = new DateTime(Opdate.Year, Opdate.Month, Opdate.Day, 0, 0, 0);
            DateTime eDate = sDate.AddDays(1);
            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 >= sDate && x.DIA_DATE < eDate)
                         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).Distinct().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 >= sDate && x.DIA_DATE < eDate)
                          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).ToList<DrPatDto>();
            return dto;

}
```

修改

登入發表回應