0

I have this issue where i have a column in SQL table. the values are in comma separated format.

ex:

 1)   facebook,google,just giving, news letter, in-store
 2)   facebook,google,just giving
 3)   just giving
 4)   facebook
 5)   google
 6)   in-store,email
 7)   email,facebook

Now i want to query that table and get this values in a list where list contains distinct values and their count.

ex:

1) facebook - 10 
2) email - 20
3) in-store -5
and so on....

is there anyway to achieve this using LINQ?

NOTE: i can work on LINQ operators on LIST variable because my datasource which is 'entries' does not support LINQ( built in .NET 2.0)

        var results = new List<string>();
        var builder = new StringBuilder();

        foreach (var entry in entries.Cast<MWCompetitionsEntry>().Where(entry => entry.HowFound != null))
        {
              builder.Append(entry.HowFound).Append(",");
        }

        results.Add(builder.ToString());

RESULTS i am getting

facebook,email,in-store,google,in-store,newsletter,facebook,email,facebok

EDITS:

Now, I can work on results variable. i dont know how to get the unique values from that variable and their count.

please help

ANSWER

var values = new List<string>();
            var builder = new StringBuilder();
            var howFoundValues = new List<string>{"Email", "Facebook", "Twitter", "Leaflet", "Just Giving", "In-Store", "Other"};

            foreach (var entry in entries.Cast<MWCompetitionsEntry>().Where(entry => entry.HowFound != null))
            {
                  builder.Append(entry.HowFound).Append(",");
            }

            values.Add(builder.ToString());
            var result1 = values.SelectMany(l => l.Split(','))
                  .Where(howFoundValues.Contains) //will disregard any other items
                  .GroupBy(e => e)
                  .Select(e => String.Format("{0} - {1}",e.Key, e.Count()))
                  .ToList();
Improve this question

4 Answers 4

Reset to default
3

How about:

var result = from word in entries.SelectMany(e => e.HowFound.Split(','))
             group word by word into gr
             select new {Entry = gr.Key, Count = gr.Count()};

grouping instead of distinct allows you to get the count.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

my issue is my datasouurce class was build in .NET 2.0 and it does not support any linq operators so i have to use foreach and append the string in some variable.
@patel.milanb You tagged your question with linq so so get linq results.
Not only tagged, the question specifically says: how can i achieve this in LINQ.
I can operate linq on LIST variable but not on my data source like entries.SelectMany ... i have to get all the values using foreach and create one string and then split it by ',' and then get the distinct values.
@patel.milanb Just read the values into a list of string, so you have a list variable like the one in my answer
|
1

Something like this:

var groupedResults = results.SelectMany(v => v.Split(','))
                        .GroupBy(n => n)
                        .Select((item, index) => 
                                 new {
                                    name = String.Format("{0}) {1}", index+1, item.Key),
                                    count = item.Count()
                                 })
                        .ToList();
  1. You need to group elements by name.
  2. In LINQ to Objects .Select() operator can second paramater in lambda as index, that can help you to attach numeration.
Improve this answer

7 Comments

my issue is my datasouurce class was build in .NET 2.0 and it does not support any linq operators so i have to use foreach and append the string in some variable. BUT it gives me error on 'count = n.Count()' i dont know why... it says can not resolve symbol Count()
But you are using SelectMany(), that is LINQ operator, I had mistakes in my code, just updated it.
yes.. but i can operate on LIST not on my datasource.. like entries.SelectMany ...
OK i ma getting the result as '1) System.Linq.Lookup`2+Grouping[System.String,System.String]'
Sorry, I missed to add .Key to item. Updated my answer.
|
1 
var entries = new List<string>{"facebook,google,just giving, news letter, in-store",
 "facebook,google,just giving",
 "just giving", 
 "facebook",
 "google",
 "in-store,email",
 "email,facebook"};

var result = entries
.SelectMany (e => e.Split(','))
.GroupBy (e => e).Select (g => string.Format("{0} - {1}", g.Count (), g.Key));

result.Dump();

results in

4 - facebook 
3 - google 
3 - just giving 
1 -  news letter 
1 -  in-store 
1 - in-store 
2 - email

It's splitting yout entries first, then grouping by different strings.

If your data contains leading and/or trailing spaces like they do in your example, consider using .GroupBy(e => e.Trim())

Improve this answer

Comments

1

Try this

        Dictionary<string, int> valuesAndCount = new Dictionary<string, int>();
        foreach (var entry in entries) // entries is a collection of records from table
        {
            string[] values = entry.Split(',');
            foreach (var value in values)
            {
                if (!valuesAndCount.ContainsKey(value))
                {
                    valuesAndCount.Add(value, 0);
                }
                valuesAndCount[value] = valuesAndCount[value] + 1;
            }
        }


        //Then you'llhave your distinct values and thei count
        foreach (var key in valuesAndCount.Keys)
        {
            Console.WriteLine("{0} {1}",key, valuesAndCount[key]);
        }

Then the dictionary should contain your distinct values and their count

LINQ VERSION

        IEnumerable<string> strings = entries
            .ToList()
            .SelectMany(e => e.Split(','))
            .GroupBy(v => v)
            .Select(str => string.Format("{0} {1}", str.Count(), str.Key));


        foreach (var p in strings)
        {
            Console.WriteLine(p);
        }
Improve this answer

1 Comment

i am able to see the values and their count using this method... BUT is there any way to use LINQ in this

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.