LINQ to SQL joins 3 tables and selects multiple columns, and also uses Sum

Question

I have three Student tables, TimeSheet and TimeRecord.

Talbe columns:

• Student: StudentId, FirstName, LastName

• TimeSheet: TimeSheetId, StudentId, IsActive

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

Table ratio:

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

Student 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, 07/17/2010 10:23:25

• 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, 09/18/2010 2:23:25 PM

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

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

I would like to receive the total bonus of each student, only Active TimeSheet has a valid bonus account, which is considered. So, the result will be something like the following:

For July, etc. for any month

• Hiro Edge has 10 hours in July 2010.
• Sarah Lem has 8 hours in July 2010.
• Macro John has 6 hours in July 2010.

Here is what I have 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 still can not get the join of the three tables. So far, I can join only two tables. I need to join the Student table to the query to get the student name.

Many thanks.

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 I can get "MonthYear" from "BournsHours". Because I want this:

• Hiro Edge has 10 hours in July 2010.
• Sarah Lem has 8 hours in July 2010.

• Macro John has 6 hours in July 2010.

• Hiro Edge has 0 hours in August 2010.

• Sarah Lem has 3 hours in August 2010.
• Macro John has 2 hours in August 2010.

etc. for any month.

Answer 1

Here's a working request:

 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 

Answer 2

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

C # on VB.Net (I don't know if this is VB.Net right):

 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 in a column, you should use .Single instead of from . What unique 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 really 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 3

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

Prerequisite: You created the Entity Framework and, if necessary, associated.

  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 input error