如何使用LINQ从SQL Server获取唯一的首字母名称和名称开头？

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.

几天前我在玩ASP.NET MVC 1.0。我从一个名为“Contacts”的表DB开始,结构非常简单,例如标题,FullName,SurName,电子邮件,电话,地址等

I'm using LINQ as my Model.

我正在使用LINQ作为我的模型。

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:

在我的主视图中,我想显示一个字母表列表,其中包含以该字母开头的FullNames,以及FullNames的数量。类似如下所示:

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.

A - (2)D - (4)J - (1)等。关于显示器的一个特别之处在于我不想显示那些没有以它们开头的名字的字母表。

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.

我尝试了几个问题,但没有成功。任何帮助解决此查询表示赞赏。请以VB.NET语言提供代码。

Thanks.

3 个解决方案

#1

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

should work

#2

There is an example on MSDN that is very similar:

在MSDN上有一个非常相似的例子:

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.

您必须在select语句中将Words = g更改为Count = g.Count,因此您只需查询组中项目的总和。

#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

#1

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