c# – Making an alphabatical list grouped by letters


I have a pet project (open source on GitHub) that I built as a learning exercise. It is a lyrics web application built in .NET Core MVC.

On the artists page of the site (view on mobile size viewport, it is only optimised for mobiles so far), I list all artists alphabatically. The artists are grouped by letter.

The code that builds that page is the following:

Controller

(Route("artists"))
public async Task<IActionResult> Index()
{
  IDictionary<char, List<LibraryArtistViewModel>> viewModel = await _artistsService
    .GetAllArtistsAsync();

  return View(viewModel);
}

Service

public async Task<IDictionary<char, List<LibraryArtistViewModel>>> GetAllArtistsAsync()
{
  List<LibraryArtistViewModel> artists = new List<LibraryArtistViewModel>();

  await using NpgsqlConnection connection = new NpgsqlConnection(databaseOptions.ConnectionString);
  await connection.OpenAsync();

  await using NpgsqlCommand command = new NpgsqlCommand("select a.first_name, a.last_name, "as".name as primary_slug, ai.data as image_data, count(l.title) as number_of_lyrics from artists a left join artist_images ai on ai.artist_id = a.id inner join artist_slugs "as" on "as".artist_id = a.id left join lyrics l on l.artist_id = a.id where a.is_approved = true and a.is_deleted = false and "as".is_primary = true and l.is_approved = true and l.is_deleted = false group by a.id, "as".name, ai.data order by a.first_name asc;", connection);

  await using NpgsqlDataReader reader = await command.ExecuteReaderAsync();

  while (await reader.ReadAsync())
  {
    LibraryArtistViewModel artist = new LibraryArtistViewModel();
    string firstName = Convert.ToString(reader(0));
    string lastName = Convert.ToString(reader(1));
    string fullName = textInfo.ToTitleCase($"{firstName} {lastName}");
    string primarySlug = Convert.ToString(reader(2));
    bool hasImage = reader(3) != System.DBNull.Value;
    int numberOfLyrics = Convert.ToInt32(reader(4));

    artist.FirstName = firstName;
    artist.LastName = lastName;
    artist.FullName = fullName;
    artist.PrimarySlug = primarySlug;
    artist.HasImage = hasImage;
    artist.NumberOfLyrics = numberOfLyrics;

    artists.Add(artist);
  }

  IDictionary<char, List<LibraryArtistViewModel>> dictionary = BuildDictionary(artists);

  return dictionary;
}

private IDictionary<char, List<LibraryArtistViewModel>> BuildDictionary(List<LibraryArtistViewModel> artists)
{
  List<char> letters = new List<char>();

  IDictionary<char, List<LibraryArtistViewModel>> dictionary =
    new Dictionary<char, List<LibraryArtistViewModel>>();

  foreach (LibraryArtistViewModel artist in artists)
  {
    char firstLetter = char.ToUpper(artist.FirstName(0));

    if (!letters.Contains(firstLetter))
    {
      letters.Add(firstLetter);

      dictionary.Add(firstLetter, new List<LibraryArtistViewModel>());
    }
  }

  foreach (char letter in letters)
  {
    foreach (LibraryArtistViewModel artist in artists)
    {
      char firstLetter = char.ToUpper(artist.FirstName(0));

      if (letter == firstLetter)
      {
        List<LibraryArtistViewModel> artistsBeginningWithTheLetter = dictionary(letter);
        artistsBeginningWithTheLetter.Add(artist);
      }
    }
  }

  return dictionary;
}

The code above works, but I feel like it isn’t efficient. I feel like there’s a better way of doing this.

Also, I am very unsure about the way I serve up images. My images are currently stored in the database as bytes and I have a controller that serves up the image. This seems to be really killing my performance metrics on https://web.dev.

I built this project to learn things. So I’d really love to learn how to do things “properly” even if they are deemed an overkill.

I’d appreciate some pointers on the above and also on how exactly I can improve the artist images situation.

web metrics