Push filtering, sorting and pagination into the database layer

Tier 2 — scale

Problem

GetAllAsync(userId) loads a user's entire library into memory (thumbnails included); the component then filters, sorts and pages it client-side. UrlExistsAsync loads every URL a user owns just to compare one. Pulse loads everything to compute aggregates. This is fine at 50 resources and collapses at 5,000.

Evidence

• LearnStack.Core/Services/LearningResourceService.cs:12-21 — GetAllAsync
• LearnStack.Core/Services/LearningResourceService.cs:74-96 — UrlExistsAsync materializes all URLs
• LearnStack/Components/Pages/Resources.razor:636-643 — in-memory paging
• LearnStack/Components/Pages/Pulse.razor.cs:60 — loads all resources for aggregates

Proposed fix

1. Introduce a query/filter object and return a paged result (Items, TotalCount) from the service.
2. Apply search, status/type/priority filters, sorting and Skip/Take in IQueryable.
3. Add a persisted NormalizedUrl column with a unique index per user, and replace UrlExistsAsync with a single indexed lookup.
4. Compute Pulse aggregates with GroupBy in SQL rather than in memory.

Acceptance criteria

• Resource list issues one query per page regardless of library size
• Duplicate-URL check is a single indexed query
• Pulse does not materialize the full library

Start by reading LearnStack.Core/Services/LearningResourceService.cs:12-21 and :74-96, then inspect Resources.razor:636-643 and Pulse.razor.cs:60. Trace how the current in-memory operations work before introducing the query/filter and paged-result design. Done means one query per resource-list page, one indexed duplicate-URL lookup, and no full-library materialization for Pulse aggregates.