CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-entities
StuartMorgan
StuartMorgan

Reputation: 678

Linq to SQL to select rows whose DateTime field falls within any of a list of date ranges

I have a list of DateRange objects, which have StartDate and EndDate properties. I need to select all the rows from a table in my database which have a DateTime field that falls within any of those DateRanges. So I'd like to do something like this:

// Using an IQueryable<DateRange> called dateRanges
var tests = from test in dbContext.Tests
            where dateRanges.Any(range => range.StartDate <= test.Date && range.EndDate >= test.Date)
            select test

This gives me an error because DateRange is not a primitive type and so LINQ to SQL can't use it. Is there any other way to do what I'm trying to do here? Been googling and trying different things for a couple hours, I feel like I'm very close but can't quite get there. Thanks in advance.

Upvotes: 0

Views: 2263

Answers (3)

StuartMorgan
StuartMorgan

Reputation: 678

The way I decided to do this in order to guarantee that no unnecessary data was being transferred was to create a stored procedure called GetTestsInDateRange on my database which takes in a start and end date and returns all of the tests that are in that range. Then in my code I'm looping through my list of DateRanges, calling the stored procedure once for each DateRange, and unioning the results together.

IEnumerable<Test> tests = new List<Test>();
foreach (DateRange range in selectedDateRanges)
{
    tests = tests.Union(dbContext.GetTestsInDateRange(range.StartDate, range.EndDate));
}

Whether that's the ideal solution or not I don't know (probably depends on how many DateRanges were selected and therefore how many separate calls it's making to the database), but it's working well for my scenario.

Upvotes: 0

sh1ng
sh1ng

Reputation: 2973

Try to use Linq2Sql with dynamic query like

(p => value1.StartDate <= p.Date && value1.EndDate >= p.Date) || (p => value2.StartDate <= p.Date && value2.EndDate >= p.Date)

Example: Linq2SQL "or/and" operators (ANDed / ORed conditions)

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Other that crafting your own SQL statement by concatenating the criteria for each range, one way to partially limit the results that you get back would be to use the min and max dates of the ranges, then filtering further in Linq-To-Objects:

var minStartDate = dateRanges.Min(r => r.StartDate);
var maxEndDate = dateRanges.Max(r => r.EndDate);
var tests = (from test in dbContext.Tests
            where minStartDate <= test.Date && maxEndDate >= test.Date
            select test)
            .AsEnumerable()  // change to Linq-To-Objects
            .Where(test => dateRanges.Any(range => range.StartDate <= test.Date 
                                                   && range.EndDate >= test.Date));

This would work perfectly for one range, and would be only marginally worse unless your ranges had huge gaps in between them.

Upvotes: -1

Related Questions