Using linq to group a table that contains substrings

Question

My Data Table(DeviceInfo):

ID      |    OS                     |   Device
-----------------------------------------------
1       | Android 2.2               |   Samsung
2       | Linux/Android 4.2         |   LG
3       | Linux/Android 4.4.2       |   HTC
4       | Android 3.2               |   Samsung
5       | Android 3.0               |   Motorola
6       | iOS 7.1.2                 |   iPad
7       | iOS 8.0                   |   iPhone 6
8       | iOS 6.1.6                 |   iPhone 4

I want to group this table by Android and ios user using Linq.Actually I have to group the table using the substring "Android" and "iOS".
My Output should be

ID      | User      | Count 
----------------------------
1       | Android   |   5
2       | iOS       |   3

How would I be able to get this table using linq?

Answer 1

Try this: (I hv used Console App, you can change the same as per your req.):-

 var query = from device in deviceInfo 
                           where device.OS.Contains("Android") || device.OS.Contains("iOS")
                            group device by new { Android = device.OS.Contains("Android"), iOS = device.OS.Contains("iOS") } into deviceGroup
                            select new
                            {
                                AndroidCount = deviceGroup.Key.Android ? deviceGroup.Count() : 0,
                                iOSCount = deviceGroup.Key.iOS ? deviceGroup.Count() : 0
                            };


                Console.WriteLine("User | Count");
                Console.WriteLine("--------------");
                foreach (var dev in query)
                {
                    if (dev.AndroidCount != 0)
                        Console.WriteLine("{0} | {1}", "Android", dev.AndroidCount);
                    if(dev.iOSCount!=0)
                        Console.WriteLine("{0} | {1}", "iOS", dev.iOSCount);
                }

Answer 2

You can try something like this :

// db is my datacontext
var groupByOS = (from c in
                      (from d in db.DeviceInfo 
                       where d.Os.ToUpper().Contains("ANDROID") ||
                       d.Os.ToUpper().Contains("IOS")
                       group d by new { d.Os } into dev
                       select new
                       {
                         User = dev.Key.Os.ToUpper().Contains("ANDROID") ? "Android" : "iOS",
                         DeviceCount = dev.Count()
                       })
                 group c by new { c.User } into newgrp
                 select new
                 {
                     newgrp.Key.User,
                     Count = newgrp.Sum(q => q.DeviceCount)
                 }).ToList();