Entity framework - select by multiple conditions in same column - Many to Many

Question

I asked very similar question here
Entity framework - select by multiple conditions in same column - referenced table

Now relationship is Many to Many. So I have 3 tables: order, items and orderItem (items is a Junction table) Again.. I want to select all orders that have at least one orderItem with price 100 and at least one orderItem with price 200

I really don't know how to edit this for that scenario.

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)
}

score 1 · Answer 1 · answered Apr 21 '12 at 18:21

1

First, you can just declare this:

var orders = kontextdbs.orders;

There's no need to use an actual select statement.

To answer your actual question, you should be able to do this:

foreach(int value in values)
{
    var temp = value;

    orders = orders.Where(o => o.order_item.Any(oi => oi.price == temp));
}

This assumes that you have a navigation property from order to order_item.

answered Apr 21 '12 at 18:21

Adam Robinson

182,639
35
285
343

Well.. I don't have navigation property from order to order_item. How can I manually join the items table and then join order_item table? – quin16 Apr 21 '12 at 18:44
Why don't you have a navigation property? – Adam Robinson Apr 21 '12 at 19:16
@AdamRobinson - probably because he didn't define a foreign key. – Erik Funkenbusch Apr 21 '12 at 19:21
Actually this scenario was hypothetical.(I should have written it, I am sorry) I only have order and order_item tables with One to Many Relationship. I just wanted to know how would this look with many to many. So EF would create navigation property? Thats good. But still.. I would like to know how could I do this without it. (I am just curious and I really want to know that ) – quin16 Apr 21 '12 at 19:31

score 1 · Accepted Answer · answered Apr 21 '12 at 19:22

Here is the modified version

foreach(int value in values)
{    
      int tmpValue = value;
      orders = orders.Where(x => (from oi in kontextdbs.order_item
                                 join i in kontextdbs.items on oi.item_id equals i.id
                                 where x.id == oi.order_id
                                 select i).Any(y => y.price == tmpValue));    
}

Martin Bliss · Answer 3 · 2012-04-21T20:09:58.473

Updated since you don't have a navigation property (you should fix that.)

If you want to select orders that have a price of 100 OR 200, you can use this:

var qMyOrders = 
from a in kontextdbs.orders
join b in kontextdbs.order_items on a.id equals b.order_id
where values.Contains(b.price)
select a;

If you need to find orders whose order items match ALL prices in an array, say 100 AND 200, use the following:

var qMyOrders = 
from a in kontextdbs.orders
from b in kontextdbs.order_items.Where( c => c.order_id == a.id )
where values.All(d => b.price == d)
select a;

Entity framework - select by multiple conditions in same column - Many to Many

3 Answers3