Dynamic LINQ on a collection?

Question

I've a project which ask me to do such a BIG 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?

Heer

Answer 1

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

Bought

Thanks a lot. Its not exactly what i've expect, but it looks great. I've still got to tricks it up a little bit to make it perfectly dynamic. But thanks, very usefull. – Byram

Answer 2

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.

Heer

If you're going to be building actual queries with strings, make sure to parametrize the strings, or you could be susceptible to SQL injection attacks. Although it might be the easier way, sometimes it's not necessarily the best/safest way. If you use LINQ for your queries, it automatically parametrizes queries for you. – Excited

Thanks a lot to prevents me. Now that we're not using anymore SQL, I've forget that dangerous things. Thanks. But in my case, user do not write "1", he selects it in a checkboxlist so the danger isn't realy there. I will do it anyway, but were not realy a danger. – Byram

Answer 3

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

Macaroon

Answer 4

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<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values);

From DynamicQueryable class.

this.CountrySitesObject.Sites.AsQueryable().Where(w.WhereQuery, w.WhereParameters) 

(copied from my code).

Brozak

Yes it works great. But not when you don't realy know how many "b.Id" you have to search for. Even not if its a "||" or "&&" between. – Byram

I will try this too. But i'm on a solution which seems to works. I'll come back later to give you news. – Byram

Answer 5

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. :)

Doelling

Yea i know!! But we thought that Dynamic LINQ were doin' exactly what we were searching for ... – Byram