Dynamic LINQ on a collection?

Question

Hi there,

I've a project which ask me to do such a BUG search engine but which is all dynamic. I mean I can have about 0 to 9 main "group" which have inside something like an infinite possibility of "where" with "OR" or "AND". First thing we think was to use Dynamic Linq which provide a good alternative to build dynamic query. All this using EF with an homemade wrapper.

Probleme : I'm not able to access to a "Collection". I mean, I can easly access to a referenced object (Like Customer.State.StateName = "New-York" OR Custoemr.State.StateName = "Quebec" ) but I can't find a way to acces to something like : "Customer.Orders.OrderID = 2 OR Customer.Orders.OrderID = 3". I can easly figure out this its because its a collection, but how can I do this?

Please help me out!!

** Sorry for my english !!

---

Update

I'm not clear enought I think, sorry its because im french...

My problem its because nothing is static. Its a candidat search engine for a recruting compagny that place candidats into an enterprise. In a page where manager can search candidat, he can "parse" by : Domain(s) (Jobs), City(ies) or many other that user have filled up when he register. All this in format (if it were in SQL) :

[...] WHERE (domaine.domainID = 3 OR domaine.domainID = 5 OR domaine.domainID = 23) AND (cities.cityID = 4, cities.city = 32) [...]

So i can't do this with a normal LINQ format like :

Candidat.Domaines.Where(domain => domain.DomainID == 3 || domain.DomainID == 5 || domain.DomainID == 23);

Even the operator in the paretheses are dynamic ("AND" or "OR")! That why we trying to use Dynamic Linq because its a lot more flexible.

Hope its more easy to understand my problem ...

---

Update 2 Here's my method

private string BuildDomainsWhereClause() {
        StringBuilder theWhere = new StringBuilder();

        if (this.Domaines.NumberOfIDs > 0) {
            theWhere.Append("( ");

            theWhere.Append(string.Format("Domaines.Where( "));
            foreach (int i in this.Domaines.ListOfIDs) {
                if (this.Domaines.ListOfIDs.IndexOf(i) > 0) {
                    theWhere.Append(string.Format(" {0} ", this.DispoJours.AndOr == AndOrEnum.And ? "&&" : "||"));
                }
                theWhere.Append(string.Format("DomaineId == {0}", i));
            }
            theWhere.Append(" ))");
        }

        return theWhere.ToString();
    }

It works great instead that it "Not return a boolean". So how should I? Error : "Expression of type 'Boolean' expected".

At the end, it returns something like : "( Domaines.Where( DomaineId == 2 && DomaineId == 3 && DomaineId == 4 && DomaineId == 5 ))." which is added to my LINQ Query :

var queryWithWhere = from c in m_context.Candidats.Where(WHERE)
                                     select c;

Dont forget that there's like 7 or 8 more "possible" added things to search in ... Any ideas?

Answer 1

Assuming that Customer.Orders returns a collection, this is exactly why you can't just call a property of it.

In order to use LINQ to get the Order you're looking for, you'd need to know the OrderID (Or some other property) in which case you can do:

Customer.Orders.Find(order => order.OrderID == 2);

Edit: To add the expression to find id 2 or 3 in this way:

Customer.Orders.FindAll(order => order.OrderID == 2 || order.OrderID == 3);

Answer 2

Do I understand that right, that both Customers is a collection and Orders is a collection while State (obviously) is a Property?

var q = from a in Customer
    from b in a.Orders
    where b.ID == 2
              || b.ID == 3
    select b;

would work I guess.

edit:

I did partly something like that. It's been too long to be exactly sure how I did it, but I can tell you, that I was using

public static IQueryable Where(this IQueryable source, string predicate, params object[] values);

from DynamicQueryable class.

this.CountrySitesObject.Sites.AsQueryable().Where(w.WhereQuery, w.WhereParameters) (copy from my code).

Answer 3

If you step back and ask what does the customer want to do.

Filter bug information.

Why not export the data to excel or point excel to the SQL Table. It is not as much fun to build, but you would be done in a couple of hours, instead of days or weeks. :)

Answer 4

What you need to do here, is build a LambdaExpression (more specifically an Expression<Func<T, bool>>). You cannot use a string. You can build a simple expression like this:

ParameterExpression p = Expression.Parameter(typeof(Domaine), "domaine");
Expression<Func<Domaine, bool>> wherePredicate = 
  Expression.Lambda<Func<Domaine, bool>>(
    Expression.Or(
      Expression.Equal(
        Expression.Property(p, "DomainID"),
        Expression.Constant(10)),
      Expression.Equal(
        Expression.Property(p, "DomainID"),
        Expression.Constant(11))
      ), p);

i.e.,

domaine.DomainID = 10 || domaine.DomainID = 11

Not very readable if you need to do this by hand.

There's a sample of a fully operational expression parser that will actually do this for you based on a string in C# Samples for Visual Studio 2008 at MSDN Code Gallery, under DynamicQuery. (The LinqDataSource control uses a slightly modified version of this sample internally.)

Answer 5

Finaly i've got it exactly the way I want.

private string BuildDomainsWhereClause() {
        StringBuilder theWhere = new StringBuilder();

        if (this.Domains.NumberOfIDs > 0) {
            theWhere.Append("( ");

            foreach (int i in this.Domains.ListOfIDs) {
                if (this.Domains.ListOfIDs.IndexOf(i) > 0) {
                    theWhere.Append(string.Format(" {0} ", this.Domains.AndOr == AndOrEnum.And ? "&&" : "||"));
                }
                theWhere.Append(string.Format("Domains.Any(IdDomaine== {0})", i));
            }
            theWhere.Append(" )");
        }

        return theWhere.ToString();
    }

Which produce something like : "( DispoJours.Any(IdDispo == 3) && DispoJours.Any(IdDispo == 5) )".

All my other "Where builder" will do the same things with a "&&" between which give the correct result.

And later :

var queryWithWhere = from c in m_context.Candidats.Where(WHERE)
                     select c;

WHOOOHOOO !! Thanks folks. Were very usefull! Love this website!

---

Update

Don't forget that i use Dynamic Linq on this query. It's not a normal LINQ query.