Linq to SharePoint with Distinct Results

Question

I'm making an alphabetical search filter, and want to get the results from a list and only show the available letter filters based on what results are in the list. Now the tricky part, the desired column is a people field, and I only want to get the first letter of the person's name.

Right now I can use the following code to query a letter, but who wants to run 26 queries and slow page loads. In addition, all the results will show of users that start with the letter a. So I guess I could do a limit to return only one result, if this was the only option.

SPList UserActivity = web.Lists["User Activity"];

var varUserActivity = (from SPListItem UserActivityItem in UserActivity.Items
                    where UserActivityItem["Username"].ToString().Substring(UserActivityItem["Username"].ToString().IndexOf('#') + 1, (UserActivityItem["Username"].ToString().Length - UserActivityItem["Username"].ToString().IndexOf('#')) - 1).ToLower().Replace("domain\\","").StartsWith("a")
                    select UserActivityItem);

foreach (SPListItem uaitem in varUserActivity)
{
    this.Controls.Add(new LiteralControl(uaitem["Username"].ToString() + "<br>"));
}

But I prefer to run one query, returning one result for each unique letter. With this list example data:

1. DOMAIN\Terra Branford
2. DOMAIN\Locke Cole
3. DOMAIN\Sabin Figaro
4. DOMAIN\Setzer Gabbiani
5. DOMAIN\Cyan Garamonde
6. DOMAIN\Celes Chere
7. DOMAIN\Cid Marquez

...the results expected to return are:

1. DOMAIN\Terra Branford
2. DOMAIN\Locke Cole
3. DOMAIN\Sabin Figaro
4. DOMAIN\Cyan Garamonde

From here I can build my filter with the letters: C L R S. So my question is how do I create a distinct Linq query that returns results based on first letter?

Answer 1

So I figured it out:

1. Use /14/bin/SPMetal.exe to export the site URL into a DataContext class file
2. Import that *.cs class file into your project (find in /14/bin/)
3. Add references
4. Start Linq to SP using your DataContext
5. Query to your heart's content

Here's an example of table data:

1. DOMAIN\Terra Branford
2. DOMAIN\Locke Cole
3. DOMAIN\Sabin Figaro
4. DOMAIN\Setzer Gabbiani
5. DOMAIN\Cyan Garamonde
6. DOMAIN\Celes Chere
7. DOMAIN\Cid Marquez

Here's the data filtering code:

using System.Linq;
using Microsoft.SharePoint.Linq;

MyDataContext dc = new MyDataContext("http://localhost");
var varUserActivity = (from item in dc.UserActivityList
                       orderby item.UsernameName.ToString().Substring(item.UsernameName.ToString().IndexOf('#') + 1, 1).ToLower().Replace("domain\\", "") ascending
                       select new { letter = item.UsernameName.ToString().Substring(item.UsernameName.ToString().IndexOf('#') + 1, 1).ToLower().Replace("domain\\", "") }).Distinct();

foreach (var uaitem in varUserActivity)
{
    this.Controls.Add(new LiteralControl(uaitem.letter + "<br>"));
}

So what's happening in this code is it queries a single people column in a list, strips out the domain, returns the first letter of the account name, sorts ascending and returns distinct letters. So the result with the above table data will show:

1. c
2. l
3. s
4. t

This is perfect for making an alphabetic filter. I'm not completely sure if it's doing real data filtering or not, but it sure beats doing 26 different queries by letter.