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 !!!

JQuery Virtual Keyboard

A quick post on virtual keyboard which can be used to enter user name / password / Pine number, etc… Internet banking highly recommend to use while logging into Internet Banking Site.

Below are the advantages of virtual keyboard

  • The virtual keyboard can be an additional precautionary measure, protects you against malicious key logger programs which might have infected your  computer.
  • A key logger program can capture the keystrokes you enter in the physical keyboard to login which may be accessing your PC without your knowledge or permission.
  • A virtual keyboard prevents your username or password from being stolen, especially while using public computers, such as those available in Internet cafes. Also, the sequence in which the keys appear will change everytime the page is refreshed.

Before I start with an example go through Keith Wood’s solution for it build on top of JQuery

It is very clean, well documented, higly compatible and scalable solution, if I have to rate, I rate 5 out of 5 stars and highly recommended solution

Virtual Keyboard:  http://keith-wood.name/keypad.html

Quick Reference: http://keith-wood.name/keypad.html

jQuery Keypad Reference:     http://keith-wood.name/keypadRef.html

Last but not the least I am going through simple yet quick example before signing off

Step 0:- Create Folder structure as below

  • main
  • main/css
  • main/javascript

Step 1:-  Add jquery.keypad.css in main/css folder

Step 2:-  Add jquery-1.7.1.min.js and jquery.keypad.js in main/javascript folder

Step 3:-  Add index.html in main folder and copy-paste below code into index.html  “please read code carefully before copying it blindly :D”

<html>
<title>Virtual Keyboard</title>
<head>
 <link type="text/css" href="css/jquery.keypad.css" rel="stylesheet" />  
 <script type="text/javascript" src="javascript/jquery-1.7.1.min.js"></script>
 <script type="text/javascript" src="javascript/jquery.keypad.js"></script> 
    <script type="text/javascript">
        $(document).ready(function () {                    
            $("input[id$='chkKeyboard']").click(function () {            
                if (!$(this).is(':checked')) {
                    $('#txtUsername').keypad('destroy');
                    $('#txtPassword').keypad('destroy');
                } else {
                    $('#txtUsername').keypad({ keypadOnly: true, layout: $.keypad.qwertyLayout, randomiseAlphabetic: true, randomiseNumeric: true, randomiseOther: true });
                    $('#txtPassword').keypad({ keypadOnly: true, layout: $.keypad.qwertyLayout, randomiseAlphabetic: true, randomiseNumeric: true, randomiseOther: true });                    
                }
            });
        });
    </script>       

</head>

<body>
   <div style="text-align: left; margin-top: 15px;">                           
      Username : <input type="text" id="txtUsername" /><br/>
      Password : <input type="Password" id="txtPassword"></asp:TextBox><br/>
      <input type="checkbox" id="chkKeyboard" /> Use virtual keyboard (Recommended)<br/>
      <input type="button" id="btnSubmit" value="Submit"  />
   </div>
</body>

</html>

Step 4:- Open index.html in any browser (ie, chrome, firefox, etc…)
virtual_keyboard

Quote: “Great solutions are described in few words”