Ling to SQL C#

This is what I have.
Table1 DailyHours
DailyHoursID
1
2
3

Table2 HoursTimeCategory
DailyHoursID     TimeCategory         Hours
1                         0                              8
1                         1                              9
2                         0                              6


How do I get a result set of the bellow using LINQ
TimeCategory     Hours
0                          14
1                          9
var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories
                                          join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
                                          where empCatList.Contains(d.DailyHoursID)
                                          orderby d.TimeCategoryID
                                          select new
                                          {
                                              d.DailyHoursID,
                                              d.TimeCategoryID,
                                              d.Hours,
                                              d.Minutes,
                                              tc.TimeCategoryName
                                          });

Open in new window

u2envy1Asked:
Who is Participating?
 
naspinskiCommented:
you are forgetting the mighty group *** by ***
var rsDailyHoursTC = from p in Empctx.DailyHoursTimeCategories
                     group p by p.TimeCategory into q
                     select new { TimeCategory = q.Key, Hours = q};
 
//now you have it grouped by TimeCategory, just spit it out and sum up the Hours:
 
foreach (var v in rsDailyHoursTC)
    Response.Write(v.TimeCategory + " - " + v.Hours.Sum(h => h.Hours) + "<br />");

Open in new window

0
 
DhaestCommented:
Your query must look like this:

        var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories_
                              Join join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
                              Group By d.TimeCategoryID Into TotalArea = sum(d.Hours)
                              Select d.TimeCategoryID , TotalArea)
0
 
u2envy1Author Commented:
Im getting errors.
I added the Group d By d.TimeCategoryID Into TotalArea = sum(d.Hours)
at sum(d. // Im not getting the column names.

var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories_
                              Join join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
                              Group By d.TimeCategoryID Into TotalArea = sum(d.Hours)
                              Select d.TimeCategoryID , TotalArea)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
u2envy1Author Commented:
I got so far without errors......


 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 by d.TimeCategoryID into TotalArea
                                          select TotalArea);

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi u2envy1;

Basically the same way except leave out the Min column.

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:
Still getting errors on
(h => h.Hours)
(m => m.Min)
0
 
naspinskiCommented:
did you try my answer?  group by sidesteps all these problems
0
 
u2envy1Author Commented:
Sorry naspinski,
I just tried it & it does not work.
I have this working so far. Just need to add the minutes.
Any help.....
  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(),
                                              //Minutes = TotalArea.Sum()
 
                                          });

Open in new window

0
 
u2envy1Author Commented:
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
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.