Using LINQ to Search Between Two Dates and Times
I am currently using 2 approaches when doing searches. I am running a stored process that fetches information between two dates. Followed by a LINQ search in these results.
Basically the user selects the start and end date with additional time options. For example, the user selects the start date 01/01/2014 from 11:00 to 01/03/2014 3pm. The problem is that the search is done in 2 steps and it will search all lines between the start and end date. LINQ then looks for that time, but then holds it back between 11:00 and 15:00 every day.
For some reason I can't seem to get my head around how to fix this problem. This is the code as in my application ...
var model = db.Results(startDate, endDate)
.Where(r => startTime == null || r.A_Time >= startTime)
.Where(r => endTime == null || r.A_Time <= endTime)
.OrderBy(r => r.A_Time)
.OrderBy(r => r.A_Date)
.Take(1000)
);
As requested, the db structure looks like this.
TABLE [dbo].[TablewithInfo](
[A_Date] [date] NOT NULL,
[A_Time] [time](7) NOT NULL,
[Site] [varchar](50) NOT NULL,
[Event] [varchar](50) NULL,
[Client] [varchar](25) NOT NULL,
[User] [varchar](50) NULL,
[Host] [varchar](50) NOT NULL,
[Info] [varchar](2800) NOT NULL)
source to share
You can use logic like this:
.Where(row => startTime == null || row.A_Date > startDate || (row.A_Date == startDate && row.A_Time >= startTime))
Thus, either the time is not specified, the date is after the current date (time does not matter), or it is the same as the date and time later.
source to share
What the following query does is redundant but necessary:
var sdt = CombineParameters(startDate, startTime);
var edt = CombineParameters(endDate, endTime);
var model = db.Results(startDate, endDate)
.Where(r => (CombineParameters(r.A_Date, r.A_Time) >= sdt))
.Where(r => (CombineParameters(r.A_Date, r.A_Time) <= edt))
.OrderBy(r => r.A_Time)
.OrderBy(r => r.A_Date)
.Take(1000)
);
// Date and Time classes below are only examples, they do not exist
// Prolly you are using string ;)
private DateTime CombineParameters(Date dt, Time tt)
{
// some way to combine dt and tt to DateTime
// you might have to do NULL checks here etc.
}
source to share