PredicateBuilder and nested predicates

Question

I'm trying to use the Predicate from Albahari to create a TSQL statement like:

select * from channel
where channel.VendorID IN (@vendorIDs)
AND channel.FranchiseID IN (@franchiseIDs)

or a predicate like : c => (c.VendorID = x || c.VendorID == x2 ...) && (c.FranchiseID == f || c.FranchiseID == f2 ...)

but I'm having troubles. Here is my attempt:

    var vendorPredicate = PredicateBuilder.False<Channel>();
    foreach (Vendor vendor in workOrderSessionData.SelectedVendors)
    {
        int tempId = vendor.VendorID;
        vendorPredicate = vendorPredicate.Or(c => c.VendorID == tempId);
    }

    var franchisePredicate = PredicateBuilder.False<Channel>();
    foreach (Franchise franchise in workOrderSessionData.SelectedFranchises)
    {
        int tempId = franchise.FranchiseID;
        franchisePredicate = franchisePredicate.Or(c => c.FranchiseID == tempId);
        // doesn't work franchisePredicate.Or(vendorPredicate);
    }

Channel.SelectByPredicate(franchisePredicate);

My table has 60,000 rows, so going to the database and selecting them all, then filtering is not an option. Channel is a LinqToSql entity. Also either the SelectedFranchises or the SelectedVendors can be empty, but not both at one time. EDIT: I need to distinct this list by the channel.Franchise.Name as well.. Maybe I should just use a stored procedure?

How would you do this?

Answer 1

To side step the whole predicate issue why not use the "Contains()" statement?

IE

var myResults =
    from c in Channel
    where
     workOrderSessionData.SelectedVendors.Select(sv => 
      sv.VendorID).Contains(c.VendorID)

     && workOrderSessionData.SelectedFranchises.Select(sf => 
      sf.FranchiseID).Contains(c.FranchiseID)
    select c;
Channel.SelectByPredicate(franchisePredicate);

Alternatively, to use the predicate method, you wouldn't want to join them with an "or" because the two conditions are "and"'d in your example SQL statement. Instead, just run them consecutively through a where clause. I don't know how your SelectByPredicate function works, but you may be successful following the same pattern with it:

var myResults = Channel.SelectByPredicate(franchisePredicate);
myResults = myResults.SelectByPredicate(vendorPredicate);

var myResults = Channel.Where(franchisePredicate).Where(vendorPredicate);

Update From Discussion in Comments

If what you want is to only match on vendor id/franchise id if the workordersessiondata contains at least one of those ids, you'd use the following logic:

List<int> VendorIDs = workOrderSessionData.SelectedVendors.Select(sv => 
    sv.VendorID).ToList();

List<int> FranchiseIDs = workOrderSessionData.SelectedFranchises.Select(sf => 
    sf.FranchiseID).ToList();

var myResults = Channel;

if(VendorIDs.Count > 0)
    myResults = MyResults.Where(c => VendorIDs.Contains(c.VendorID));

if(FranchiseIDs.Count > 0)
    myResults = MyResults.Where(c => FranchiseIDs.Contains(c.FranchiseID));

Answer 2

Well I just did this:

var prope = outerWhere.ToString();
if (prope.Equals("f => True") == false || prope.Equals("f => False") == false)
  query = query.Where(outerWhere);

Maybe tho the cleanest of all versions...