ObjectQuery lambda expression for select inside select

Question

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

Answer 1

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