3

Example scenario:
Two tables: order and orderItem, relationship One to Many.
I want to select all orders that have at least one orderItem with price 100 and at least one orderItem with price 200. I can do it like this: 

var orders = (from o in kontextdbs.orders
              join oi in kontextdbs.order_item on o.id equals oi.order_id
              join oi2 in kontextdbs.order_item on o.id equals oi2.order_id
              where oi.price == 100 && oi2.price  == 200
              select o).Distinct();

But what if those conditions are user generated? So I dont know how many conditions there will be.

Aducci
  • 26,101
  • 8
  • 63
  • 67
quin16
  • 515
  • 2
  • 8
  • 15

2 Answers2

1

You need to loop through all the values using a Where and Any method like this:

List<int> values= new List() { 100, 200 };

var orders = from o in kontextdbs.orders
             select o;
foreach(int value in values)
{    
      int tmpValue = value;
      orders = orders.Where(x => kontextdbs.order_item.Where(oi => x.id == oi.order_id)
                                                      .Any(oi => oi.price == tmpValue));    
}

orders = orders.Distinct();
Aducci
  • 26,101
  • 8
  • 63
  • 67
0
List<int> orderValues = new List() { 100, 200 };

ObjectQuery<Order> orders = kontextdbs.Orders;
foreach(int value in orderValues) {
    orders = (ObjectQuery<Order>)(from o in orders
                                  join oi in kontextdbs.order_item
                                    on o.id equals oi.order_id
                                  where oi.price == value
                                  select o);
}
orders = orders.Distinct();

ought to work, or at least that's the general pattern - you can apply extra queries to the IObjectQueryables at each stage.

Note that in my experience generating dynamic queries like this with EF gives terrible performance, unfortunately - it spends a few seconds compiling each one into SQL the first time it gets a specific pattern. If the number of order values is fairly stable though then this particular query ought to work OK.

Rup
  • 33,765
  • 9
  • 83
  • 112
  • This finds all orders that have price 100 OR 200. – quin16 Apr 21 '12 at 08:14
  • No, I've just tested this and it works fine for me. I'm composing an extra filter on top of the object query each time - how could that be used to construct an OR? Aducci is doing exactly the same - if he gets AND not OR can you explain how this code wouldn't? (I probably like his answer better - I shouldn't have tried to use the SQL-like syntax myself and stuck to what I know - but this works fine.) – Rup Apr 22 '12 at 08:33