CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
quin16
quin16

Reputation: 525

Entity framework - select by multiple conditions in same column - referenced table

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.

Upvotes: 3

Views: 5880

Answers (2)

Rup
Rup

Reputation: 34408

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.

Upvotes: 0

Aducci
Aducci

Reputation: 26664

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

Upvotes: 1

Related Questions