2013年5月28日 星期二

[C#] Linq extension: GroupJoin By

///查詢每個月需要用膳的數目
///GroupJoin by CheckInOn月份,
var B = db.Reservations.Where(x => x.IsDinner == true);
var _dinnerInfo = A.GroupJoin( B,
                 x => x, //母體Ajoin key
                 y => y.CheckInOn.Month, //子體Bjoin key
                 (x, y) =>
                 new
                 {
                    Month = x,
                    IsDinner = "需要用餐的訂房數",
                    Count = y.Count()
                  });

[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.{欄位名稱} 來取得值

2013年5月24日 星期五

[C#] Enum用法

public enum EnumWeek
{
        Monday=1,
        Tuesday,
        Wednesday,
        Thursday,
        Friday,
        Saturday,
        Sunday = 0
}


///將字串轉換成Enum
var day1 = (EnumWeek)Enum.Parse(typeof(EnumWeek), "monday", true);
var day2 = (EnumWeek)Enum.Parse(typeof(EnumWeek), "tuesday", true);
var day7 = (EnumWeek)Enum.Parse(typeof(EnumWeek), "sunday", true);

//取得Enum的值(字串)
Console.WriteLine(day1.ToString());
Console.WriteLine(day2.ToString());
Console.WriteLine(day7.ToString());

//取得Enum的值(數字)
Console.WriteLine((int)day1);
Console.WriteLine((int)day2);
Console.WriteLine((int)day7);



Output :

Monday
Tuesday
Sunday
1
2
0