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?

edited. How would I be able to get this table using linq? Let my table name "DeviceInfo" — fyasir, Oct 13 '14 at 04:05
yap. but here group by contains two different substring. how can I solve this? — fyasir, Oct 13 '14 at 04:13
Just a tiny `if` that checks for `Android` or `iOS` substring. — zerkms, Oct 13 '14 at 04:15
my output should have to be like the second table. So any suggestion? — fyasir, Oct 13 '14 at 04:19
First - learn how to use `GroupBy` - in your case it must return one of those 2 strings. Try to do that first. — zerkms, Oct 13 '14 at 04:26
is it possible in one query? or I have to make a dummy table where only "Android" and "iOS" were found,then use group by on that dummy table? — fyasir, Oct 13 '14 at 04:40
It is possible with one query. As the first step - learn how to write an expression that returns either `Android` or `iOS` for the given input, using trivial `if` — zerkms, Oct 13 '14 at 05:40

score 2 · Accepted Answer · answered Oct 13 '14 at 10:15

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();

Rahul Singh · Answer 2 · 2014-10-14T10:39:12.157

2

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);
                }

edited Oct 14 '14 at 10:39

answered Oct 13 '14 at 10:54

Rahul Singh

21,585
6
41
56

+1.`var query = (from device in db.DeviceInfoes group device by new { Android = device.OS.Contains("Android"), iOS = device.OS.Contains("iOS") } into deviceGroup select new { User = deviceGroup.Key.Android ? "Android": "iOS", Count = deviceGroup.Count() }).ToList();`is much more simpler. – kelsier Oct 13 '14 at 12:21
it returns 4 rows instead of 2. – fyasir Oct 14 '14 at 03:30
@NutBoltu - Show us the data source in that case! I am using the below Data Source: List deviceInfo = new List { new DeviceInfo { ID = 1, OS = "Android 2.2" , Device ="Samsung"}, new DeviceInfo { ID = 2, OS = "Linux/Android 4.2" , Device ="LG"},........... }; – Rahul Singh Oct 14 '14 at 04:57
@RahulSingh The List contains values neither Android nor iOS.(like blackberry OS) which creates extra rows. – fyasir Oct 14 '14 at 07:22
@NutBoltu - You didn't mentioned that in question. I have updated the code for 'Other' devices as well. – Rahul Singh Oct 14 '14 at 09:45
The list have other devices but I need only Android and iOS users. – fyasir Oct 14 '14 at 10:15
@NutBoltu - Then simply add that filter:- where device.OS.Contains("Android") || device.OS.Contains("iOS") Updated the code. – Rahul Singh Oct 14 '14 at 10:22
@RahulSingh Now it works.You may remove the othercount from your code. thanks :) – fyasir Oct 14 '14 at 10:35

Using linq to group a table that contains substrings

2 Answers2

Linked