Subquery in LINQ that's in the select statement, not the where clause

c#, linq-to-sql, subquery

Solution

This query should be equivalent:

var query = Products.Select(p => new {
                         p.Name,
                         SubProducts = Products.Count(c => c.parent_id == p.id)
                     });

foreach (var item in query)
{
    Console.WriteLine("{0} : {1}", item.Name, item.SubProducts);
}

Problem

I need to do something like the following ``` SELECT p.name, (SELECT COUNT(p.id) FROM products WHERE products.parent_id = p.id) AS sub_products FROM products AS p ``` I see lots of LINQ examples of subqueries in the where clause,but nothing like this where it's in the select statement.

Original source