CODEWITHSUNDEEP
linq
Stephen
Stephen

Reputation: 803

Linq statement Where within a Where

I am building a query tool for use by non technical staff to retrieve records from the database.

I have a form with various drop downs which can be selected by the user depending on what they are looking for.

I have come across a problem where my query is returning records that do not match the users selection.

I believe this is only happening when I am querying the joined tables.

I have the following:

results = results.Where(c => c.CustomerEnrollment
                  .Where(x => x.CustomerCategoryID == CustomerCategoryID)
                  .Any());

results = results.Where(c => c.CustomerEnrollment
              .Where(x => x.StartDate <= DateRangeStart && x.EndDate >= DateRangeStart)
              .Any());

This will return results for the correct category but not within the specified date range.

I have also tried:

results = results.Where(c => c.CustomerEnrollment
                          .Any(x => x.CustomerCategoryID == CustomerCategoryID));

Upvotes: 2

Views: 1060

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109099

Your query returns categories that have any CustomerEnrollment having their Id, and also have any CustomerEnrollment in the the required data range, but these CustomerEnrollments are not necessarily the same.

To make sure that you get categories with CustomerEnrollments that fulfill both conditions you have to do:

results = results.Where(c => c.CustomerEnrollment
                  .Where(x => x.CustomerCategoryID == CustomerCategoryID
                           && x.StartDate <= DateRangeStart
                           && x.EndDate >= DateRangeStart)
                  .Any());

With PredicateBuilder you can parametrize the conditions:

using LinqKit;
...

var pred = Predicate.True<CustomerEnrollment>();

if (CustomerCategoryID > 0)
    pred = pred.And(c => c.CustomerCategoryID == CustomerCategoryID);

if (DateRangeStart.HasValue)
    pred = pred.And(c => c.StartDate <= DateRangeStart
                      && c.EndDate >= DateRangeStart);

results = results.AsExpandable()
                 .Where(c => c.CustomerEnrollment.AsQueryable()
                     .Any(pred));

The combination of .AsExpandable() and .AsQueryable() appears to be the only way to avoid exceptions.

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Try changing your date range check as;

Change:

x => x.StartDate <= DateRangeStart && x.EndDate >= DateRangeStart

To:

//StartDate should be greater than or equal to
//EndDate should be less than or equal to
//Also you are using same variable DateRangeStart to check start and end
x => x.StartDate >= DateRangeStart && x.EndDate <= DateRangeEnd

Upvotes: 2

Related Questions