Feature: sort on nested endpoints

[bart-degreed]

Sorting on nested endpoints is currently not supported. For example, this request:

GET /blogs/1/articles?sort=-id

returns the next response:

{
  "errors": [
    {
      "id": "772f747d-12d6-47d5-a43e-2e9843fc1cff",
      "status": "400",
      "title": "The specified query string parameter is currently not supported on nested resource endpoints.",
      "detail": "Query string parameter 'sort' is currently not supported on nested resource endpoints. (i.e. of the form '/article/1/author?parameterName=...')",
      "source": {
        "parameter": "sort"
      }
    }
  ]
}

But it turns out that EF Core 3.1 is able to translate the next query:

[HttpGet]
public string Get()
{
    var query =
            _appDbContext.Blogs
                .Include(blog => blog.Articles)
                .ThenInclude(article => article.Revisions)
                .OrderByDescending(blog => blog.Name)
                .Select(blog => new Blog
                {
                    Id = blog.Id,
                    Name = blog.Name,
                    Articles = blog.Articles
                        .OrderByDescending(article => article.Title)
                        .Select(article => new Article
                        {
                            Title = article.Title,
                            Revisions = article.Revisions
                                .OrderByDescending(revision => revision.PublishTime)
                                .ToList()
                        })
                        .ToList()
                })
        ;

    var results = query.ToArray();
    return JsonConvert.SerializeObject(results);
}

Into:

SELECT b."Id", b."Name", t."Title", t."Id", t."Id0", t."ArticleId", t."AuthorId", t."PublishTime"
FROM "Blogs" AS b
LEFT JOIN (
    SELECT a."Title", a."Id", r."Id" AS "Id0", r."ArticleId", r."AuthorId", r."PublishTime", a."BlogId"
    FROM "Article" AS a
    LEFT JOIN "Revision" AS r ON a."Id" = r."ArticleId"
) AS t ON b."Id" = t."BlogId"
ORDER BY b."Name" DESC, b."Id", t."Title" DESC, t."Id", t."PublishTime" DESC, t."Id0"

Resulting in the next response:

[
  {
    "Name": "Nature",
    "Articles": [
      {
        "Title": "Wildlife",
        "Url": null,
        "Author": null,
        "Revisions": [],
        "Id": 0,
        "StringId": ""
      },
      {
        "Title": "Flowers",
        "Url": null,
        "Author": null,
        "Revisions": [],
        "Id": 0,
        "StringId": ""
      }
    ],
    "Id": 2,
    "StringId": "2"
  },
  {
    "Name": "Coding Guidelines",
    "Articles": [
      {
        "Title": "What's new in .NET Core",
        "Url": null,
        "Author": null,
        "Revisions": [
          {
            "PublishTime": "2020-05-12T00:00:00",
            "Author": null,
            "Id": 1,
            "StringId": "1"
          },
          {
            "PublishTime": "2019-08-11T00:00:00",
            "Author": null,
            "Id": 2,
            "StringId": "2"
          }
        ],
        "Id": 0,
        "StringId": ""
      },
      {
        "Title": "The art of refactoring",
        "Url": null,
        "Author": null,
        "Revisions": [],
        "Id": 0,
        "StringId": ""
      }
    ],
    "Id": 1,
    "StringId": "1"
  }
]

Seed data, for reference:

context.Database.EnsureDeleted();
context.Database.EnsureCreated();

var authorJane = new Author
{
    FirstName = "Jane",
    LastName = "Smith",
    Email = "nospam@email.com",
    LivingAddress = new Address
    {
        Street = "Square Street",
        ZipCode = "12345",
        Country = new Country
        {
            IsoCode = "USA",
            DisplayName = "United States of America"
        }
    }
};
var authorJohn = new Author
{
    FirstName = "John",
    LastName = "Doe",
    Email = "nospam@email.com",
    LivingAddress = new Address
    {
        Street = "Main Street",
        ZipCode = "11111",
        Country = new Country
        {
            IsoCode = "AUS",
            DisplayName = "Australia"
        }
    }
};

context.Blogs.AddRange(new Blog
{
    Name = "Coding Guidelines",
    Articles = new List<Article>
    {
        new Article
        {
            Title = "The art of refactoring",
            Url = "http://www.coding.com/refactoring",
            Author = authorJohn
        },
        new Article
        {
            Title = "What's new in .NET Core",
            Url = "http://www.coding.com/netcore",
            Author = authorJane,
            Revisions = new List<Revision>
            {
                new Revision
                {
                    Author = authorJane,
                    PublishTime = new DateTime(2020,5,12)
                },
                new Revision
                {
                    Author = authorJane,
                    PublishTime = new DateTime(2019,8,11)
                }
            }
        }
    }
}, new Blog
{
    Name = "Nature",
    Articles = new List<Article>
    {
        new Article
        {
            Title = "Wildlife",
            Url = "http://www.nature.com/wildlife",
            Author = authorJane
        },
        new Article
        {
            Title = "Flowers",
            Url = "http://www.nature.com/flowers",
            Author = authorJane
        }
    }
});

Models, for reference:

public class Blog : Identifiable
{
    [Attr]
    public string Name { get; set; }
    
    [HasMany]
    public ICollection<Article> Articles { get; set; }
}

public class Article : Identifiable
{
    [Attr]
    public string Title { get; set; }

    [Attr]
    public string Url { get; set; }

    [HasOne]
    public Author Author { get; set; }

    [HasMany]
    public ICollection<Revision> Revisions { get; set; }
}

public class Author : Identifiable
{
    [Attr]
    public string FirstName { get; set; }

    [Attr]
    public string LastName { get; set; }

    [Attr]
    public string Email { get; set; }

    [HasOne]
    public Address LivingAddress { get; set; }
}

public class Address : Identifiable
{
    [Attr]
    public string Street { get; set; }

    [Attr]
    public string ZipCode { get; set; }

    [HasOne]
    public Country Country { get; set; }
}

public class Country : Identifiable
{
    [Attr]
    public string IsoCode { get; set; }
    
    [Attr]
    public string DisplayName { get; set; }
}

public class Revision : Identifiable
{
    [Attr] 
    public DateTime PublishTime { get; set; }

    [Attr] 
    public Author Author { get; set; }
}

[bart-degreed]

Based on the above, we should be able to support nested endpoints, as well as sorting on deeply included relationships, for example:

GET /blogs/1/articles?sort=-title&sort[revisions]=-publishTime

and

GET /blogs?sort=-name&sort[articles]=-title&sort[articles.revisions]=-publishTime