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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s