Suppose, We have following data source:
table

Now, I want to basically group these users and show the count of browser type they are using. Thus, my output should look like:
user status
Solution:

We need to first group the data by UserIds, and from that result-set we need to again group by individual browser names (excluding their version numbers). Here is the complete code snippet:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  5. public class Program
  6. {
  7. public static void Main()
  8. {
  9. List<BrowserInfo> browesers = new List<BrowserInfo>
  10. {
  11. new BrowserInfo { UserID = 1, Browser = "Chrome-32.0"},
  12. new BrowserInfo { UserID = 1, Browser = "Chrome-30.0"},
  13. new BrowserInfo { UserID = 1, Browser = "Chrome-33.0"},
  14. new BrowserInfo { UserID = 1, Browser = "Firefox-20.0"},
  15. new BrowserInfo { UserID = 1, Browser = "Firefox-26.0"},
  16. new BrowserInfo { UserID = 1, Browser = "Safari"},
  17. new BrowserInfo { UserID = 1, Browser = "IE 9"},
  18. new BrowserInfo { UserID = 1, Browser = "IE 10"},
  19. new BrowserInfo { UserID = 2, Browser = "Chrome-31.0"},
  20. new BrowserInfo { UserID = 2, Browser = "Chrome-32.0"},
  21. new BrowserInfo { UserID = 2, Browser = "IE 10"},
  22. new BrowserInfo { UserID = 2, Browser = "Firefox-22.0"},
  23. new BrowserInfo { UserID = 2, Browser = "Firefox-20.0"}
  24. };
  27. var query = from browser in browesers
  28. group browser by browser.UserID into UserGroup
  29. from countGroup in
  30. from brow in UserGroup
  31. group brow by new
  32. {
  33. Chrome = brow.Browser.Contains("Chrome"),
  34. Firefox = brow.Browser.Contains("Firefox"),
  35. IE = brow.Browser.Contains("IE")
  36. } into g
  37. select new
  38. {
  39. ChromeCount = g.Key.Chrome ? g.Count() : 0,
  40. FirefoxCount = g.Key.Firefox ? g.Count() : 0,
  41. IECount = g.Key.IE ? g.Count() : 0,
  42. OthersCount = (!g.Key.Chrome && !g.Key.Firefox && !g.Key.IE) ? g.Count() : 0
  43. }
  44. )
  45. group countGroup by UserGroup.Key;
  47. Console.WriteLine("UserId | Chrome | Firefox | IE | Others");
  48. Console.WriteLine("----------------------------------------");
  49. foreach(var users in query)
  50. {
  51. Console.WriteLine("{0} | {1} | {2} | {3} | {4} ",
  52. users.Key, users.Where(x => x.ChromeCount != 0).Select(x => x.ChromeCount).FirstOrDefault(),
  53. users.Where(x => x.FirefoxCount != 0).Select(x => x.FirefoxCount).FirstOrDefault(),
  54. users.Where(x => x.IECount != 0).Select(x => x.IECount).FirstOrDefault(),
  55. users.Where(x => x.OthersCount != 0).Select(x => x.OthersCount).FirstOrDefault()
  56. );
  57. }
  59. }
  60. }
  62. public class BrowserInfo
  63. {
  64. public int UserID { get; set; }
  65. public string Browser { get; set; }
  66. }
Final Output
Ouptput