If i have a LINQ query that's querying a table with a date field, how do you return the first date and last date in the recordset?
For example, I want to return a list of employee time entries, and then also return the date range for those entries...
string time_flagInProcess = "I";
var GetInProcessEntriesqry = from t in db.timesheetView
where t.time_flag == time_flagInProcess
orderby t.first_name, t.workday
select new
{
t.EmpID,
EmpName = t.first_name + " " + t.last_name,
t.workday,
t.time_flag
};
Ok, this will return all records that have a flag of "I", and there would be lots of duplicates... (I'm excluding project information here; this is just for a report to display links to the time sheets for each person.)
Bob Jones ... 12/10/2012 ... I Bob Jones ... 12/10/2012 ... I Ann Smith ... 12/12/2012 ... I Ann Smith ... 12/12/2012 ... I Ann Smith ... 12/12/2012 ... I Bob Jones ... 12/12/2012 ... I Bob Jones ... 12/12/2012 ... I Bob Jones ... 12/12/2012 ... I Ann Smith ... 12/15/2012 ... I Ann Smith ... 12/15/2012 ... I ... ... Bob Jones ... 12/23/2012 ... I
Now, I don't want to return all records; I want to group them by person, which I do here:
var distinctInProcessResults = (from t in GetInProcessEntriesqry
group t by t.EmpID into uniqueIds
select uniqueIds.FirstOrDefault())
.OrderBy(x => x.EmpName);
InprocessTimeSheetDataList.DataSource = distinctInProcessResults;
InprocessTimeSheetDataList.DataBind();
So, that gives me just one entry for each person for that parameter (which is "I" for "In Process Time Entries"), and it displays just the first date in the list.
Ann Smith (12/12/2012)
Bob Jones (12/10/2012)
But what I want is to display the dates of the oldest and newest entries. Does that make sense? So, it would return something like:
Ann Smith (12/12/2012-12/15/2012)
Bob Jones (12/10/2012-12/23/2012)
... to give me the date range for each person's in-process time entries.
Do I need to do a separate LINQ query to get the min and max date, and then store that as a variable?
It looks like you may have to make multiple passes through the data....
var emps = (from t in db.timesheetView
where t.time_flag == time_flagInProcess
select t).distinct()
foreach (timesheetview emp in emps){
datetime min =from t in db.timesheetView where t.EmpID = emp.EmpID && t.time_flag == time_flagInProcess orderby t.first_name, t.workday select t.workday).Min();
datetime max =from t in db.timesheetView where t.EmpID = emp.EmpID && t.time_flag == time_flagInProcess orderby t.first_name, t.workday select t.workday).Max();
//do something with the values, e.g. add them to a List<> and return that
You can group by "Name" and then fetch the largest datetime and smallest datetime：
var result = from emp in xxxEmployees
group emp by emp.Name into temp
select new
{
EmployeeName = temp.Name,
StartDateTime = temp.Min(m=>m.DateTimeColumn),
EndDateTime = temp.Max(m=>m.DateTimeColumn)
}
And then you can use foreach to loop the result and do binding……
Marked as answer by dezinnia on Jan 08, 2013 05:41 PM
I played with this a bit, and I got partway there...
foreach (DataListItem item in InprocessTimeSheetDataList.Items)
{
if ((item.ItemType == ListItemType.Item) || (item.ItemType == ListItemType.AlternatingItem))
{
DataList InprocessTimeSheetDataList2 = InprocessTimeSheetDataList.Items[0].FindControl("InprocessTimeSheetDataList2") as DataList;
HiddenField EmpID = InprocessTimeSheetDataList.Items[0].FindControl("EmpID") as HiddenField;
int EmpIDint = Convert.ToInt32(EmpID.Value);
var minmaxdates = from emp in GetInProcessEntriesqry //this is the original query that gives all the time entries
where emp.empID == EmpIDint
group emp by emp.empID into temp
select new
{
StartDateTime = temp.Min(m => m.workday),
EndDateTime = temp.Max(m => m.workday)
};
InprocessTimeSheetDataList2.DataSource = minmaxdates;
InprocessTimeSheetDataList2.DataBind();
//this is only giving me a date for the first person!
}
}
So, it returns the correct date range for the first record, but no dates are listed after that first record. What might I be doing wrong?
dezinnia
Member
18 Points
144 Posts
LINQ - returning the date range for a list of records
Jan 04, 2013 06:16 PM|LINK
If i have a LINQ query that's querying a table with a date field, how do you return the first date and last date in the recordset?
For example, I want to return a list of employee time entries, and then also return the date range for those entries...
string time_flagInProcess = "I"; var GetInProcessEntriesqry = from t in db.timesheetView where t.time_flag == time_flagInProcess orderby t.first_name, t.workday select new { t.EmpID, EmpName = t.first_name + " " + t.last_name, t.workday, t.time_flag };Ok, this will return all records that have a flag of "I", and there would be lots of duplicates... (I'm excluding project information here; this is just for a report to display links to the time sheets for each person.)
Bob Jones ... 12/10/2012 ... I
Bob Jones ... 12/10/2012 ... I
Ann Smith ... 12/12/2012 ... I
Ann Smith ... 12/12/2012 ... I
Ann Smith ... 12/12/2012 ... I
Bob Jones ... 12/12/2012 ... I
Bob Jones ... 12/12/2012 ... I
Bob Jones ... 12/12/2012 ... I
Ann Smith ... 12/15/2012 ... I
Ann Smith ... 12/15/2012 ... I
...
...
Bob Jones ... 12/23/2012 ... I
Now, I don't want to return all records; I want to group them by person, which I do here:
var distinctInProcessResults = (from t in GetInProcessEntriesqry group t by t.EmpID into uniqueIds select uniqueIds.FirstOrDefault()) .OrderBy(x => x.EmpName); InprocessTimeSheetDataList.DataSource = distinctInProcessResults; InprocessTimeSheetDataList.DataBind();So, that gives me just one entry for each person for that parameter (which is "I" for "In Process Time Entries"), and it displays just the first date in the list.
Ann Smith (12/12/2012)
Bob Jones (12/10/2012)
But what I want is to display the dates of the oldest and newest entries. Does that make sense? So, it would return something like:
Ann Smith (12/12/2012-12/15/2012)
Bob Jones (12/10/2012-12/23/2012)
... to give me the date range for each person's in-process time entries.
Do I need to do a separate LINQ query to get the min and max date, and then store that as a variable?
Thanks in advance!
alex_brambil...
Participant
771 Points
278 Posts
Re: LINQ - returning the date range for a list of records
Jan 04, 2013 07:05 PM|LINK
It looks like you may have to make multiple passes through the data....
var emps = (from t in db.timesheetView where t.time_flag == time_flagInProcess select t).distinct()foreach (timesheetview emp in emps){//do something with the values, e.g. add them to a List<> and return thatDecker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ - returning the date range for a list of records
Jan 06, 2013 02:30 AM|LINK
Hello,
You can group by "Name" and then fetch the largest datetime and smallest datetime：
var result = from emp in xxxEmployees
group emp by emp.Name into temp
select new
{
EmployeeName = temp.Name,
StartDateTime = temp.Min(m=>m.DateTimeColumn),
EndDateTime = temp.Max(m=>m.DateTimeColumn)
}
And then you can use foreach to loop the result and do binding……
dezinnia
Member
18 Points
144 Posts
Re: LINQ - returning the date range for a list of records
Jan 07, 2013 06:39 PM|LINK
thanks Decker...
ok, so would I keep my earlier query to get the distinct results?
Where would you add the foreach?
Thank you!
dezinnia
Member
18 Points
144 Posts
Re: LINQ - returning the date range for a list of records
Jan 08, 2013 04:04 PM|LINK
I played with this a bit, and I got partway there...
foreach (DataListItem item in InprocessTimeSheetDataList.Items) { if ((item.ItemType == ListItemType.Item) || (item.ItemType == ListItemType.AlternatingItem)) { DataList InprocessTimeSheetDataList2 = InprocessTimeSheetDataList.Items[0].FindControl("InprocessTimeSheetDataList2") as DataList; HiddenField EmpID = InprocessTimeSheetDataList.Items[0].FindControl("EmpID") as HiddenField; int EmpIDint = Convert.ToInt32(EmpID.Value); var minmaxdates = from emp in GetInProcessEntriesqry //this is the original query that gives all the time entries where emp.empID == EmpIDint group emp by emp.empID into temp select new { StartDateTime = temp.Min(m => m.workday), EndDateTime = temp.Max(m => m.workday) }; InprocessTimeSheetDataList2.DataSource = minmaxdates; InprocessTimeSheetDataList2.DataBind(); //this is only giving me a date for the first person! } }So, it returns the correct date range for the first record, but no dates are listed after that first record. What might I be doing wrong?
dezinnia
Member
18 Points
144 Posts
Re: LINQ - returning the date range for a list of records
Jan 08, 2013 05:40 PM|LINK
DataList InprocessTimeSheetDataList2 = (DataList)item.FindControl("InprocessTimeSheetDataList2"); Label EmpIDLabel = (Label)item.FindControl("EmpIDLabel") as Label;and
DataList InprocessTimeSheetDataList2 = InprocessTimeSheetDataList.Items[0].FindControl("InprocessTimeSheetDataList2") as DataList; HiddenField EmpID = InprocessTimeSheetDataList.Items[0].FindControl("EmpID") as HiddenFieldI originally had it the second way, and the Foreach loop was stopping at the first record. But when I changed it to the first way, it worked.
What's the difference?
Thanks.
dezinnia
Member
18 Points
144 Posts
Re: LINQ - returning the date range for a list of records
Jan 08, 2013 05:50 PM|LINK
Oh, and also, another question... it's laying out as such:
But I'd like the date to follow the name, on the same line... Is that possible with a nested datalist?