Linq statement Where within a Where

Question

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));

Can you describe in words what you are trying to do? Return the customers that have enrolled in a specific category *outside* a specific date range? — Panagiotis Kanavos, Nov 22 '13 at 08:59
I want to return the Customers that were enrolled in a specific category and that enrollment is within a specific date. — Stephen, Nov 22 '13 at 09:15

score 2 · Answer 1 · answered Nov 22 '13 at 08:59

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

Gert Arnold · Accepted Answer · 2013-11-25T19:24:15.670

0

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.

edited Nov 25 '13 at 19:24

answered Nov 22 '13 at 09:52

Gert Arnold

105,341
31
202
291

My problem is I am building the query on the fly based on user selection. Is there a solution around this? – Stephen Nov 22 '13 at 10:04
[PredicatBuilder](http://www.albahari.com/nutshell/predicatebuilder.aspx) is _the_ tool for this. I haven't got time now, but if you wish I can give it a shot later. In the mean time you could take a look [here](http://stackoverflow.com/a/14622200/861716). – Gert Arnold Nov 22 '13 at 11:12
I have had a play around with this but unsure how to use it with the joined tables. If you did have time I would appreciate the help. – Stephen Nov 22 '13 at 16:00
Thanks for this. I am getting the following error now:Cannot implicitly convert type 'System.Collections.Generic.ICollection' to 'bool' on this line results = results.Where(c => c.CustomerEnrollment).Any(pred.Expand()); – Stephen Nov 25 '13 at 10:39
Sorry not sure which query you are referring to. The code works great until the last line. there is a closing bracket missing in your code by inserting this at the end i get the error does not contain a definition for any. – Stephen Nov 25 '13 at 10:51
OMG, I'm afraid it doesn't work in nested predicates. – Gert Arnold Nov 25 '13 at 12:53
Not the answer I was hoping for, but thanks for your time on this. – Stephen Nov 25 '13 at 14:23
I have been able to get this working using the first query above. Not ideal as I am repeating the date code in multiple where statements, but it works! – Stephen Nov 28 '13 at 17:43

Linq statement Where within a Where

2 Answers2