Loop over parameters to generate LINQ at runtime

Question

public class Command
{
    public int CommandId { get; set; }
    public string Title { get; set; }
    public virtual ICollection<CommandPart> CommandParts { get; set; }
}

public class CommandPart
{
    public int CommandPartID { get; set; }
    public string part { get; set; }
    public virtual ICollection<Command> command { get; set; }
}

public ViewResult Filter(string myString)
{
    var foo = myString.Split(Convert.ToChar("+"));
    var a = foo[0];
    var b = foo[1];

    var query =
    db.Commands.Where(
        c =>
        c.CommandParts.Any(p => p.part == a) 
        && (c.CommandParts.Any(p2 => p2.part == b))).ToList();

    return View(query.ToList());
}

The query above works as expected when there are exactly two items in foo. I would like to be able to loop over foo to create the query. Here is my attempt, but it is not working:

        var foo = myString.Split(Convert.ToChar("+"));
        IQueryable<Command> query = db.Commands;

        foreach (var keyword in foo)
        {
            query = query.Where(c => c.CommandParts.Any(p => p.part == keyword));
        }

This is an ASP.NET MVC3 project using Entity Framework. How can I write the query so that it is generated at runtime?

possible duplicate of [Create Predicate Dynamically](http://stackoverflow.com/questions/6383825/how-to-create-predicate-dynamically/6383937#6383937) — Eranga, Jul 22 '11 at 04:57

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

Look at PredicateBuilder. It's a small helper class that encapsulates some LINQ Expressions that allow you to dynamically build up your Where predicate clause. You can AND and OR clauses together, and nest them pretty deep.

So you could do something like this (pseudo-code):

var predicate = PredicateBuilder.True<Command>(); //use True for ANDs
foreach(var keyword in foo) {
  predicate = predicate.And(c => c.CommandParts.Any(p => p.part==keyword));
}
var results = db.Commands.Where(predicate);

I missed that you're using EF, so you'll need to download LINQKit (a NuGet that you can download, too), and use its 'AsExandable' extension. See the PredicateBuilder page about this (about half-way down).

Hope this helps!

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 22 '11 at 05:30

David Hoerster

28,421
8
67
102

Thanks, @david-hoerster That did the trick. I did have to assign keyword to a string variable inside the foreach loop and then assign reference that variable in the predicate creation statement. I don't understand why that made it work and will post a follow-up question. – Ross Fuhrman Jul 24 '11 at 05:02
Glad I could help. Assigning the keyword to a temp variable is something I forgot. The predicate isn't evaluated immediately...only when iterated, so the keyword's value isn't copied; just a reference to keyword. Look up deferred execution for linq. That should explain it. – David Hoerster Jul 24 '11 at 10:01

Loop over parameters to generate LINQ at runtime

1 Answers1