Nested query results in LINQ (or sub queries)

Question

I have two classes: OrderDTO and ProductsDTO.

public class OrderDTO
{
    // Attributes
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public int EmployeeID { get; set; }

    // Collection of Products
    List<ProductDTO> Products { get; set; }
}

public class ProductsDTO
{
    // Attributes
    public int ProductID { get; set; }
    public string Name { get; set; }
}

Also I have tables: Orders, Products, and ProductOrder.

I would like to select orders with associated products and return them in one query.

Example:

using(var ctx = new Database())
{
    return from o in ctx.Orders
        join po in ctx.ProductOrder on o.OrderID equals po.OrderID
        where o.OrderID == 1
        select new OrderDTO
            {
                OrderID = o.OrderID,
                OrderDate = o.OrderDate,
                EmployeID = o.EmployeeID,

                Products = (new ProductDTO
                {
                    ProductID = po.ProductID,
                    Name =  po.Name
                }).ToList();
            }
}

I would like to fill OrderDTO with order attributes and also fill the collection with Products.

Answer 1

(from o in ctx.Orders
where o.OrderID == 1
select new OrderDTO
            {
                OrderID = o.OrderID,
                OrderDate = o.OrderDate,
                EmployeID = o.EmployeeID,

                Products = from p in o.Products select
                 new ProductDTO
                {
                    ProductID = p.ProductID,
                    Name =  p.Name
                };
            }).ToList();

You can simply use the relationship between Order and Product, so no need for explicit join. Also you cannot do .ToList() inside a query, so you need to make your products to IEnumerable in the dto object.

public class OrderDTO
{
    // Attributes
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public int EmployeeID { get; set; }

    // Collection of Products
    IEnumerable<ProductDTO> Products { get; set; }
}

Answer 2

Since you want the join to return a collection of matching items rather than creating a new item for each pair you want to perform a GroupJoin rather than a Join. The synatx is very similar though.

using(var ctx = new Database())
{
    return (from o in ctx.Orders
        join po in ctx.ProductOrder on o.OrderID equals po.OrderID
        into products
        where o.OrderID == 1
        select new OrderDTO
            {
                OrderID = o.OrderID,
                OrderDate = o.OrderDate,
                EmployeID = o.EmployeeID,

                Products = products.Select(po => new ProductDTO
                {
                    ProductID = po.ProductID,
                    Name =  po.Name
                }).ToList();
            }).ToList();
}

Also note that you're currently disposing of your database before actually fetching the results of the query. You need to materialize the results of the query before disposing of the context.