?
Solved

Ling to SQL C# Group By

Posted on 2009-02-12
11
Medium Priority
?
1,565 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

0
Comment
Question by:u2envy1
  • 6
  • 4
11 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23621834
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
 

Author Comment

by:u2envy1
ID: 23622026
Not working get zeros back.....
I need to sum the Hour & Minutes columns.
Hours = TotalArea.Sum()
0
 

Author Comment

by:u2envy1
ID: 23622028
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 25

Expert Comment

by:reb73
ID: 23622188
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
 

Author Comment

by:u2envy1
ID: 23622241
Double groups not allowed.
0
 
LVL 25

Accepted Solution

by:
reb73 earned 2000 total points
ID: 23624138
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 23624549
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
 

Author Comment

by:u2envy1
ID: 23630299
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
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 2000 total points
ID: 23630842
That was typo. Please change the line as follows  -

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

Author Comment

by:u2envy1
ID: 23631354
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
 

Author Closing Comment

by:u2envy1
ID: 31546008
Thx
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question