English
The chat list
Step 1. What the chat list needs
Why. The chat list is the first screen of the messenger and is loaded on every start and after every reconnect. Each row shows (Fig. 5.2):
- the chat itself: title or, for a direct chat, the other person's name and avatar;
- a preview of the last message and its time;
- the number of unread messages;
- the user's role and whether the chat is muted.
The rows are ordered by activity: the chat with the newest message comes first.
Fig. 5.2. The data behind one row of the chat list
A naive implementation loads the chats and then, for each chat, runs three more queries — 61 queries for 20 chats (the N+1 problem). Chatex builds one query: LINQ with sub-queries inside a projection, which EF Core translates into one SQL statement — a correlated sub-query SELECT count(*) for the unread counter, and joins over numbered rows (ROW_NUMBER() OVER (PARTITION BY chat_id …)) that pick the newest message and the other member of each chat.
Step 2. A cursor of two values
Why. The list is sorted by LastActivityAt, but two chats can have exactly the same activity time — for example, two chats created by the same save. A cursor of only the time would skip or repeat such chats at a page boundary. The cursor needs a second, unique tie-breaker: the chat id. The order becomes (LastActivityAt, ChatId) descending, and "the next page" means "rows that come after the pair (t, id)".
SQL can compare such pairs directly with a row value: (a, b) < (x, y) is true when a < x, or a = x and b < y. EF Core offers it as EF.Functions.LessThan(ValueTuple.Create(a, b), ValueTuple.Create(x, y)).
Step 3. The chat repository
Do.
cs
using Chatex.Data.Abstractions;
using Chatex.Data.Entities;
using Chatex.Data.Models;
using Microsoft.EntityFrameworkCore;
namespace Chatex.Data.Repositories;
internal sealed class ChatRepository(ChatexDbContext db) : IChatRepository
{
private const int PreviewLength = 100;
private IQueryable<Chat> Chats => db.Chats.Where(c => c.DeletedAt == null);
public void Add(Chat chat) => db.Chats.Add(chat);
public Task<Chat?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default) =>
Chats.FirstOrDefaultAsync(c => c.Id == id, cancellationToken);
public Task<Chat?> GetDirectAsync(Guid userA, Guid userB, CancellationToken cancellationToken = default)
{
var key = Chat.MakeDirectKey(userA, userB);
return Chats.FirstOrDefaultAsync(c => c.DirectKey == key, cancellationToken);
}
public Task<Chat?> GetByHandleAsync(string handle, CancellationToken cancellationToken = default)
{
var normalized = handle.ToLowerInvariant();
return Chats.FirstOrDefaultAsync(c => c.PublicHandle == normalized, cancellationToken);
}
public async Task<IReadOnlyList<Chat>> SearchPublicAsync(string query, int limit, CancellationToken cancellationToken = default)
{
var pattern = LikePattern.Contains(query);
return await Chats
.AsNoTracking()
.Where(c => c.PublicHandle != null
&& (EF.Functions.ILike(c.PublicHandle, pattern, LikePattern.Escape)
|| EF.Functions.ILike(c.Title!, pattern, LikePattern.Escape)))
.OrderBy(c => c.PublicHandle)
.Take(limit)
.ToListAsync(cancellationToken);
}
public async Task<CursorPage<ChatListItem, ChatListCursor>> GetUserChatsAsync(
Guid userId, ChatListCursor? after, int limit, CancellationToken cancellationToken = default)
{
var memberships = db.ChatMembers
.AsNoTracking()
.Where(m => m.UserId == userId && m.Chat.DeletedAt == null);
if (after is { } cursor)
{
memberships = memberships.Where(m => EF.Functions.LessThan(
ValueTuple.Create(m.Chat.LastActivityAt, m.ChatId),
ValueTuple.Create(cursor.LastActivityAt, cursor.ChatId)));
}
var items = await memberships
.OrderByDescending(m => m.Chat.LastActivityAt)
.ThenByDescending(m => m.ChatId)
.Take(limit + 1)
.Select(m => new ChatListItem(
m.Chat,
m.Role,
m.IsMuted,
m.Chat.Messages
.Where(x => x.DeletedAt == null)
.OrderByDescending(x => x.Id)
.Select(x => new MessagePreview(
x.Id,
x.SenderId,
x.Body.Length > PreviewLength ? x.Body.Substring(0, PreviewLength) : x.Body,
x.CreatedAt))
.FirstOrDefault(),
m.Chat.Messages.Count(x => x.DeletedAt == null
&& x.SenderId != userId
&& (m.LastReadMessageId == null || x.Id.CompareTo(m.LastReadMessageId.Value) > 0)),
m.Chat.Type == ChatType.Direct
? m.Chat.Members
.Where(o => o.UserId != userId)
.Select(o => new UserSummary(o.UserId, o.User.UserName!, o.User.Profile!.DisplayName, o.User.Profile.AvatarPath))
.FirstOrDefault()
: null))
.ToListAsync(cancellationToken);
return Paging.ToPage(items, limit, i => new ChatListCursor(i.Chat.LastActivityAt, i.Chat.Id));
}
public Task TouchAsync(Guid chatId, DateTimeOffset at, CancellationToken cancellationToken = default) =>
db.Chats
.Where(c => c.Id == chatId && c.LastActivityAt < at)
.ExecuteUpdateAsync(s => s.SetProperty(c => c.LastActivityAt, at), cancellationToken);
}From the top:
The private property
Chatsis the table without soft-deleted chats. Every method starts from it, so a deleted chat cannot slip through a forgotten filter. (EF Core also has global query filters for this; an explicit property keeps the filter visible where it is used.)GetByHandleAsynclower-cases the handle: handles are stored in lower case, so@DotNetand@dotnetare the same chat.SearchPublicAsyncmatches the handle or the title, but only of chats that have a handle: private chats never appear in a search.c.Title!— groups and channels always have a title.GetUserChatsAsyncstarts from the user's memberships, not from the chats, because a chat appears in the list only for its members. Then, inside oneSelect:m.Chat.Messages ... .FirstOrDefault()is the last not-deleted message, cut to 100 characters withSubstring(translated to SQL);m.Chat.Messages.Count(...)counts unread messages: not deleted, not sent by the user, and newer than the read position (or all of them if the user has read nothing);- the peer is the other member of a direct chat, projected into a
UserSummary; groups and channels getnull.
Take(limit + 1)andPaging.ToPagework exactly as in the message history; the cursor is built from the last item's activity time and id.TouchAsyncmovesLastActivityAtforward with oneUPDATE, and only if the new time is later. When two messages are sent at almost the same time, the order in which their transactions commit cannot move the activity time backwards.
Projection instead of Include
GetUserChatsAsync uses no Include. A Select into a new type (a projection) tells EF Core exactly which columns to fetch, and the sub-queries become part of the same SQL statement. The result types ChatListItem, MessagePreview and UserSummary are not entities, so they are never tracked.
Check
dotnet build succeeds. To see the SQL of the chat list, run the program of the last chapter of this topic with EF Core's logging switched on (exercise 1 of the checkpoint).