LINQ to SQL sum null value

Question

Hi,

I have the following query, I'd like to sum the NULL value also. Some TimeSheet don't records in TimeRecord and some tr.TimeIn and tr.TimeOut are NULL.

The query select only TimeSheet that has reords in TimeRecord. How I can have it select everything, and sum up the NULL value as well. So, the SUM of NULL will be just zero.

Table relationship:

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

TimeIn and TimeOut are DateTime type and nullable.

Query 1: Monthy Report:

    Dim query = 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.Student, .MonthYear = (tr.TimeOut.Value.Month & "/" & tr.TimeOut.Value.Year)} Into TotalHour = Sum(DateDiffSecond(tr.TimeIn, tr.TimeOut)) _
                 Select key.Student.StudentId, key.Student.AssignedId, key.MonthYear, TotalHour 

Query 2: Total TimeRecord for Student with Active TimeSheet:

Dim query = From ts In db.TimeSheets _
             Join tr In db.TimeRecords On tr.TimeSheetId Equals ts.TimeSheetId _
             Where ts.IsArchive = False And ts.IsCompleted = False _
             Group By ts.StudentId, tr.TimeSheetId Into TotalTime = Sum(DateDiffSecond(tr.TimeIn, tr.TimeOut)) _
             Select StudentId, TimeSheetId, TotalTime

Here's the result of the query 2:

• 734 -- 159 : 9 hrs 35 mm 28 sec
• 2655 -- 160 : 93 hrs 33 mm 50 sec
• 1566 -- 161 : 37 hrs 23 mm 53 sec
• 3114 -- 162 : 25 hrs 0 mm 21 sec

Wanted result of Query 2:

• 733 -- 158 : 0 hr 0mm 0 sec
• 734 -- 159 : 9 hrs 35 mm 28 sec
• 736 -- 169 : 0 hrs 0mm 0sec
• 2655 -- 160 : 93 hrs 33 mm 50 sec
• 1566 -- 161 : 37 hrs 23 mm 53 sec
• 3114 -- 162 : 25 hrs 0 mm 21 sec

Same for Query 1 but it makes monthly report.

Answer 1

I apologise because I translated your query to C# before tweaking it, and I don’t really know the VB syntax well enough to translate it back, but I hope that you will be able to. I tried the following query and it does what you asked for:

var query = from st in Students
    select new
    {
        st.StudentId,
        st.AssignedId,
        TotalHour = (
            from ts in TimeSheets
            where ts.StudentId == st.StudentId
            join tr in TimeRecords on ts.TimeSheetId equals tr.TimeSheetId
            where !ts.IsArchive && !ts.IsCompleted && tr.TimeOut != null
            select (tr.TimeOut.Value - tr.TimeIn).TotalHours
        ).Sum()
    };

I had to remove the MonthYear thing because I didn’t really understand how that fit in with your grouping, but since it’s not in the output, I suspected that maybe you don’t need it.

I had to make a few assumptions:

• I am assuming that TimeOut is a DateTime? (nullable) while TimeIn is DateTime (non-nullable). I think that makes sense.

• I am assuming that TimeSheets have a StudentId that links them to students.