Entity Framework select from a list of ids obtained from another E.F select query

Question

I'm trying to get ItemsEntity from a E.F query with a where statement. The condition gets item_id from another E.F query which returns more than 1 item_id.

var item_id = (from n in db.OrderDetail where n.OrderId == id select n.item_id);
var itemEntity = (from m in db.ItemsEntity where *m.item_id==item_id* select m);

Answer 1

You can use Contains method when you want to use a collection in your Where clause

var listOfIds = (from n in db.OrderDetail where n.OrderId == id select n.item_id);
var itemEntity = (from m in db.ItemsEntity where listOfIds.Contains(m.item_id) select m);

Keep in mind that, with the above code, itemEntity variable will be collection. If you want single item, Use methods like FirstOrDefault() or First() as needed.

The above code can be written as a LINQ method chain as below as well

var listOfIds = db.OrderDetail.Where(n=>n.OrderId == id).Select(x=>x.item_id);
var itemEntity = db.ItemsEntity.Where(m=>listOfIds.Contains(m.item_id));