Linq 查mssql的幾個問題

ms sql linq asp.net mvc

yuchingtw 2018-11-04 13:13:33 ‧ 1783 瀏覽

分享至

各位大大好

小弟在學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中要怎麼處理呢，我最後是想得到這樣

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

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

登入發表討論

直播研討會

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

直播中

1 個回答

米歐

iT邦新手 3 級 ‧ 2018-11-05 16:42:28

最佳解答

小弟我比較喜歡用 Lambda 的方式來寫 LINQ，所以請見諒囉。

附上本人的解法，不懂再問吧！

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))
  });