Kevin Robinson
asked on
LINQ Query
I have the function which uses a 2 LINQ Queries to
1) get all the timesheet entries from a particilur date.
2) count the time for these entries.
Is there a way to make this 1 query?
1) get all the timesheet entries from a particilur date.
2) count the time for these entries.
Is there a way to make this 1 query?
Public Function GetTIMESHEETsMonday() As Double
Dim Monday As Date = GetThisMondayDate()
Dim totalTime = From t In Me.ObjectContext.TIMESHEETs
Where t.TaskDate = Monday
Select t
If totalTime.Count <> 0 Then
Return Aggregate T In totalTime Into Sum(T.TaskTime)
Else
Return 0
End If
End Function
Or something like:
Dim x As Integer = If((From t In Me.ObjectContext.TIMESHEET
Where t.TaskDate = Monday
Select t.TaskTime).Sum, 0)
Arun
Dim x As Integer = If((From t In Me.ObjectContext.TIMESHEET
Where t.TaskDate = Monday
Select t.TaskTime).Sum, 0)
Arun
ASKER
Error on second one.
First operand in binary if must be nullable or referance type
First operand in binary if must be nullable or referance type
I think this should work...
Dim sum as Integer = (From item In timesheets Where item.TaskDay == Day.Monday Select item).Sum<Timesheet>(t => t.TaskTime);
Dim sum as Integer = (From item In timesheets Where item.TaskDay == Day.Monday Select item).Sum<Timesheet>(t => t.TaskTime);
This should work if TaskTime is not a nullable integer
Dim sum as Integer = (From item In timesheets
Where item.TaskDay == Day.Monday
Select item.TaskTime).Sum(t => t);
Hi VolunteerDevelopmentAgency
Actually this should work even if TaskTime is nullable in the database.
Fernando
Actually this should work even if TaskTime is nullable in the database.
Fernando
Dim sum As Integer = (From t In ObjectContext.TIMESHEETs _
Where t.TaskDate = Monday _
Select t.TaskTime).Sum()
ASKER
What happens when there is No tasks for a given day?
Hi VolunteerDevelopmentAgency
If that is the case then test the results before summing, see code snippet.
Fernando
If that is the case then test the results before summing, see code snippet.
Fernando
Dim sum = From t In ObjectContext.TIMESHEETs _
Where t.TaskDate = Monday _
Select t.TaskTime
Dim sum As Integer = IIf(query IsNot Nothing, query.Sum(), 0)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did that work for you?
ASKER
Got this, which is pretty much what I already had.
Private Function GetTotalTimeForADay(ByVal Day As Date) As Double
Dim totalTime = From t In Me.ObjectContext.TIMESHEET
Where t.TaskDate = Day
Select t
If totalTime.Count <> 0 Then
Return Aggregate T In totalTime Into Sum(T.TaskTime)
Else
Return 0
End If
End Function
Private Function GetTotalTimeForADay(ByVal Day As Date) As Double
Dim totalTime = From t In Me.ObjectContext.TIMESHEET
Where t.TaskDate = Day
Select t
If totalTime.Count <> 0 Then
Return Aggregate T In totalTime Into Sum(T.TaskTime)
Else
Return 0
End If
End Function
Hi VolunteerDevelopmentAgency
You may want to go the way I showed in my last post because in your solution you are downloading from the server all the fields in the table for each row selected just to Sum one field. In my solution I bring down the only field being used to Sum which placed a much smaller load on the network. The two solutions are basically the same except for the number of fields being transmitted over the wire.
Fernando
You may want to go the way I showed in my last post because in your solution you are downloading from the server all the fields in the table for each row selected just to Sum one field. In my solution I bring down the only field being used to Sum which placed a much smaller load on the network. The two solutions are basically the same except for the number of fields being transmitted over the wire.
Fernando
ASKER
Good point thanks.
If the issue has been answered please close the question.
Thank you.
Thank you.
Where t.TaskDate = Monday
Select t.TaskTime).Sum
What does this return?
Arun