Join two tables using LINQ Query and order based two parameters

Question

I have two tables Customers and Orders. I want a LINQ query to fetch list of all the orders placed by all the customers organized first by month and then by year. If there is no order corresponding to the customer, "No Orders” should be displayed.

The columns of Customers table are

customer_id
name
city

The columns of Orders table are

order_id
order_date
order_total
customer_id

I tried writing it the following way but its not giving complete output.

var res = from cust in db.Customers 
          join ord in db.Orders 
               on cust.customer_id equals ord.customer_id into g 
          from d in g.DefaultIfEmpty() 
          select new { 
               name=cust.name, 
               oId=d.order_id==null?-1:d.order_id 
          };   

How do I rectify it?

Answer 1

I finally got the right answer which exactly the result as expected. I have put it below. I have used two LINQ queries to arrive at the result though. The first one gives the result, but the final result needs to be displayed with names of customer and their order totals, hence it is partial result. The second LINQ query further refines the 'partialResult' and gives the result as expected.

var partialResult = (from c in db.Customers
                      join o in db.Orders

                      on c.customer_id equals o.customer_id
                      select new
                      {c.name,
                       o.order_total,
                       o.order_date }).OrderBy(m => m.order_date.Month).ThenBy(y =>              y.order_date.Year);

var finalResult = from c in db.Customers
                       orderby c.name
                       select new
                       {
                           name = c.name,
                           list = (from r in partialResult where c.name == r.name select r.order_total).ToList()

                       };

            foreach (var item in finalResult)
            {

                Console.WriteLine(item.name);
                if (item.list.Count == 0)
                {
                    Console.WriteLine("No orders");
                }
                else
                {
                    foreach (var i in item.list)
                    {
                        Console.WriteLine(i);
                    }
                }
            }