Have Geek Brain

How can I get the unique first letter of names and the number of names starting with that letter from SQL Server using LINQ?

I was playing around with ASP.NET MVC 1.0 a couple of days ago. I started with a single DB table named "Contacts", with a very simple structure for example. Title, FullName, SurName, Email, Phone, Address, etc.

I am using LINQ as my model.

In my main view, I wanted to display a list of alphabets that have matching full names starting with that alphabet plus a number of full names. Similar as below:

A - (2)
D - (4)
J - (1)
and so on. One feature of the display is that I don't want to display alphabets that don't have names starting with them.

I tried a couple of requests but couldn't. Any help with this issue is appreciated. Provide the code in VB.NET language.

Thanks.

0


a source to share


3 answers 


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


must work

+3


a source


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 will need to change Words = g to Count = g.Count in the select statement so that you only ask for the total of the items in the group.

0


a source


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
0


a source






More articles:


All Articles