Grouping with LINQ
-
This is what I have..... DailyHoursID 1 2 3 Table2 HoursTimeCategory DailyHoursID TimeCategory Hours Min 1 0 8 30 1 1 9 0 2 0 6 30 How do I get a result set of the bellow using LINQ TimeCategory Hours Min 0 14 60 1 9 0 Im getting the Hours Summed up. I just cant get the Minutes to sum up. var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d.Hours by tc.TimeCategoryName into TotalArea select new { Category = TotalArea.Key, Hours = TotalArea.Sum() });
-
This is what I have..... DailyHoursID 1 2 3 Table2 HoursTimeCategory DailyHoursID TimeCategory Hours Min 1 0 8 30 1 1 9 0 2 0 6 30 How do I get a result set of the bellow using LINQ TimeCategory Hours Min 0 14 60 1 9 0 Im getting the Hours Summed up. I just cant get the Minutes to sum up. var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d.Hours by tc.TimeCategoryName into TotalArea select new { Category = TotalArea.Key, Hours = TotalArea.Sum() });
See if this works (its not tested). I am guessing you need things grouped by the TimeCategory.
var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories
group d.TimeCategory into TotalArea
orderby TotalArea.TimeCategory
select new
{
TimeCategory = TotalArea.TimeCategory,
Hours = TotalArea.Sum( h => h.Hours),
Min = TotalArea.Sum( m => m.Min)
});EDIT: Changed from DailyHoursID to TimeCategory
-
See if this works (its not tested). I am guessing you need things grouped by the TimeCategory.
var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories
group d.TimeCategory into TotalArea
orderby TotalArea.TimeCategory
select new
{
TimeCategory = TotalArea.TimeCategory,
Hours = TotalArea.Sum( h => h.Hours),
Min = TotalArea.Sum( m => m.Min)
});EDIT: Changed from DailyHoursID to TimeCategory
You missing a by clause group by lambada Error on TotalArea.TimeCategory, var rsDailyMinutesTC1 = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d.TimeCategory by tc.TimeCategoryName into TotalArea select new { TimeCategory = TotalArea.TimeCategory, Hours = TotalArea.Sum( h => h.Hours), Min = TotalArea.Sum( m => m.Min) });
-
You missing a by clause group by lambada Error on TotalArea.TimeCategory, var rsDailyMinutesTC1 = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d.TimeCategory by tc.TimeCategoryName into TotalArea select new { TimeCategory = TotalArea.TimeCategory, Hours = TotalArea.Sum( h => h.Hours), Min = TotalArea.Sum( m => m.Min) });
u2envy12 wrote:
ambada Error on TotalArea.TimeCategory
Is the column name TimeCategory or TimeCategoryName?
var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories
group d by d.TimeCategory into TotalArea
orderby TotalArea.TimeCategoryName
select new
{
TimeCategory = TotalArea.TimeCategoryName,
Hours = TotalArea.Sum( h => h.Hours),
Min = TotalArea.Sum( m => m.Min)
}); -
u2envy12 wrote:
ambada Error on TotalArea.TimeCategory
Is the column name TimeCategory or TimeCategoryName?
var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories
group d by d.TimeCategory into TotalArea
orderby TotalArea.TimeCategoryName
select new
{
TimeCategory = TotalArea.TimeCategoryName,
Hours = TotalArea.Sum( h => h.Hours),
Min = TotalArea.Sum( m => m.Min)
}); -
Its TimeCategoryName Error on orderby TotalArea."TimeCategoryName" Does not contain a definition for TimeCategoryName
-
Get an error on orderby TotalArea.TimeCategoryName If I remove orderby Get an error on TimeCategory = TotalArea.TimeCategoryName, var rsDailyHoursTC1 = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d by tc.TimeCategoryName into TotalArea orderby TotalArea.TimeCategoryName select new { TimeCategory = TotalArea.TimeCategoryName, Hours = TotalArea.Sum(h => h.Hours), Min = TotalArea.Sum(m => m.Minutes) });
-
Get an error on orderby TotalArea.TimeCategoryName If I remove orderby Get an error on TimeCategory = TotalArea.TimeCategoryName, var rsDailyHoursTC1 = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d by tc.TimeCategoryName into TotalArea orderby TotalArea.TimeCategoryName select new { TimeCategory = TotalArea.TimeCategoryName, Hours = TotalArea.Sum(h => h.Hours), Min = TotalArea.Sum(m => m.Minutes) });
-
u2envy12 wrote:
DailyHoursID 1 2 3 Table2 HoursTimeCategory DailyHoursID TimeCategory Hours Min 1 0 8 30 1 1 9 0 2 0 6 30
I wrote the query based on the data you provided. The query you have posted and data(tablename, column name) do not match at all.
Sorry..... Let me get the names correct. DailyHoursID 1 2 3 Table2 HoursTimeCategory DailyHoursID TimeCategoryID Hours Min 1 0 8 30 1 1 9 0 2 0 6 30 Table3 TimeCategory TimeCategoryID TimeCategoryName 1 Normal Time 2 Over Time Join on Table3 to get the TimeCategoryName
-
This is what I have..... DailyHoursID 1 2 3 Table2 HoursTimeCategory DailyHoursID TimeCategory Hours Min 1 0 8 30 1 1 9 0 2 0 6 30 How do I get a result set of the bellow using LINQ TimeCategory Hours Min 0 14 60 1 9 0 Im getting the Hours Summed up. I just cant get the Minutes to sum up. var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID where empCatList.Contains(d.DailyHoursID) group d.Hours by tc.TimeCategoryName into TotalArea select new { Category = TotalArea.Key, Hours = TotalArea.Sum() });
You are trying to group and join at the same time, which is actually quite problematic in LINQ. Instead, join and select the fields you need into a new result set, then group that result set. Like so:
var rsDailyHoursTC =
from rs in
(
from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID)
select new { TimeCategoryName = tc.TimeCategoryName, Hours = d.Hours, Minutes = d.Min }
)
group rs by rs.TimeCategoryName into g
select new { Category = g.Key, Hours = g.Sum(r => r.Hours), Minutes = g.Sum(r => r.Minutes) };