select.md

Let's take database models like this:

public class Blogpost
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
    public List<Tag> Tags { get; set; }
    public User Author { get; set; }
    public id AuthorId { get; set; }
    public DateTime CreatedAt { get; set; }
}
public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Color { get; set; }
    public string Description { get; set; }
    public List<Blogpost> Blogposts { get; set; }
}
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string PasswordHash { get; set; }
    public DateTime CreatedAt { get; set; }
    public List<Blogpost> Blogposts { get; set; }
}

We would like to get a list of five most recent blogposts, with their tags and authors. We don't need all of the information about the tags or authors, mind you. We don't need the author's password, for example.

Thus, we create a DTO that will represent the shape of data we actually do want:

public class BlpogpostDto
{
    public int Id { get; init; }
    public string Title { get; init; }
    public string Excerpt { get; init; }
    public DateTime CreatedAt { get; init; }
    public List<TagDto> Tags { get; init; }
    public string AuthorName { get; init; }
}
public class TagDto
{
    public string Name { get; init; }
    public string Color { get; init; }
}

and we can use .Select() to project our database models into that DTO, so the database only selects what we actually need. That is, it does not execute a SELECT * FROM but rather SELECT a, b, c FROM

var blogpost = await _context.Blogposts
    .OrderByDescending(b => b.CreatedAt) // sort the blogposts
    .Select(b => new BlogpostDto         // select the data
        Id = b.Id,
        Title = b.Title,
        Excerpt = $"{b.Body[..400]}...",
        CreatedAt = CreatedAt,
        AuthorName = b.Author.Name,      // use the nav property
        Tags = b.Tags.Select(t => new TagDto { // select tags
            Name = t.Name,
            Color = t.Color
        })
    )
    .Take(5)                             // get 5 of them
    .ToListAsync();                      // execute the query