Group By Query Example in Linq
public DataTable GetApprovedLeaveData(int emp_id)
{
DataClassesHrPortalDataContext dc1 = new DataClassesHrPortalDataContext(dbConnStr);
var obj = from tsk in dc1.Tasks
join lvr in dc1.Leave_Requests on tsk.task_id equals lvr.Task_Id
where tsk.requested_by == emp_id && tsk.substatus == "Approved"
group lvr by new { tsk.requested_by, lvr.Leave_Type } into grp
select new { grp.Key.requested_by, grp.Key.Leave_Type, ndays = grp.Sum(lvr => lvr.ndays) };
IDbCommand objCmd = dc1.GetCommand(obj as IQueryable);
SqlDataAdapter objAdpt = new SqlDataAdapter();
objAdpt.SelectCommand = (SqlCommand)objCmd;
DataTable objDt = new DataTable();
try
{
objCmd.Connection.Open();
objAdpt.Fill(objDt);
}
finally
{
objCmd.Connection.Close();
}
return objDt;
}
public DataTable GetApprovedLeaveData(int emp_id)
{
DataClassesHrPortalDataContext dc1 = new DataClassesHrPortalDataContext(dbConnStr);
var obj = from tsk in dc1.Tasks
join lvr in dc1.Leave_Requests on tsk.task_id equals lvr.Task_Id
where tsk.requested_by == emp_id && tsk.substatus == "Approved"
group lvr by new { tsk.requested_by, lvr.Leave_Type } into grp
select new { grp.Key.requested_by, grp.Key.Leave_Type, ndays = grp.Sum(lvr => lvr.ndays) };
IDbCommand objCmd = dc1.GetCommand(obj as IQueryable);
SqlDataAdapter objAdpt = new SqlDataAdapter();
objAdpt.SelectCommand = (SqlCommand)objCmd;
DataTable objDt = new DataTable();
try
{
objCmd.Connection.Open();
objAdpt.Fill(objDt);
}
finally
{
objCmd.Connection.Close();
}
return objDt;
}