We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Ling to SQL C# Group By

u2envy1
u2envy1 asked
on
Medium Priority
1,576 Views
Last Modified: 2013-11-11
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

Comment
Watch Question

Commented:
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

Author

Commented:
Not working get zeros back.....
I need to sum the Hour & Minutes columns.
Hours = TotalArea.Sum()

Author

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

                                            });

Commented:
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

Author

Commented:
Double groups not allowed.
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

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

Commented:
That was typo. Please change the line as follows  -

 Mins  = TotalArea.Sum(TotalArea => TotalArea.Minutes)

Author

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

Author

Commented:
Thx
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.