• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1425
  • Last Modified:

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

0
u2envy1
Asked:
u2envy1
3 Solutions
 
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
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
Fernando SotoCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now