C# linq left out join does not work using DefaultIfEmpty

Question

I am new to c# and I have a query for using left join in LINQ.

I have two data source - one is categories collection, one is products collection. I want to join this two data source together so that I can have result like this :

Category Name (Fruit) - Product Name ( peach)- Source ( QLD,NSW).

Please note I have Source as List collection inside products collection.

I have two problems here:

1. When I use DefaultIfEmpty, I cannot set correct reference to null Source collection if Souce does not exist - I can however reference to a Product Name if it does not exist to a meaningful string say "Does not exist". Anybody can help to show how to put a empty string if left join cannot find Source for a product ?
2. I cannot find a way to output Category Name - like Fruit ,Vegetables in final result although i can do it in group join, any idea of how to do that ?

I attached my code here.

class Product
{
    public string Name { get; set; }
    public int CategoryID { get; set; }
    public List<string> Source;
}

class Category
{
    public string Name { get; set; }
    public int ID { get; set; }
}

// Specify the first data source.
List<Category> categories = new List<Category>()
{ 
    new Category(){Name="Beverages", ID=001},
    new Category(){ Name="Condiments", ID=002},
    new Category(){ Name="Vegetables", ID=003},
    new Category() {  Name="Grains", ID=004},
    new Category() {  Name="Fruit", ID=005}            
};

// Specify the second data source.
List<Product> products = new List<Product>()
{
     new Product{Name="Cola",CategoryID=001,  Source = new List<string> {"NSW","VIC","QLD"} },  
     new Product{Name="Mustard", CategoryID=002 ,  Source = new List<string> {"NSW","VIC","SA"} },
     new Product{Name="Pickles", CategoryID=002 ,  Source = new List<string> {"NSW","VIC","NT"} },
     new Product{Name="Carrots", CategoryID=003 ,  Source = new List<string> {"NSW","VIC","TAS"} },
     new Product{Name="Bok Choy", CategoryID=003 ,  Source = new List<string> {"NSW","VIC","ACT"} },
     new Product{Name="Eggplant", CategoryID=003 ,  Source = new List<string> {"QLD","NSW"} },
     new Product{Name="Broccoli", CategoryID=003 ,  Source = new List<string> {"QLD,SA"} },
     new Product{Name="Peaches", CategoryID=005 ,  Source = new List<string> {"NSW","VIC","NZ"} },
     new Product{Name="Melons", CategoryID=005,  Source = new List<string> {"NSW","VIC"} },
};

the following code does not work for left join in LINQ:

void LeftOuterJoin()
{
   var leftOuterQuery =
       from category in categories
       join prod in products on category.ID equals prod.CategoryID into prodGroup          
       select prodGroup.DefaultIfEmpty(new Product()
       { Name = "Nothing!", CategoryID = category.ID})
       ;

    // Store the count of total items (for demonstration only). 
    int totalItems = 0;

    Console.WriteLine("Left Outer Join:");

    // A nested foreach statement  is required to access group items 
    foreach (var prodGrouping in leftOuterQuery)
    {
        Console.WriteLine("\nGroup count: {0}", prodGrouping.Count());

        // Intellisense does not show category name  if I use prodGrouping.Name 
         //and want to get categorhy name since left join produce hierarchy data 
         //why category name is not showing up - I am itinerate from 1st loop ??
        foreach (var item in prodGrouping)
        {
            totalItems++;
            Console.WriteLine("  {0,-10}{1}", item.Name, item.CategoryID);

            foreach (var s in item.Source)
            {
                Console.WriteLine("Souce state: {0}", s.ToString());
            }
        }
    }

    Console.WriteLine(System.Environment.NewLine);
}

Hi Marcin, I changed my original code it works but give me duplicate records, do you know why ?

 void LeftOuterJoin()
    {
        // Create the query. 
        var leftOuterQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           from prodG in prodGroup.DefaultIfEmpty()       
            select new
            {
                Category = category.Name,
                //Products = products.OrderBy(x => x.Name)
                Products = from prod2 in prodGroup
                           orderby prod2.Name
                           select prod2
            };


        Console.WriteLine("Left Outer Join:");

        // A nested foreach statement  is required to access group items 
        foreach (var item in leftOuterQuery)
        {                 

             Console.WriteLine("  {0,-10}", item.Category);
             foreach ( var p in item.Products)
             {
                 Console.WriteLine("ProductName: {0}", p.Name);
                 foreach (var s in p.Source)
                 {
                     Console.WriteLine("SourceName: {0}", s.ToString());

                 }
             }                   


        }

    }

I want to get result like this:

  Group Beverages:1
  Cola      1
  Souce state: NSW
  Souce state: VIC
  Souce state: QLD

 Group : Condiments
  Mustard   2
   Souce state: NSW
   Souce state: VIC
   Souce state: SA
 Pickles   2
  Souce state: NSW
  Souce state: VIC
  Souce state: NT

But I got result like this:

Beverages
 ProductName: Cola
 SourceName: NSW
 SourceName: VIC
 SourceName: QLD

Condiments
 ProductName: Mustard
 SourceName: NSW
 SourceName: VIC
 SourceName: SA    
 ProductName: Pickle
 SourceName: NSW
 SourceName: VIC
 SourceName: NT

Condiments
 ProductName: Mustard
 SourceName: NSW
 SourceName: VIC
 SourceName: SA
 ProductName: Pickle
 SourceName: NSW
 SourceName: VIC
 SourceName: NT

Answer 1

There is a simple and neat extension for left outer join using linq published here: http://www.codeproject.com/Tips/724333/Left-Outer-Join-extension-for-Entity-Framework I think this may help you.

Answer 2

I'm still not sure what you're trying to achieve, but to get left outer join using LINQ you need to use join ... into and from ....DefaultIfEmpty().

You're using select ....DefaultIfEmpty(), which is not exactly what you need.

var leftOuterQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID into prodGroup
    from products in prodGroup.DefaultIfEmpty()       
    select new
    {
        Category = category.Name,
        Products = products.OrderBy(x => x.Name)
    }