English
Simple queries
Step 1. Safe search patterns
Why. Users search for people and public chats by a part of the name: "ali" finds alice. In SQL that is ILIKE '%ali%' (ILIKE is the case-insensitive LIKE of PostgreSQL). But % and _ are wildcards in a LIKE pattern: _ matches any single character. A search for bob_ would also find bobby, and a search for % would return every user. The characters a user types must be escaped so that they mean themselves.
Do.
cs
namespace Chatex.Data.Repositories;
internal static class LikePattern
{
/// <summary>Pass to <c>EF.Functions.ILike</c>; without it Npgsql emits <c>ESCAPE ''</c> and the escaping is ignored.</summary>
public const string Escape = @"\";
/// <summary>Builds a LIKE pattern matching <paramref name="text"/> anywhere, with wildcards in it escaped.</summary>
public static string Contains(string text) =>
"%" + text.Replace(Escape, Escape + Escape).Replace("%", Escape + "%").Replace("_", Escape + "_") + "%";
}Contains("bob_") returns %bob\_%: the outer % are wildcards, the escaped \_ is a plain underscore. The backslash itself is escaped first, otherwise a typed \ would escape the next character. The @"\" is a verbatim string: inside @"..." a backslash is an ordinary character.
Step 2. Users
Why. The user repository shows the three tools that appear in almost every query of Chatex:
Includeloads a related entity in the same query:Include(u => u.Profile)adds aJOINtouser_profiles. Without it,user.Profilestaysnull.AsNoTrackingtells the context not to track the results. Tracking costs memory and time and is only needed for entities we are going to change and save. Search results are only shown, so they are read-only.ExecuteUpdateAsyncchanges rows directly in the database with oneUPDATE … WHERE …, without loading them. "Setlast_seen_atof this user" does not need the whole user in memory. Such methods write immediately, independent ofSaveChangesAsync, as their interface comments say.
Do.
cs
using Chatex.Data.Abstractions;
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
namespace Chatex.Data.Repositories;
internal sealed class UserRepository(ChatexDbContext db) : IUserRepository
{
public Task<User?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default) =>
db.Users.FirstOrDefaultAsync(u => u.Id == id, cancellationToken);
public Task<User?> GetWithProfileAsync(Guid id, CancellationToken cancellationToken = default) =>
db.Users.Include(u => u.Profile).FirstOrDefaultAsync(u => u.Id == id, cancellationToken);
public async Task<IReadOnlyList<User>> SearchAsync(string query, int limit, CancellationToken cancellationToken = default)
{
var pattern = LikePattern.Contains(query);
return await db.Users
.AsNoTracking()
.Include(u => u.Profile)
.Where(u => EF.Functions.ILike(u.UserName!, pattern, LikePattern.Escape)
|| (u.Profile != null && EF.Functions.ILike(u.Profile.DisplayName, pattern, LikePattern.Escape)))
.OrderBy(u => u.UserName)
.Take(limit)
.ToListAsync(cancellationToken);
}
public async Task<IReadOnlyList<Guid>> GetExistingIdsAsync(
IReadOnlyCollection<Guid> ids, CancellationToken cancellationToken = default) =>
await db.Users.Where(u => ids.Contains(u.Id)).Select(u => u.Id).ToListAsync(cancellationToken);
public void AddProfile(UserProfile profile) => db.UserProfiles.Add(profile);
public Task UpdateLastSeenAsync(Guid userId, DateTimeOffset lastSeenAt, CancellationToken cancellationToken = default) =>
db.Users
.Where(u => u.Id == userId)
.ExecuteUpdateAsync(s => s.SetProperty(u => u.LastSeenAt, lastSeenAt), cancellationToken);
}- The class is
internal sealed: outside the data layer only the interface is visible. - The primary constructor parameter
dbis captured by the methods; there is no need for a field. - Expression-bodied methods return the
Taskof EF Core directly (=> db.Users...FirstOrDefaultAsync(...)), withoutasync/await, because there is nothing to do after the query. Methods that convert aList<T>toIReadOnlyList<T>needawait. EF.Functions.ILike(column, pattern, escape)translates tocolumn ILIKE pattern ESCAPE '\'. The escape character must be passed explicitly — the comment inLikePatternexplains why.u.UserName!—UserNameisstring?in Identity; the!tells the compiler that in this query it is notnull(every Chatex user has a name).ids.Contains(u.Id)translates toid = ANY (@ids): one query for a whole list of ids.
Step 3. Refresh tokens
Do.
cs
using Chatex.Data.Abstractions;
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
namespace Chatex.Data.Repositories;
internal sealed class RefreshTokenRepository(ChatexDbContext db) : IRefreshTokenRepository
{
public void Add(RefreshToken token) => db.RefreshTokens.Add(token);
public Task<RefreshToken?> GetByHashAsync(string tokenHash, CancellationToken cancellationToken = default) =>
db.RefreshTokens.Include(t => t.User).FirstOrDefaultAsync(t => t.TokenHash == tokenHash, cancellationToken);
public Task<int> RevokeAllForUserAsync(Guid userId, DateTimeOffset revokedAt, CancellationToken cancellationToken = default) =>
db.RefreshTokens
.Where(t => t.UserId == userId && t.RevokedAt == null && t.ExpiresAt > revokedAt)
.ExecuteUpdateAsync(s => s.SetProperty(t => t.RevokedAt, revokedAt), cancellationToken);
public Task<int> DeleteStaleAsync(DateTimeOffset before, CancellationToken cancellationToken = default) =>
db.RefreshTokens
.Where(t => t.ExpiresAt < before || t.RevokedAt < before)
.ExecuteDeleteAsync(cancellationToken);
}GetByHashAsyncis tracked: the auth service will revoke the token and save it.RevokeAllForUserAsync("sign out everywhere") updates every active token of a user with oneUPDATEand returns how many rows changed.DeleteStaleAsyncusesExecuteDeleteAsync, oneDELETE … WHERE ….t.RevokedAt < beforeisfalsefor aNULLRevokedAtin C# (a lifted comparison), and also in SQL, so tokens that were never revoked are deleted only after they expire.
Step 4. Attachments
Do.
cs
using Chatex.Data.Abstractions;
using Chatex.Data.Entities;
using Microsoft.EntityFrameworkCore;
namespace Chatex.Data.Repositories;
internal sealed class AttachmentRepository(ChatexDbContext db) : IAttachmentRepository
{
public void Add(Attachment attachment) => db.Attachments.Add(attachment);
public void Remove(Attachment attachment) => db.Attachments.Remove(attachment);
public Task<Attachment?> GetByIdAsync(Guid id, CancellationToken cancellationToken = default) =>
db.Attachments.Include(a => a.Message).FirstOrDefaultAsync(a => a.Id == id, cancellationToken);
public async Task<IReadOnlyList<Attachment>> GetPendingAsync(
Guid uploaderId, IReadOnlyCollection<Guid> ids, CancellationToken cancellationToken = default) =>
await db.Attachments
.Where(a => a.UploaderId == uploaderId && a.MessageId == null && ids.Contains(a.Id))
.ToListAsync(cancellationToken);
public async Task<IReadOnlyList<Attachment>> GetStalePendingAsync(
DateTimeOffset before, int limit, CancellationToken cancellationToken = default) =>
await db.Attachments
.Where(a => a.MessageId == null && a.CreatedAt < before)
.OrderBy(a => a.CreatedAt)
.Take(limit)
.ToListAsync(cancellationToken);
}GetPendingAsync is a security check written as a query: of the ids a client sends with a message, it returns only uploads that belong to this user and are not yet attached to a message. A client cannot attach somebody else's file or reuse a file from another message by guessing ids. GetStalePendingAsync returns the oldest unsent uploads first, at most limit of them, so the cleanup job of topic 13 works in small batches.
Check
dotnet build succeeds.