I need some help on performing a select query based on a result of a sub query in the ASP.NET MVC Controller. by default the Get method returns the list of objects... for example:
public IQueryable<Order> GetOrdersByID(string id)
{
return db.Orders.Where(e => e.orderId == id);
}
If I want to (select all products where product id is in (select product IDS from orders where order id = ID)
)
How can I achieve that in the controller with a method like:
public IQueryable<Product> GetOrdersByID(string id)
{
/// This line is wrong what is the correct one please....
return db.Products.Where(p => p.id IN (db.Orders
.Where(e => e.orderId == id));
}
You can use the IEnumerable<T>
's Any
extension method here:
public IQueryable<Product> GetOrdersByID(string id)
{
return db.Products
.Where(p => db.Orders.Any(i => i.orderId == p.id && i.orderId == id));
}
NOTICE: Take on account that the Any
method has the complexity of O(N) and the full solution actually has the complexity of O(N•M) because for each Product
, in the worst-case, you might scan the full Orders
collection.