Avoid LINQ with Let keyword doing self-loop

Question

I have three tables Student, TimeSheet and TimeRecord.

Talbe columns:

• Student : StudentId, AssignedId, FirstName, LastName

• TimeSheet: TimeSheetId,StudentId, IsArchive, IsComplete

• 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, AssignedId, FirstName, LastName

• 100 , 741 , Macro , John
• 101 , 742 , Hiro , Edge
• 102 , 743, Sarah , Lemon and son on..

TimeSheet sample data:

TimeSheetId, StudentId, IsArchive, IsComplete

• 187 , 100 , True , False
• 196 , 101 , True , False
• 195 , 102 , True , False
• 199, 100 , True , True
• 200 , 102 , True , True

TimeRecord sample data:

TimeRecordId, TimeSheetId, BonusHour, CreationDate

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

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

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

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

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

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

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

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

Actually, I'm trying to get monthly total bonus hour of every student.

    Dim query = From s In db.Students _
                 Let pair = (From ts In db.TimeSheets _
          Join tr In db.TimeRecords On tr.TimeSheetId Equals ts.TimeSheetId _
    Where ts.IsArchive = False And ts.IsCompleted = False And tr.TimeOut IsNot Nothing _
    Group By key = New With {ts.StudentId, .MonthYear = (tr.CreationDate.Value.Month & "/" & tr.CreationDate.Value.Year)} Into TotalHour = Sum(tr.BonusHour)) _
                From part In pair _
                Select New With {.stId = s.AssignedId, .MonthYear = part.key.MonthYear, .TotalHour = part.TotalHour}

This query loops over "pair" element and assign it to every student.

AssignedId , MonthYear, TotalHour

- 741, 6/2010 , 5
- 742, 6/2010 , 5
- 743, 6/2010 , 5
- 744, 6/2010 , 5
- 745, 6/2010 , 5

- 741, 7/2010 , 8
- 742, 7/2010 , 8
- 743, 7/2010 , 8
- 744, 7/2010 , 8
- 745, 7/2010 , 8

and so on..till last result of "pair" element.

So, the correct result should be like this:

- 741, 6/2010 , 5
- 742, 6/2010 , 8
- 743, 6/2010 , 9
- 744, 6/2010 , 10
- 745, 6/2010 , 15

Or like this :

AssignedId , Month/Year for example:

AssignedId, 7/2010, 8/2010

 - 741 , 8hr, 2hr
 - 742, 3hr ,4hr
 - 743, 3hr, 1hr
 - and son on..

Can you help me correct my query above? Any suggestion either in C# or VB.NET is welcome. Thanks.

Answer 1

At the top-level, your query is formed from two resultsets:

1. db.Students
2. pair, as defined by the Let clause.

Since you have two From clauses (one for db.Students, one for pair) at the top-level and no Join or Where clause to associate one to the other, LINQ constructs what is essentially a cross-join between them.

I think you want to either Join the second resultset (pair) to the first (db.Students), or otherwise indicate their association in a top-level Where clause.

Answer 2

Instead of starting from a Student, I would consider starting from the TimeRecord itself. So long as your Linq To Sql mapping is correct (and relationships are in place), you'll already have some useful relationship properties to use. Here is how I've formed the query, though a few changes may need to be made when moving to VB.

var hours = from tr in db.TimeRecords
            where !tr.TimeSheet.IsArchive && !tr.TimeSheet.IsCompleted && tr.TimeOut != null
            group tr.BonusHour by
                new {
                        tr.TimeSheet.Student,
                        MonthYear = tr.CreationDate.Value.Month + "/" + tr.CreationDate.Value.Year
                    }
            into g
            select new {g.Key.Student.AssignedId, g.Key.MonthYear, TotalHour = g.Sum()};

Let me know if your model differs, but this idea should work for you.