Advanced database search using linq-to-sql help

Question

I have a database table that contains "commands" and "states". Each command can have several states, and the user is able to configure this when search. For example, the command could be "Run" and it could have two states: "Fast" and "Slow".

I want to search my table for all commands called "Run" with "Fast" or "Slow". This is pretty simple to do:

var results = from t in table
              where t.Command == "Run" &&
              (t.State == "Fast" || t.State == "Slow")
              return t;

However the user could also search command "Walk" with state "Fast", and so the query would look like:

    var results = from t in table
                  where (t.Command == "Run" &&
                        (t.State == "Fast" || t.State == "Slow")) ||
                  (t.Command == "Walk" &&
                   t.State == "Fast")
                  return t;

There is a potential for as many searches like this, and I'm wondering how to combine them in a loop of sorts.

I can't do this:

foreach(var command in commands)
{
    foreach(var state in command.states)
    {
        results = from t in table
                  where t.Command == command.Command &&
                  t.State == state;
    }
}

because once it searches for "Run", "Walk" would be left out of results so asking for "Walk" would result in NO results at all.

Does anyone know of a good way to do this?

StriplingWarrior · Accepted Answer · 2010-11-18 16:34:19Z

7

Use Joe Albahari's PredicateBuilder to build a predicate:

var predicate = PredicateBuilder.False<Entry>();
foreach(var command in commands)
{
    foreach(var state in command.states)
    {
        predicate = predicate.Or(p => p.Command == command.Command && p.State == state);
    }
}
var query = table.Where(predicate);

Or a more LINQ-heavy version:

var commandStates = from c in commands
                    from s in c.states
                    select new {c.Command, State = s};
var predicate = commandStates.Aggregate(
    PredicateBuilder.False<Entry>(),
    (pred, e) => pred.Or(p => p.Command == e.Command && p.State == e.state));
var query = table.Where(predicate);

edited Nov 18, 2010 at 16:34

answered Nov 18, 2010 at 16:28

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jim tollan Over a year ago

this is very timely actually (the predicate builder) as i've hobbled along of late aggregating where clauses - nice find. how's your success faired with it??

StriplingWarrior Over a year ago

@jim: I actually haven't had a need for it yet. Most of what we do is either simple enough not to need it or advanced enough that I have to build the whole expression tree by hand anyway. If you're using PredicateBuilder, you might want to look at LinqKit generally. It has some handy features for invoking predicates and things.

jim tollan Over a year ago

yes, hand rolled expression trees have been taking more time than i care to admit to of late. i'll have a look at the whole kit for ideas,... thanks

Collectives™ on Stack Overflow

Advanced database search using linq-to-sql help

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related