Ordering inner keysource in simple/unnamed C# LINQ group join

Question

I know the question reads weird due to words used. But this is the terminology used on this MSDN page from where I am learning LINQ group joins and I am going to explain them.

The data on which I am trying LINQ is:

    class Product
    {
        public string Name { get; set; }
        public int CategoryID { get; set; }
    }

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

    // Specify the first data source.
    static 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.
    static List<Product> products = new List<Product>()
    {
        new Product{Name="Cola",  CategoryID=001},
        new Product{Name="Tea",  CategoryID=001},
        new Product{Name="Mustard", CategoryID=002},
        new Product{Name="Pickles", CategoryID=002},
        new Product{Name="Carrots", CategoryID=003},
        new Product{Name="Bok Choy", CategoryID=003},
        new Product{Name="Peaches", CategoryID=005},
        new Product{Name="Melons", CategoryID=005},
    };

Now the terms:

• Simple/unnamed group join is one in which we directly select group:

  //... 
  join product in products on category.ID equals product.CategoryID into prodGroup                      
          select prodGroup;
  

  instead of selecting anonymous type or using nested from-select clause.

• Outer keysource in LINQ group join is a datasource that follows from keyword, inner keysource is a datasource that follows join keyword

  I call simple group join unnamed (for convinience sake), since we dont have the outer keysource name/Id in selected group.

I was trying to write simple/unnamed query that will produce following result, first orderby outer keysource attribute and then by inner keysource attribute:

Unnamed Group
  Cola
  Tea
Unnamed Group
  Mustard
  Pickles
Unnamed Group
  Melons
  Peaches
Unnamed Group
Unnamed Group
  Bok Choy
  Carrots

I am able to order by outer keysource as follows:

var simpleGroupJoinOrderby = 
        from category in categories
        orderby category.Name           //orderby outer ks
        join product in products on category.ID equals product.CategoryID into prodGroup                      
        select prodGroup;

foreach (var unnamedGroup in simpleGroupJoinOrderby)
{   
    Console.WriteLine("Unnamed group");
    foreach(var product in unnamedGroup)
    {
        Console.WriteLine("  " + product.Name);
    }
}

But it is producing following output:

Unnamed group
  Cola
  Tea
Unnamed group
  Mustard
  Pickles
Unnamed group
  Peaches
  Melons
Unnamed group
Unnamed group
  Carrots
  Bok Choy

But I am unable order products under unnamed categories group.

I know I can do this by selecting anonymous type as follows:

        var namedGroupJoinOrderBy =
            from category in categories
            orderby category.Name  //order group hierarchy by name
            join product in products on category.ID equals product.CategoryID into prodGroup
            select
            new
            {
                Category = category.Name,
                Products = from prod in prodGroup
                           orderby prod.Name       //order by prodGroup.prod.Name under particular group hierarchy
                           select prod
            };

        foreach (var category in namedGroupJoinOrderBy)
        {
            Console.WriteLine("Category : " + category.Category);
            foreach (var product in category.Products)
            {
                Console.WriteLine("   " + product.Name);
            }
        }

However I just want to know if I can do the same without selecting the anonymous type. I feel it should be syntactically un-related to order on "both" keysources and selecting the anonymous type. Thus there should be some way to do this in query itself as follows, but its not working:

var simpleGroupJoinOrderby = 
            from category in categories
            orderby category.Name           //orderby outer ks
            join product in products on category.ID equals product.CategoryID into prodGroup    
            orderby product.Name   //Err: The name 'product' does not exist in the current context
            select prodGroup;

Answer 1

You could just order products before joining:

var simpleGroupJoinOrderby = 
    from category in categories
    orderby category.Name     
    join product in products.OrderBy(p => p.Name) 
        on category.ID equals product.CategoryID into prodGroup                      
    select prodGroup;

The result is:

Unnamed group
  Cola
  Tea
Unnamed group
  Mustard
  Pickles
Unnamed group
  Melons
  Peaches
Unnamed group
Unnamed group
  Bok Choy
  Carrots

It works because Join preserves the order of the elements of outer, and for each of these elements, the order of the matching elements of inner (quoted from MSDN).

---

Some alternatives (more like your examples):

var simpleGroupJoinOrderby = categories.OrderBy (c => c.Name)
                                       .GroupJoin(products, 
                                                  c => c.ID, 
                                                  p => p.CategoryID, 
                                                  (c, ps) => ps.OrderBy(p => p.Name));

var simpleGroupJoinOrderby = from category in categories
                             orderby category.Name     
                             join product in products
                                on category.ID equals product.CategoryID into prodGroup                      
                             select prodGroup.OrderBy(g => g.Name); 

Answer 2

I could be wrong. But I don't think you can do this without the full select. I kind of disagree that the selection of the anonymous type and the order by are unrelated.

Either you select that thing which was already mentioned in the query, in which case you don't need an anon type, or you select something else based on it. If you select something else, not what you are given by the previous part of the query, you have to do a full select.

IINM this is the same in SQL - you would need a subquery to do something similar to this (not that you would select the whole group like this, but if you for example wanted to do ORDER BY followed by TOP).

Answer 3

I looked at your code listed in the link you provided and here is a solution to your problem. the original order by you have in your query will only order the categories and will not look at the products. if you want the order you described in your question you will need another sub-query to order the products as well:

var simpleGroupJoinOrderby =
        from category in DBContext.categories
        orderby category.Name           //orderby outer ks
        join product in DBContext.products on category.ID equals product.CategoryID into prodGroup
        select prodGroup;

            foreach (var unnamedGroup in simpleGroupJoinOrderby)
            {
                lblList.Text += "//Unnamed group";
                var unnamedProductsGroup = unnamedGroup.OrderBy(z => z.Name).Select(group => new { name = group.Name, ID = group.CategoryID });

                foreach (var product in unnamedProductsGroup)
                {
                    //Console.WriteLine("  " + product.Name);
                    lblList.Text += "|" + product.name;

                }
            }