Complex OData Query in ASP.NET API Controller

Question

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

Why do you return IQueryable<Product> from the GetOrdersByID method? — Yair Nevet
– Yair Nevet, Commented May 5, 2014 at 22:46

Yair Nevet · Accepted Answer · 2014-05-19 10:31:52Z

3

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.

edited May 19, 2014 at 10:31

answered May 5, 2014 at 22:22

Yair Nevet

13.1k17 gold badges71 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Complex OData Query in ASP.NET API Controller

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related