Multiple level where clause clause with Linq
Let's say I have some filter criteria passed to my application through an array of CustomerFilter objects where I need to run a query and return results based on those filters from a Linq to Entities query.
So in this case, the client will pass me to an array of CustomerFilter objects above the service call.
Filter object:
class CustomerFilter
{
public string CustomerID;
public int[] LocationID;
}
Sample data:
CustomerID LocationID
1 1
2
3
4
2 2
3
4
I can easily create filtering requests on external CustomerID as shown below.
Query:
public void GetCustomerResults(List<CustomerFilter> accounts)
{
List<string> customer = (from a in accounts select a.CustomerID).ToList();
var locations = ctx.Portal_SurveyLocations
.Where(w => customer.Contains(w.CustNum))
.OrderBy(o => o.LocationKey);
}
So I can filter the external criteria, but I'm not sure how to filter by multiple location IDs for each CustomerID. Obviously, simply placing an OR clause will give incorrect results as it will pull in other CustomerIDs with matching LocationIDs.
Any ideas on how to accomplish this multi-level filter if the CustomerFilter object is passed to?
source to share
Small improvement. Basically, we use a combination Any
to move subcategories to achieve the desired result.
var locations = ctx.Portal_SurveyLocations
.Where(w => accounts.Any(a => a.CustomerID == w.CustNum &&
a.LocationID.Any(l => w.LocationKey == l)))
.OrderBy(o => o.LocationKey);
source to share
For a quick search (using the Contains
on List
not very fast), you can create a dictionary of hash sets of filter objects.
The dictionary will contain one element for each customer, and the value in that element will be a hash set of locations. When you use it, you first check if the client is in the dictionary, then you check if the location is in the hash setting for that client:
public void GetCustomerResults(List<CustomerFilter> accounts) {
Dictionary<string, HashSet<int>> lookup =
accounts.ToDictionary(a => a.CustomerID, a => new HashSet<int>(a.LocationID));
var locations =
ctx.Portal_SurveyLocations
.Where(w =>
lookup.ContainsKey(w.CustNum) &&
lookup[w.CustNum].Contains(w.LocationKey))
.OrderBy(o => o.LocationKey);
}
Both the dictionary and the hash set have complexity O(1)
to find an element, so the whole operation gets complexity O(n+m)
, where n
is the number of filters and a m
is the number of elements in Portal_SurveyLocations
.
source to share