How to Sum the duration in Gridview List by using LINQ to SQL?

Question

Hi guys, How you doing today? Wish you all good.

By the way, here is my problem. I would like to display the total of sum duration in gridview by using LINQ to SQL. I have two tables, Users and Log_Times

Users
UserID-----Name
1------------Bob
2------------Mary
3------------Jane

Log_Times
ID---------------UserID--------------------TimeIn---------------------TimeOut
1--------------------1-----------------------1/1/2009 10:00:00-------1/1/2009 11:05:00
2--------------------2-----------------------1/1/2009 10:00:00-------1/1/2009 11:02:30
3--------------------3-----------------------1/1/2009 10:00:00-------1/1/2009 11:00:00
4--------------------1-----------------------1/2/2009 10:00:00-------1/2/2009 11:05:00
5--------------------2-----------------------1/2/2009 10:00:00-------1/2/2009 11:02:30

There would be so great, if our gentle man can generate report which looks like this:
UserName-------TotalDuration
Bob---------------2 Hours, 10 Minutes
Mary--------------2 Hours, 5 Minutes
Jane--------------1 Hour

I am using LINQ to SQL and VB.NET.

The code I have so far is:

Dim db = new LogTimeDataContext
Dim LogUser = from lu in db.Users select lu.Name, TotalDuration = ? ? ?
Gridview1.DataSource = LogUser
Gridview1.DataBind()

Please and please help me to figure out what is behind this TotalDuration code..

I really appreciate for taking your valuable time to pass through this problem and Im looking forward for the best response toward it. Thanks you very much in advance.

Answer 1

You could write something like this

from t in db.Log_Times 
join u in db.Users on t.UserID equals u.UserID 
select new {user = u.name, Sum(t.TimeOut - t.TimeIn) AS Duration };

I don't know if the sum part is correct but it's maybe the sort of route you'd like to go down.

I'm sure someone more experienced in SQL could modify the query to be correct.

Answer 2

Update:

Dim QueryLogUser = from u in db.Users 
                   Select u.Name, Duration = u.Log_Times.Select (Function(lt) SqlMethods.DateDiffSecond(lt.TimeIn, lt.TimeOut)).Sum).OrderbyDescending(function(o) o.Duration)

The Query below only returns users who have an entry in the Log_times table.

Imports System.Data.Linq.SqlClient

Dim db = New LogTimeDataContext
Dim LogUser = From log In db.Log_Times _
              Group By _
                 Name = log.User.Name _
              Into _
                 Duration = Sum(SqlMethods.DateDiffMinute(log.TimeIn,log.TimeOut) _
              Select name, TotalDuration = TimeSpan.FromMinutes(If(Duration,0)).tostring

The final line just checks that the duration is not nothing before converting to a timespan

Answer 3

The solution which is working fine is this.

Imports System.Data.Linq.SqlClient 

Dim QueryLogUser = from u in db.Users 
Select u.Name, Duration = u.Log_Times.Select (Function(lt) SqlMethods.DateDiffSecond(lt.TimeIn, lt.TimeOut))

Dim LogUser = From u In QueryLogUser.AsEnumerable _
Select u.Name, TotalDuration = u.Duration.Sum Order By TotalDuration Descending

By this I can get the total second. Then I will write one more function in gridview later to convert from Second into minutes and hour.

Dim totalSecond as Integer
Dim totalMinute as Integer
Dim totalHour as Integer

Protected Function FieldDisplayDuration(ByVal Second As Integer)
 Dim d As String = "DefaultValue"
 If (Second > 60)
   totalSecond = Second mod 60
   totalMinutes = Second / 60
   If (totalMinutes > 60)
      totalHour = totalMinute mod 60
      totalMinute = totalMinute + totalMinute mod 60
      return totalHour & totalMinute & totalSecond
   End If
   return totalMinute & totalSecond
 End If
 return totalSecond

End Function

In this way, I could get my duration column in the format of Hour, Minute, and Second.Happy coding :)