Filtering on subqueries using Dynamic LINQ

Question

I'm working on a large application which is using Dynamic LINQ Library for filtering .

All of the classes in my data-access-layer are derived from a common ancestor DALAncestor which, among other things, defines method GetData: public abstract List GetData(Filter filter)

An implementation of this method for a Customer would look like this (simplified):

public override List<Entities.Customer> GetData(Filter filter) {
  var customers = from c in db.Customers
                  select new Entites.Customer { 
                    ID = c.ID,
                    FullName = c.Name,
                    Country = c.Country
                  };

   return = filter.Apply(customers).ToList();
}

"Apply" is a method which takes a collection of conditions and applies all of them using Dynamic LINQ Libary.

Method GetData is called like this:

private void DemoCallGetDataMethod() {
  var filter = new Filter();
  filter.AddCondition("Country", "Austria");

  var list = myCustomerDAL.GetData(filter);
  // do something
}

SQL Server gets a select statment:

SELECT [t0].ID, [t0].Name, [t0].Country
  FROM Customer [t0]
 WHERE [t0].Country = @p0

(@p0 is a parameter with its value set to "Austria").

Everything works great, if I want to filter values only on the "top" (master) level. However, since I build my filter on user's input on the filter form, there are cases in which users want to filter by a value in detail level (i.e. columns to be filtered by are known only after a user clicks "Search").

I cannot find a solution for this: "Get all of the customers from Austria, who bought Pepsi".

I've tried several things, none of them seems to be working. The basic idea is:
1. Add support for CONTAINS to DynamicLibrary
2. Change my code to

public override List<Entities.Customer> GetData(Filter filter) {
  var customers = from c in db.Customers
                  select new Entites.Customer { 
                    ID = c.ID,
                    FullName = c.Name,
                    Country = c.Country,
                    Items = c.Order.SelectMany(o => o.Item).Select(i => i.ItemId).ToList()
                  };

  return = filter.Apply(customers).ToList();
}

private void DemoCallGetDataMethod() {
  var filter = new Filter();
  filter.AddCondition("Country","=", "Austria");
  filter.AddCondition("Items","Contains", "11"); // 11 = Id for Pepsi

  var list = myCustomerDAL.GetData(filter);

  // do something
}

The exepction thrown is:

System.InvalidOperationException: No generic method 'Contains' on type 'System.Linq.Enumerable' is compatible with the supplied type arguments and arguments. No type arguments should be provided if the method is non-generic.

Does anyone have any idea what I'm doing wrong? Or am I just going in wrong direction and I should try a some other approach? Which? :)

EDITED: changed my example

Answer 1

@user182630 I want to be able to filter by values from subselects in general, not only for a specific case. The approach from that article works only if you hard code you filters, which is not a thing I can do. – Marko Juvančič 9 mins ago

Have a read through this article http://csharpindepth.com/articles/chapter5/closures.aspx

Answer 2

I use PredicateBuilder for dynamic queries in my applications.

Predicate Builder

In your case I imagine it will be something like:

 string MyCountry = "Austria";
 string MyProductId = 11

 var Predicate = PredicateBuilder.True<db.Customers>();
 Predicate = Predicate.And(p=>p.Country ==MyCountry && p.Order.SelectMany(o=>o.Item).Where(i=>i.id == 11).Count() >0);

 var list = Customers.Where(Predicate).Select(s=>s).ToList();