Search code examples
linqlambdaobjectquery

ObjectQuery lambda expression for select inside select

i want to create query like this using lambda expression in ObjectQuery

SELECT *
FROM tableA
WHERE ID in (SELECT ID in tableB)

I try using

var histories = _entities.ProductViewHistories.Take(5).OrderByDescending(p=>p.DateViewed);
var products = _entities.Products.Where(p => p.ID in histories );

but it's not working. Can someone point me to the right direction?

Thanks

Solution

  • Sure - "in" isn't part of C# in that way. Try this though:

    var products = _entities.Products.Where(p => histories.Contains(p.ID));

    Contains is the appropriate method to detect whether a value is within another collection. However, you may find it would be more appropriate to use a join here.

    Also, I'm concerned about this query:

    That doesn't represent the 5 most recently viewed histories - it represents "some arbitrary 5 view histories" (the first 5 returned in some indeterminate order) then arranged in most-recent-first order.

    I suspect you actually want:

    var histories = _entities.ProductViewHistories.OrderByDescending(p=>p.DateViewed)
                                              .Take(5);