Ling to SQL C# Group By

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 only able to get the Hours at the moment with the following. ........
How do Get the sum of Hours & Min ?



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

Open in new window

u2envy1Asked:
Who is Participating?
 
reb73Connect With a Mentor Commented:
Try this -
HoursTC = (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
			select new
			{
			Category = TotalArea.Key,
			Hours = TotalArea.Sum(TotalArea => TotalArea.Hours)
			Mins  = TotalMins.Sum(TotalArea => TotalArea.Min)
			});

Open in new window

0
 
reb73Commented:
Can you try this -

HoursTC = (from d in Empctx.DailyHoursTimeCategories
                                          join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
                                          where empCatList.Contains(d.DailyHoursID)
                                          group d.Hours + d.Min by tc.TimeCategoryName into TotalArea                                          
                                          select new
                                          {
                                              Category = TotalArea.Key,
                                              Hours = TotalArea.Sum()
                                              
 
                                          });

Open in new window

0
 
u2envy1Author Commented:
Not working get zeros back.....
I need to sum the Hour & Minutes columns.
Hours = TotalArea.Sum()
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
u2envy1Author Commented:
var rsDailyMinutesTC = (from d in Empctx.DailyHoursTimeCategories
                                            join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
                                            where empCatList.Contains(d.DailyHoursID) && d.Minutes != null
                                            group d.Hours + d.Minutes by tc.TimeCategoryName into TotalArea
                                            select new
                                            {
                                                Category = TotalArea.Key,
                                                Hours = TotalTimeHours,
                                                Minutes = TotalTimeMinutes

                                            });
0
 
reb73Commented:
How about this -
HoursTC = (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
			group d.Min by tc.TimeCategoryName into TotalMins
			select new
			{
			Category = TotalArea.Key,
			Hours = TotalArea.Sum()
			Mins  = TotalMins.Sum()
			});

Open in new window

0
 
u2envy1Author Commented:
Double groups not allowed.
0
 
Fernando SotoRetiredCommented:
Hi u2envy1;

I used Table1 and Table2 in my query cause I was not sure from your question, see code snippet.

Fernando
THIS IS Table1 IN THE QUERY    THIS IS Table2 IN THE QUERY                                          
DailyHoursID                   DailyHoursID     TimeCategory         Hours     Min                   
1                              1                0                    8         30
2                              1                1                    9         0 
3                              2                0                    6         30
 
 
var rsDailyHoursTC = from t1 in Empctx.Table1
                     join t2 in Empctx.Table2 on t1.DailyHoursID equals t2.DailyHoursID
                     into groupedRec
                     where groupedRec.Any()
                     select new
                     {
                         t1.DailyHoursID,
                         totalHours = groupedRec.Sum(h => h.Hours),
                         totalMin = groupedRec.Sum(m => m.Min)
                     };
 
foreach (var rec in rsDailyHoursTC)
{
    Console.WriteLine("Catagory = {0} - Hours = {1} - Min = {2}",
        rec.DailyHoursID, rec.totalHours, rec.totalMin);
}
 
 
// Results of running the code with the test data.
Catagory = 1 - Hours = 17 - Min = 30
Catagory = 2 - Hours =  6 - Min = 30

Open in new window

0
 
u2envy1Author Commented:
reb73:
TotalMins is not defined....
 Mins  = TotalMins.Sum(TotalArea => TotalArea.Min)

FernandoSoto:
Bellow is the SQL code I would like to duplicate in Linq.
Im getting errors on

(h => h.Hours)
(m => m.Min)


SELECT     TimeCategory.TimeCategoryName, Sum(DailyHoursTimeCategories.Hours) AS [Hours] , Sum(DailyHoursTimeCategories.Minutes) AS [Minutes]
FROM         DailyHours INNER JOIN
                      DailyHoursTimeCategories ON DailyHours.DailyHoursID = DailyHoursTimeCategories.DailyHoursID INNER JOIN
                      TimeCategory ON DailyHoursTimeCategories.TimeCategoryID = TimeCategory.TimeCategoryID
GROUP BY TimeCategory.TimeCategoryName

Open in new window

0
 
reb73Connect With a Mentor Commented:
That was typo. Please change the line as follows  -

 Mins  = TotalArea.Sum(TotalArea => TotalArea.Minutes)
0
 
u2envy1Author Commented:
Thx peeps.......
All your efforts is much appreciated reb73.
It is working !!
 var rsDailyHoursTC2 = (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
                                           select new
                                           {
                                               Category = TotalArea.Key,
                                               Hours = TotalArea.Sum(h => h.Hours),
                                               Mins = TotalArea.Sum(m => m.Minutes)
                                           });
0
 
u2envy1Author Commented:
Thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.