How to get unique First Letter of names and count of names starting with that letter from SQL Server using LINQ?

Question

Hello all.

I was playing with ASP.NET MVC 1.0 a couple of days ago. I started out with a single table DB named 'Contacts', with very simple structure e.g. Title, FullName, SurName, Email, Phone, Address etc.

I'm using LINQ as my Model.

In my main view I wanted to display a list of alphabets that have matching FullNames starting with that alphabet plus the count of FullNames. Someting similar as shown below:

A - (2)
D - (4)
J - (1)
and so on. One particular thing about the display is that i don't want to display those alphabets that have no names starting with them.

I tried a couple of queries but didn't succeed. Any help to solve this query is appreciated. Please provide the code in VB.NET language.

Thanks.

Answer 1

    var query = from c in contacts
                group c by c.FullName[0] into cg
                select new { FirstChar = cg.Key, Count = cg.Count() };

sould work

Answer 2

There is an example on MSDN that is very similar:

public void Linq41() {
            string[] words = { "blueberry", "chimpanzee", "abacus", "banana", "apple", "cheese" };

            var wordGroups =
                from w in words
                group w by w[0] into g
                select new { FirstLetter = g.Key, Words = g };

            foreach (var g in wordGroups) {
                Console.WriteLine("Words that start with the letter '{0}':", g.FirstLetter);
                foreach (var w in g.Words) {
                    Console.WriteLine(w);
                }
            }
        }

You would have to change the Words = g to Count = g.Count in the select statement, so you only query the total sum of items in the group.

Answer 3

In VB.NET:

Dim FirstLetterCounts = From c In contacts _
                        Group c By Key = c(0) Into NameGroup _
                        Select FirstLetter = Key, Count = NameGroup.Count()

For Each g In FirstLetterCounts
    Console.WriteLine("First Letter = {0}, Count = {1}", g.Key, g.Count)
Next