LINQ to SQL join 3 tables and select multiple columns and also using Sum

Question

Hi,

I have three tables Student, TimeSheet and TimeRecord.

Talbe columns:

• Student : StudentId, FirstName, LastName

• TimeSheet: TimeSheetId,StudentId, IsActive

• TimeRecord: TimeRecordId,TimeSheetId, BonusHour(type int), CreationDate

Table relationship:

• Student 1:N TimeSheet (FK StudentId)
• TimeSheet 1:N TimeRecord (FK TimeSheetId)

Student sample data:

StudentId, FirstName, LastName

• 10 , Macro , John
• 11 , Hiro , Edge
• 12 , Sarah , Lemon

TimeSheet sample data:

TimeSheetId, StudentId, IsActive

• 187 , 10 , True
• 196 , 11 , True
• 195 , 12 , True
• 199, 10 , False
• 200 , 12 , False

TimeRecord sample data:

TimeRecordId, TimeSheetId, BonusHour, IsValid, CreationDate

• 1 , 187 , 1 , True , 7/18/2010 10:23:25 PM

• 2 , 196 , 2 , True , 7/19/2010 2:23:25 PM

• 3 , 187 , 1 , False , 8/1/2010 2:5:25 AM

• 4 , 187 , 3 , True , 8/9/2010 12:23:13 PM

• 5 , 196 , 0 , True , 7/20/2010 6:15:25 PM

• 6 , 196 , 2 , True , 9/18/2010 2:23:25 PM

• 7 , 195 , 3 , False , 8/18/2010 2:23:25 PM

• 8 , 199, 4 , False , 7/18/2010 2:23:25 PM

I'd like to get total BonusHour of each student, only Active TimeSheet has Valid BonousHour that count. So, the result will be like something in the following:

For the month of July and so on for the any month

• Hiro Edge has 10hr for July 2010
• Sarah Lem has 8hr for July 2010
• Macro John has 6hr for July 2010

Here's what I tried so far:

 Dim query = From ts In db.TimeSheet _ 
                 Join tr In db.TimeRecord On tr.TimeSheetId Equals ts.TimeSheetId _ 
                 Group By ts.StudentId, tr.TimeSheetId Into TotalTime = Sum(BonusHour) 
                 Select StudentId, TimeSheetId, TotalTime 

I can't get the join of three tables right yet. I can only join two tables so far. I need to join Student table to the query to get student name.

Thanks a lot.

Update One

Dim query = From st In db.Student Select New With { .stName = st.FirstName & " " & st.LastName, _ 
.BonusHours = (From ts In st.TimeSheets Join tr in db.TimeRecord On tr.TimeSheetId Equals ts.TimeSheetId _
                    Where ts.IsActive = True And tr.IsValid = True _
                    Group By key = New With {ts.TimeSheetId, .MonthYear = (tr.CreationDate.Value.Month & "/" & tr.CreationDate.Value.Year)} Into BonusHr = Sum(tr.BonusHour)})}

Now, the problem is that how I can get the "MonthYear" out from "BournsHours". Because I want it like this :

• Hiro Edge has 10hr for July 2010
• Sarah Lem has 8hr for July 2010

• Macro John has 6hr for July 2010

• Hiro Edge has 0hr for August 2010

• Sarah Lem has 3hr for August 2010
• Macro John has 2hr for August 2010

and so on for any month.

Answer 1

(some C# and alternatives is removed for clarity, see history)

C# to VB.Net (don't know whether this is correct VB.Net):

Dim query = From st In db.StudentNew With { _
 st.FirstName, _
 st.LastName, _
 Key .BonusHours = (From ts In st.TimeSheets _ //Or st.TimeSheet
                    Where ts.IsActive _
                    From tr In ts.TimeRecords _ //Or ts.TimeRecord
                    Where tr.IsValid
                    Select tr.BonusHour).Sum() _
}

In cases where you have unique on a column you should use .Single instead of a from. Which unique´s do you have on the columns? Is it unique in TimeSheet?

If you set up associations with foreign keys LINQ to SQL can make this a lot easier.

Addition based on your updated code (I don't think this is actually valid code):

Dim query = From st In db.Student
            Let pair = From ts In st.TimeSheets
                       Join tr In db.TimeRecord On tr.TimeSheetId Equals ts.TimeSheetId _
                       Where ts.IsActive = True And tr.IsValid = True _
                       Group By key = New With {
                           tr.CreationTime.Month,year/month - not sure how the syntax will be
                           tr.CreationTime.Year} Into BonusHr = Sum(tr.BonusHour)}
            From part In pair.BonusHr
            Select New With {
                .stName = st.FirstName & " " & st.LastName, _
                .BonusHours = part.BonusHours, _
                .YearMonth = key.Month + " " + key.Year _
            }

Answer 2

I know you want VB.NET code, but I don't have much exp in VB.NET so here is C# code. You can convert it using converters.

Prerequisite: You have set up Entity Framework and associates as necessary.

    var q = db.Students
              .Include("TimeSheet")
              .Include("TimeSheet.TimeRecord")
              .ToList();

    q.ForEach(i=>
{
  Console.WriteLine(string.Format("{0} {1}: {2} bonus hours",i.FirstName, i.LastName, i.Sum(ii => ii.BonusHour))
});

Edit: Fix typing error

Answer 3

Here's the working query:

Dim query = From ts In db.TimeSheets_
            Join tr In db.TimeRecords On tr.TimeSheetId Equals ts.TimeSheetId _
     Where ts.IsActive = True And tr.IsValid = True _
     Group By key = New With {ts.Student, .MonthYear = (tr.TimeOut.Value.Month & "/" & tr.TimeOut.Value.Year)} Into TotalHour = Sum(BonusHour) _
                     Select key.Student.StudentId, key.Student.AssignedId, key.MonthYear, TotalHour