Both Left Join & Group By In Single LINQ Query

I had encountered with the complicated requirement, requirement was to display category and total products against it, I need both left outer join and group by on CategoryID of Categories table

I had provide below solution which I had tested on LINQPad Below is tested solution

I applied simple rule while I was coding and that is “Break one complex problem in small but reasonably simple problems” (Tip: Divide & Rule)

Step 1: Left Join Categories Products

var LO =  from a in Categories 
	       join b in Products			
	       on a.CategoryID equals b.CategoryID into leftjoin
	       from c in leftjoin.DefaultIfEmpty()			
	       select new {
		 ProductID = c.ProductID == null ? 0 : 1,
		 CategoryID = a.CategoryID,
		 CategoryName = a.CategoryName,
	       };

Step 2: Group by { CategoryID, CategoryName, ProductID }

var Group = from d in LO
		 group d by new {d.CategoryID, d.CategoryName, d.ProductID} into groupby
		 select new {
			CategoryID = groupby.Key.CategoryID,
			CategoryName = groupby.Key.CategoryName,
			Total = groupby.Sum(x => groupby.Key.ProductID)
		 };

Step 3: Iteration

foreach(var i in Group) {
	Console.WriteLine(i);
}

Output

Category ID: 1; Category Name: Beverages;      Total Products: 12
Category ID: 2; Category Name: Condiments;     Total Products: 12
Category ID: 3; Category Name: Confections;    Total Products: 13
Category ID: 4; Category Name: Dairy Products; Total Products: 10
Category ID: 5; Category Name: Grains/Cereals; Total Products: 7
Category ID: 6; Category Name: Meat/Poultry;   Total Products: 6
Category ID: 7; Category Name: Produce;        Total Products: 5
Category ID: 8; Category Name: Seafood;        Total Products: 12
Category ID: 9; Category Name: New;            Total Products: 0

Cheers !!!

Advertisements

Greedy Operators For Eager, Not For Lazy !!!

——————————————————-
Deferred execution or Lazy execution
——————————————————-

We have seen how YIELD keyword works

One of the fundamental use of it deferred execution or lazy execution of code
We try to see this practically

class Program
{        
   static void Main(string[] args)
   {
      IEnumerable EvenNum = Extension.GetEvenNumbers();
      foreach (var item in EvenNum)
      {
          Console.WriteLine("Selected Number: {0}", item);
      }            
   }
}

public static class Extension
{
   public static IEnumerable GetEvenNumbers()
   {
      IEnumerable list = new[] { 1, 2, 3, 4, 5, 6, 7, 8 };
      foreach (var item in list)
      {
          if (item % 2 == 0)
          {
              Console.WriteLine("Start Deferred Execution");
              yield return item;
              Console.WriteLine("Stop Deferred Execution");
          }
      }
   }
}

////// OUTPUT ///////
Start Deferred Execution
Selected Number: 2
Stop Deferred Execution
Start Deferred Execution
Selected Number: 4
Stop Deferred Execution
Start Deferred Execution
Selected Number: 6
Stop Deferred Execution
Start Deferred Execution
Selected Number: 8
Stop Deferred Execution
Press any key to continue . . .

It can be seen that due to yield return in GetEvenNumbers function is not called
unless we actually required return data from function, and to do so we need to
iterate using foreach

Food For Thought:
Useful to avoid unnecessary function call which are not actually used in system by developer (:D)

—————————————————
Eager Evaluation or Execution
—————————————————

Using Greedy Operators
ToList, ToArray, ToDictionary

Product A Concrete Type
Sum, First, Single, Count

The ToList() is also eager because it transforms a collection into a concrete IEnumerable

public static List ToList(this IEnumerable source)
{
    // Arguments checking is ommitted. 
    return new List(source);
}

Inside the constructor of List, the source is iterated, and all items are calculated
and added to the List collection before it is returned.

Append ToList and this function is executed immediately

IEnumerable EvenNum = Extension.GetEvenNumbers().ToList();

/////// OUTPUT /////////
Start Deferred Execution
Stop Deferred Execution
Start Deferred Execution
Stop Deferred Execution
Start Deferred Execution
Stop Deferred Execution
Start Deferred Execution
Stop Deferred Execution
Selected Number: 2
Selected Number: 4
Selected Number: 6
Selected Number: 8