[C#] Linq extension: Group By

※GROUP BY 範例(1)

from a in SM_ISET_PARAM_Ds

where a.SYS_ID=="TM"

group a by new {a.SYS_ID, a.PARAM_ID} into p

select p

※GROUP BY 範例(2) :  使用Linq Extension

///取得目前Reservations裡面所有有訂房紀錄的房號

//Group by RoomId

var _rooms = db.Reservations.GroupBy(x => x.RoomId).

Select(

group => new

{

RoomId = group.Key

}).OrderBy(x => x.RoomId);

※GROUP BY 範例(3) :  使用Linq Extension

///取得某房號某月份的訂房次數

///Select RoomId, COUNT(1) as ResCount

///from db.Reservations

///WHERE SUBSTRING(CheckInOn,1,6)=months[i]

///Group by RoomId

var _roomInfo =

db.Reservations.

Where(x => x.RoomId == _room.RoomId).

Where(y => (

y.CheckInOn.Year == _year && y.CheckInOn.Month==_mon)).

GroupBy( x => x.RoomId).

Select(

group => new

{
RoomId=group.Key,
ResCount = group.Count()

});

PS. 使用關鍵字Key來取得Group By的欄位值

PS. 使用關鍵字Count()來做計數

※GROUP BY 範例(4) :  使用Linq Extension

///查詢每個月需要/不需要用膳的數目
///Group by CheckInOn月份, IsDinner

int _year = DateTime.Now.Year;

var _dinnerInfo =

db.Reservations.

Where(x => x.CheckInOn.Year == _year).

GroupBy(x => new { mon = x.CheckInOn.Month, IsDinner = x.IsDinner }).

Select(

   group => new

   {

        Month = group.Key.mon,

        IsDinner = group.Key.IsDinner,

        Count = group.Count()

}).OrderBy(x => x.Month); PS. 此範例Group by兩個欄位

PS. 如果Group by的欄位超過一個，請使用 Key.{欄位名稱} 來取得值