c# - Replacing a query in a foreach with a join statement -

i have table in db called dish , 1 table called ingredient. have table called dishingredient represents relationship between dish , 1 of ingredient or dish. in dishingredient have column called 'table', stores if relationship between dish , dish, or dish , ingredient.

here c# code:

list<int> dish = (from x in db.dishingredient                   join y in db.dish on x.dish_ing_dish equals y.dish_id                   x.dish_ing_ing == ing_id && x.table == "ingredient"                   select y.dish_id).tolist();  (int = 0; < dish.count; i++) {     int dish_id = dish[i];     list<int> dish_in_dish = (from x in db.dishingredient                               join y in db.dish on x.dish_ing_dish equals y.dish_id                               x.dish_ing_ing == dish_id && x.table == "dish"                               select y.dish_id).tolist();     dish.addrange(dish_in_dish); }  dish = dish.distinct().tolist(); 

i have feeling can same results making join between dish , dish, don't know how it.

can piece of code rewritten join between dish , dish? i'm hoping time reduction, faster?

i'm sure can join, i'm still not @ them, , reduce db calls 2 instead of n+1:

list<int> dish = (from x in db.dishingredient                   join y in db.dish on x.dish_ing_dish equals y.dish_id                   x.dish_ing_ing == ing_id && x.table == "ingredient"                   select y.dish_id).tolist();  list<int> dish_in_dish = (from x in db.dishingredient                               join y in db.dish on x.dish_ing_dish equals y.dish_id                               dish.contains(x.dish_ing_ing) && x.table == "dish"                               select y.dish_id).tolist();  dish = dish.union(dish_in_dish).tolist(); 

this might work well:

list<int> dish = (from x in db.dishingredient     join y in db.dish on x.dish_ing_dish equals y.dish_id     x.dish_ing_ing == ing_id && x.table == "ingredient"     select y.dish_id).union(         (from x in db.dishingredient         join y in db.dish on x.dish_ing_dish equals y.dish_id         join in db.dishingredient on a.dish_ing_ing equals x.dish_id         join b in db.dish on a.dish_ing_dish equals b.dish_id         x.dish_ing_ing == ing_id && x.table == "ingredient"         , a.table=="dish"         select b.dish_id).tolist();