CODEWITHSUNDEEP
c#sqllinq
Alexender
Alexender

Reputation: 61

Using LINQ to search between two Dates and Times

I'm currently using 2 approaches in doing a search. I run a stored proc that pulls in information between two dates. Followed by a LINQ search within those results.

Basically the user picks a start and an end date with optional Time parameters. For instance the user selects a start date of 01/01/2014 11am to 01/03/2014 3pm. The problem is since the search is in 2 stages it'll search for all rows between the start and end date. Then the LINQ searches those times but then restrains it to between 11am and 3pm of each day.

For some reason I can't seem to wrap my head around how to solve this issue. This is the code how in my app...

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 is as follows.

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)

Upvotes: 3

Views: 6358

Answers (2)

Abhinav
Abhinav

Reputation: 2085

What the following query is doing, 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.
}

Upvotes: 1

Kyle W
Kyle W

Reputation: 3752

You can use logic like this:

.Where(row => startTime == null || row.A_Date > startDate || (row.A_Date == startDate && row.A_Time >= startTime))

So either no time specified, the date is after the current date (time doesn't matter), or it's the same date and the time is later.

Upvotes: 4

Related Questions