Calculating DateTime with LINQ to SQL

Question

Hi,

I have two tables TimeSheet and TimeRecord. TimeRecord has Foreign Key TimeSheetId of TimeSheet. The following time-logs are from TimeRecord,

TimeSheet sample data:

TimeSheetId, StudentId

• 187 , 10
• 196 , 11
• 195 , 12

TimeRecord sample data:

TimeRecordId, TimeSheetId, TimeIn, TimeOut

• 1 , 187 , 8/17/2010 1:06:55 PM , 8/17/2010 1:53:49 PM
• 2 , 196 , 8/17/2010 1:31:28 PM , 8/17/2010 4:59:58 PM
• 3 , 187 , 8/17/2010 1:51:40 PM , 8/17/2010 4:59:02 PM
• 4 , 187 , 8/17/2010 2:13:35 PM , 8/17/2010 5:00:08 PM
• 5 , 196 , 8/17/2010 2:19:44 PM , 8/17/2010 5:00:14 PM
• 6 , 196 , 8/17/2010 2:23:02 PM , 8/17/2010 4:46:00 PM
• 7 , 195 , 8/17/2010 3:04:15 PM , 8/17/2010 4:58:34 PM

I'd like to get total time spent of each student. So, the result will be like something's like the following:

• 10 has 10hr 30mn 5sec
• 11 has 8hr 45mm 23sec
• 12 has 2hr 33mn 25sec

Thanks a lot.

Answer 1

Well, I don't know how well it will work, but I'd at least try:

var query = from record in db.TimeRecords
            group record by record.TimeSheetId into g
            select new { id = g.Key, 
                         TotalTime = g.Sum(x => x.TimeOut - x.TimeIn) } into t
            join student in db.TimeSheets
            on t.id equals student.TimeSheetId
            select new { student.StudentId, t.TotalTime };

It really depends on whether LINQ to SQL is happy to subtract one DateTime from another and then sum the results of doing that across multiple records. (If it works, TotalTime should end up as a TimeSpan.)

Answer 2

You can simply subtract one date from the other. This will give you a TimeSpan object. You can then use the ToString method of the TimeSpan object to display the time spent however you wish using standard TimeSpan format strings. Your might look something like this:

TimeSpan timeSpent = studentTimeRecord.TimeOut - studentTimeRecortd.TimeIn; 
string displayTime = timeSpent.ToString("[hh]hr [mm]mn [ss]sec");

Update: I just realized that I did not address the issue of grouping the students and summing over the grouping. I'll defer to Jon's answer for that.

Answer 3

I think this what you are trying to archive:

    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(DateDiffSecond(tr.TimeIn, tr.TimeOut)) _
                 Select StudentId, TimeSheetId, TotalTime

    Dim timespan As TimeSpan
    Dim formattedTimeSpan As String

    For Each q In query

        timespan = timespan.FromSeconds(q.TotalTime)
        formattedTimeSpan = String.Format("{0} hr {1} mm {2} sec", Math.Truncate(timespan.TotalHours), timespan.Minutes)
        Response.Write("Student " & " " & q.StudentId & " has " & q.TimeSheetId & " : " & formattedTimeSpan & "<br/>")

    Next

You need to import SqlMethods:

Imports System.Data.Linq.SqlClient.SqlMethods

Also Check out:

• LINQ to SQL Samples
• SqlMethods Class
• System.DateTime Methods (LINQ to SQL)