BlogApp SELECT Queries — Published Posts, Search and Analytics

Putting it all together: the BlogApp’s real-world SELECT queries implement the patterns from this chapter — filtering, pagination, aggregation, and functions — in service of the actual API endpoints. These queries are what EF Core generates (Chapter 37–38) but understanding their raw SQL form enables performance diagnosis, optimisation, and debugging when EF Core’s generated SQL is not optimal.

BlogApp Production Queries #

-- ── 1. Published posts list with pagination (matches API GetPublishedAsync) ──
DECLARE @Page     INT = 1;
DECLARE @PageSize INT = 10;
DECLARE @Category VARCHAR(100) = NULL;   -- optional filter
DECLARE @Search   NVARCHAR(200) = NULL;  -- optional search term

SELECT
    p.Id,
    p.Title,
    p.Slug,
    p.Excerpt,
    p.ViewCount,
    p.CommentCount,
    p.PublishedAt,
    p.IsFeatured,
    u.DisplayName  AS AuthorName,
    u.AvatarUrl    AS AuthorAvatarUrl,
    c.Name         AS CategoryName,
    c.Slug         AS CategorySlug,
    STRING_AGG(t.Name, ',') WITHIN GROUP (ORDER BY t.Name) AS Tags,
    COUNT(*) OVER() AS TotalCount
FROM   dbo.Posts p
JOIN   dbo.Users      u  ON u.Id  = p.AuthorId
LEFT JOIN dbo.Categories c  ON c.Id  = p.CategoryId
LEFT JOIN dbo.PostTags   pt ON pt.PostId = p.Id
LEFT JOIN dbo.Tags       t  ON t.Id     = pt.TagId
WHERE  p.IsPublished = 1
  AND  (@Category IS NULL OR c.Slug = @Category)
  AND  (@Search   IS NULL
        OR p.Title   LIKE '%' + @Search + '%'
        OR p.Excerpt LIKE '%' + @Search + '%')
GROUP BY p.Id, p.Title, p.Slug, p.Excerpt, p.ViewCount, p.CommentCount,
         p.PublishedAt, p.IsFeatured, u.DisplayName, u.AvatarUrl,
         c.Name, c.Slug
ORDER BY p.PublishedAt DESC, p.Id DESC
OFFSET (@Page - 1) * @PageSize ROWS
FETCH  NEXT @PageSize ROWS ONLY;

-- ── 2. Post detail with full data (matches API GetBySlugAsync) ───────────
DECLARE @Slug VARCHAR(200) = 'getting-started-with-dotnet';

SELECT
    p.Id, p.Title, p.Slug, p.Body, p.Excerpt,
    p.CoverImageUrl, p.ViewCount, p.CommentCount,
    p.Status, p.IsPublished, p.IsFeatured,
    p.PublishedAt, p.CreatedAt, p.UpdatedAt,
    u.Id          AS AuthorId,
    u.DisplayName AS AuthorName,
    u.AvatarUrl   AS AuthorAvatarUrl,
    u.Bio         AS AuthorBio,
    c.Id          AS CategoryId,
    c.Name        AS CategoryName,
    c.Slug        AS CategorySlug
FROM   dbo.Posts p
JOIN   dbo.Users      u ON u.Id = p.AuthorId
LEFT JOIN dbo.Categories c ON c.Id = p.CategoryId
WHERE  p.Slug = @Slug
  AND  p.IsPublished = 1;

-- ── 3. Admin dashboard summary ────────────────────────────────────────────
SELECT
    Status,
    COUNT(*)             AS PostCount,
    AVG(ViewCount * 1.0) AS AvgViews,
    SUM(ViewCount)       AS TotalViews,
    MAX(UpdatedAt)       AS LastUpdated
FROM dbo.Posts
GROUP BY Status
ORDER BY
    CASE Status
        WHEN 'published' THEN 1
        WHEN 'review'    THEN 2
        WHEN 'draft'     THEN 3
        ELSE 4
    END;

-- ── 4. Most viewed posts in last 30 days ──────────────────────────────────
SELECT TOP 10
    p.Id, p.Title, p.Slug, p.ViewCount,
    p.PublishedAt,
    u.DisplayName AS AuthorName
FROM   dbo.Posts p
JOIN   dbo.Users u ON u.Id = p.AuthorId
WHERE  p.IsPublished = 1
  AND  p.PublishedAt >= DATEADD(DAY, -30, SYSUTCDATETIME())
ORDER  BY p.ViewCount DESC, p.PublishedAt DESC;

Common Mistakes #

Mistake 1 — Concatenating user input into SQL strings (SQL injection) #

❌ Wrong — "SELECT * FROM Posts WHERE Slug = '" + slug + "'"; user inputs '; DROP TABLE Posts; --.

✅ Correct — use parameters: ExecuteSqlInterpolated($"SELECT * FROM Posts WHERE Slug = {slug}").

Mistake 2 — Forgetting GROUP BY columns when using STRING_AGG (ambiguous column error) #

❌ Wrong — SELECT p.Id, p.Title, STRING_AGG(t.Name, ',') FROM Posts p JOIN PostTags ... GROUP BY p.Id; p.Title not in GROUP BY.

✅ Correct — include all non-aggregate columns: GROUP BY p.Id, p.Title, p.Slug, ....