LINQ Query with Array input and variable Where Statements - Advice

Question

I would like to query data given an array to filter by via WCF Data Services using the Silverlight Client API. Basically, I want to query Employees given a list (array) of States.

I'm thinking something like this:

public IQueryable<Employee> Load(string[] states)
{
     foreach (var x in states)
     {
           // LINQ query here with 1 to N .Where statements
           return from e in Context.Employees
           .Where(...)
     }
}

So let's say my array has 2 items in it, i.e. I want to query by 2 states, I would do something like this manually:

return from e in Context.Employees
    .Where(e => e.State== states[0] || e.State == states[1])));

Any advice will be greatly appreciated!

Are you using LINQ to SQL or entity framework or plain LINQ? — Rune FS
– Rune FS, Commented Jul 15, 2010 at 19:12
Edited original post - using WCF Data Services (Astoria 1.0) with Silverlight 3.0 (Silverlight Client libraries). — user118190
– user118190, Commented Jul 15, 2010 at 19:22

Daniel Brückner · Accepted Answer · 2010-07-15 21:15:29Z

5

You can dynamically build the expression tree for the condition.

var parameter = Expression.Parameter(typeof(Employee), "employee");

Expression condition = Expression.Constant(false);

foreach (var state in states)
{
    condition = Expression.OrElse(
        condition,
        Expression.Equal(
            Expression.Property(parameter, "State"),
            Expression.Constant(state)));
}

var expression = Expression.Lambda<Func<Employee, Boolean>>(condition, parameter);

And then just perform the call.

var result = Context.Employees.Where(expression);

I am not 100% sure if this will work out of the box for you but I hope the general idea helps.

edited Jul 15, 2010 at 21:15

answered Jul 15, 2010 at 19:09

Daniel Brückner

59.8k16 gold badges103 silver badges144 bronze badges

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

3 Comments

Yury Tarabanko Over a year ago

Probably not Or but OrElse. And Parameter(typeof(Employee)...

Daniel Brückner Over a year ago

Thanks, you are right - I wrote the code using a list of String for testing and forgot to change it to Employee. The difference between Or and OrElse should not matter but one would of course usually use a conditional or instead of the normal or.

Max Over a year ago

Good comment @DanielBrückner but Equal method makes a case-sensitive comparison, instead how to perform a case-insensitive comparison? Expression.Contains() method does not exists.

gmn · Accepted Answer · 2010-07-15 19:02:12Z

1

Context.Employees.ToList().Where(x => states.Contains(x.State))

edited Jul 15, 2010 at 19:02

answered Jul 15, 2010 at 18:51

gmn

4,3194 gold badges28 silver badges48 bronze badges

8 Comments

Daniel Brückner Over a year ago

This will not work with EntityFramework or LINQ to SQL. I assume one if this is used because the parameter is IQeryable and not IEnumerable.

gmn Over a year ago

it should do if you call tolist() before the where.

gmn Over a year ago

You may want to consider performance if this is a large table though as all employees would be read in, then the filter applied

Daniel Brückner Over a year ago

He will have to test it but I am quite sure that neither EF nor L2S is capable of translating a complex object like a list into a matching SQL statement - at least prior to .NET 4.0.

gmn Over a year ago

your right(I think), but tolist converts it into a normal list object, so even if ef or l2s don't support it you should be dealing with L2o at that point. The query has already been executed on the db when running the filter. I would test it without tolist first as that would be more efficient if it worked.

|

Tim Coker · Accepted Answer · 2010-07-15 19:09:27Z

0

Here's a runnable example that does what you want, I think? Given a list of states, it will give you the employees that are in those states.

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<string> states = new List<string>();
            states.Add("SC");
            states.Add("TX");
            states.Add("NC");

            List<Employee> emps = new List<Employee>();
            emps.Add(new Employee() { State = "GA", Name = "Bill" });
            emps.Add(new Employee() { State = "TX", Name = "John" });
            emps.Add(new Employee() { State = "SC", Name = "Mary" });

            //Here's where the work is done.  The rest is fluff...
            var empsinstates = from e in emps where states.Contains(e.State) select e;

            foreach (var e in empsinstates)
            {
                Console.WriteLine(e.Name + " " + e.State);
            }
            Console.Read();
        }
    }
    class Employee
    {
        public string State;
        public string Name;
    }
}

answered Jul 15, 2010 at 19:09

Tim Coker

6,5242 gold badges34 silver badges65 bronze badges

2 Comments

Daniel Brückner Over a year ago

This works with LINQ to Object but (quite surely) not with Entity Framework or LINQ to SQL.

user118190 Over a year ago

This is what I am getting: {Error translating Linq expression to URI: The method 'Contains' is not supported.}

Collectives™ on Stack Overflow

LINQ Query with Array input and variable Where Statements - Advice

3 Answers 3

3 Comments

8 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related