iT邦幫忙

0

Linq 查mssql的幾個問題

各位大大好

小弟在學ASP.Net MVC,使用Entity Framework,遇到了幾個查詢的Linq不知道該怎麼寫。
資料表如下

表1 課程資訊

CourseNo CourseName CourseNote
AA00001 微積分 eng,ct30
AA00002 物理 01,ct30
AA00003 化學

表2 教室、節次

CourseNo Node CalssRoomNo
AA00001 W1 MA-303
AA00001 W2 MA-301
AA00001 W3 TR-212
AA00002 T1 IB-301
AA00002 T2 IB-301
AA00002 T3 IB-301

表3 課程備註代號

NoteNo NoteContents
ct30 開放外系選修
01 分組教學
eng 英文授課

問題1
假設我以CourseNo搜尋想要得到課程名稱(CourseName)及上課節次(Node)及教室(ClassRoomNo),我目前是寫

var result = from a in ctx.v_courseb
             from b in ctx.Cour_Time_Room
             where a.CourseNo == a.CourseNo
             where b.CourseNo == a.CourseNo
             select new {a.CourseName, b.Node, b.ClassRoomNo};

假設我查詢AA00001他會吐回

{"微積分", "W1", "MA-303"}, {"微積分", "W2", "MA-303"}, {"微積分", "W3", "TR-212"}

可是我想得到的是

{"微積分", "W1, W2, W3", "MA-303, MA-303, TR-212"}

在SQL中好像可以用STRING_AGG()和Group by解決,可是用Linq就不知道該怎麼實現了。

問題2
假設我以CourseNo搜尋想要得到課程名稱(CourseName)及課程備註(CourseNote),可是課程備註放在另一張表內(表3),且CourseNote欄位內的資料以逗號隔開,請問SQL中的STRING_SPLIT()在Linq中要怎麼處理呢,我最後是想得到這樣

{"微積分", "英文授課, 開放外系選修"}

謝謝各位大大的解答<(_ _)>

1 個回答

1
米歐
iT邦新手 5 級 ‧ 2018-11-05 16:42:28
最佳解答

小弟我比較喜歡用 Lambda 的方式來寫 LINQ,所以請見諒囉。/images/emoticon/emoticon06.gif

附上本人的解法,不懂再問吧!

var table1 = new [] {
  new { CourseNo = "AA00001", CourseName = "微積分", CourseNote = "eng,ct30" },
  new { CourseNo = "AA00002", CourseName = "物理", CourseNote = "01,ct30" },
  new { CourseNo = "AA00003", CourseName = "化學", CourseNote = "" }
};
var table2 = new [] {
  new { CourseNo = "AA00001", Node = "W1", ClassRoomNo = "MA-303" },
  new { CourseNo = "AA00001", Node = "W2", ClassRoomNo = "MA-301" },
  new { CourseNo = "AA00001", Node = "W3", ClassRoomNo = "TR-212" },
  new { CourseNo = "AA00002", Node = "T1", ClassRoomNo = "IB-301" },
  new { CourseNo = "AA00002", Node = "T2", ClassRoomNo = "IB-301" },
  new { CourseNo = "AA00002", Node = "T3", ClassRoomNo = "IB-301" }
};
var table3 = new [] {
  new { NoteNo = "ct30", NoteContents = "開放外系選修" },
  new { NoteNo = "01", NoteContents = "分組教學" },
  new { NoteNo = "eng", NoteContents = "英文授課" }
};
var ans1 = table1.GroupJoin (table2, t1 => t1.CourseNo, t2 => t2.CourseNo,
  (t1, t2) => new {
    CourseName = t1.CourseName,
    Node = string.Join (",", t2.Select (x => x.Node)),
    ClassRoomNo = string.Join (",", t2.Select (x => x.ClassRoomNo))
  });
var ans2 = table1.Select (x =>
  new {
    CourseName = x.CourseName,
    CourseNote = string.Join (",",
      table3.Where (y => x.CourseNote.Contains (y.NoteNo)).Select (z => z.NoteContents))
  });
yuchingtw iT邦新手 5 級 ‧ 2018-11-05 18:52:48 檢舉

解決了!!!
謝謝大大<(_ _)>

我要發表回答

立即登入回答