Linq Subquery same Table

Question

I've a table with Id and CategoryId.

I need to get all records from that table with same CategoryId as the given Id.

This is what I came up to:

var query = from x in erm.Projects
            where x.CategoryId == erm.Projects.Where(y => y.Id == lookId).FirstOrDefault().CategoryId
            select x;

What's the best way to do this query?

Thank you

Answer 1

You can use a join to do this sort of thing. Also has the advantage of avoiding a NullReferenceException if FirstOrDefault() returns null and you call .CategoryId on it blindly.

var query = erm.Table.Join(erm.Projects.Where(y => y.Id == lookId), x => x.CategoryId, y => y.CategoryId, (x,y) => x);