CODEWITHSUNDEEP
c#.netvb.netlinqlinq-to-sql
Narazana
Narazana

Reputation: 1950

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

I have three tables Student, TimeSheet and TimeRecord.

Talbe columns:

Table relationship:

Student sample data:

StudentId, FirstName, LastName

TimeSheet sample data:

TimeSheetId, StudentId, IsActive

TimeRecord sample data:

TimeRecordId, TimeSheetId, BonusHour, IsValid, CreationDate

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

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 :

and so on for any month.

Upvotes: 4

Views: 16012

Answers (3)

Lasse Espeholt
Lasse Espeholt

Reputation: 17782

(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 _
            }

Upvotes: 2

Narazana
Narazana

Reputation: 1950

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

Upvotes: 3

Holystream
Holystream

Reputation: 972

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

Upvotes: 2

Related Questions