如何使用自动查询返回多对多关系的嵌套对象

假设我有 3 个课程：

public class Book
{
    [Autoincrement]
    public int Id {get; set;}
    public string Title {get; set;}
    [Reference]
    public list<BookAuthor> BookAuthors {get; set;}
}

public class BookAuthor
{
    [ForeignKey(typeof(Book))]
    public int BookId {get; set;}
    [Reference]
    public Book Book {get; set;}

    [ForeignKey(typeof(Author))]
    public int AuthorId {get; set;}
    [Reference]
    public Author Author {get; set;}
}

public class Author
{
    [Autoincrement]
    public int Id {get; set;}
    public string Name {get; set;}
}

书籍和作者之间存在多对多的关系。

这是我当前正在构建的应用程序的常见问题，我需要向前端提供这样的 DTO：

public class BookDto
{
    public int Id {get; set;}
    public string Title {get; set;}
    public list<Author> Authors {get; set;}
}

前端需要嵌入Author。我需要一种在单个查询中将作者嵌套在 DTO 中的方法。

这可能吗？

我添加了一个实例来执行您想要的操作.

在 OrmLite 中，每个数据模型类都与基础表 1:1 映射，并且没有对 M:M 查​​询的神奇支持，您必须将它们用作存储在 RDBMS 中的不同表。

此外，每个表都需要 OrmLite 中唯一的主 ID，而 OrmLite 中缺少该 IDBookAuthor我已经添加了，我还添加了[UniqueConstraint]为了强制不重复的关系，通过这些更改，生成的类如下所示：

public class Book
{
    [AutoIncrement]
    public int Id {get; set;}
    public string Title {get; set;}
    [Reference] 
    public List<BookAuthor> BookAuthors {get; set;}
}

[UniqueConstraint(nameof(BookId), nameof(AuthorId))]
public class BookAuthor
{
    [AutoIncrement] public int Id {get; set;} 

    [ForeignKey(typeof(Book))]
    public int BookId {get; set;}

    [ForeignKey(typeof(Author))]
    public int AuthorId {get; set;}
}

public class Author
{
    [AutoIncrement]
    public int Id {get; set;}
    public string Name {get; set;}
}

public class BookDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<Author> Authors { get; set; }
}

然后创建表并添加一些示例数据：

db.CreateTable<Book>();
db.CreateTable<Author>();
db.CreateTable<BookAuthor>();

var book1Id = db.Insert(new Book { Title = "Book 1" }, selectIdentity:true);
var book2Id = db.Insert(new Book { Title = "Book 2" }, selectIdentity:true);
var book3Id = db.Insert(new Book { Title = "Book 3" }, selectIdentity:true);

var authorAId = db.Insert(new Author { Name = "Author A" }, selectIdentity:true);
var authorBId = db.Insert(new Author { Name = "Author B" }, selectIdentity:true);

db.Insert(new BookAuthor { BookId = 1, AuthorId = 1 });
db.Insert(new BookAuthor { BookId = 1, AuthorId = 2 });
db.Insert(new BookAuthor { BookId = 2, AuthorId = 2 });
db.Insert(new BookAuthor { BookId = 3, AuthorId = 2 });

然后，要在 OrmLite 中的单个查询中选择多个表，您可以使用多选 https://github.com/ServiceStack/ServiceStack.OrmLite#selecting-multiple-columns-across-joined-tables, e.g:

var q = db.From<Book>()
    .Join<BookAuthor>()
    .Join<BookAuthor,Author>()
    .Select<Book,Author>((b,a) => new { b, a });
var results = db.SelectMulti<Book,Author>(q);

作为属性名称遵循参考约定 https://github.com/ServiceStack/ServiceStack.OrmLite#typed-sqlexpression-support-for-joins它们的连接不需要显式指定，因为它们可以隐式推断。

这将返回一个List<Tuple<Book,Author>>然后你可以使用字典将所有作者及其书籍拼接起来：

var booksMap = new Dictionary<int,BookDto>();
results.Each(t => {
    if (!booksMap.TryGetValue(t.Item1.Id, out var dto))
        booksMap[t.Item1.Id] = dto = t.Item1.ConvertTo<BookDto>();        
    if (dto.Authors == null) 
        dto.Authors = new List<Author>();
    dto.Authors.Add(t.Item2);
});

我们可以从 Dictionary Values 中获取书籍列表：

var dtos = booksMap.Values;
dtos.PrintDump();

书籍中包含其作者并打印出来：

[
    {
        Id: 1,
        Title: Book 1,
        Authors: 
        [
            {
                Id: 1,
                Name: Author A
            },
            {
                Id: 2,
                Name: Author B
            }
        ]
    },
    {
        Id: 2,
        Title: Book 2,
        Authors: 
        [
            {
                Id: 2,
                Name: Author B
            }
        ]
    },
    {
        Id: 3,
        Title: Book 3,
        Authors: 
        [
            {
                Id: 2,
                Name: Author B
            }
        ]
    }
]

自动查询

自动查询 https://docs.servicestack.net/autoquery只能实现可以自动化的隐式查询，如果您需要执行任何自定义查询或投影，则需要提供自定义自动查询实现 https://docs.servicestack.net/autoquery-rdbms#custom-autoquery-implementations，由于可以隐式推断连接，因此您可以让 AutoQuery 构造连接查询，因此您只需提供自定义Select()自己进行投影和映射，例如：

[Route("/books/query")]
public class QueryBooks : QueryDb<Book,BookDto>, 
    IJoin<Book,BookAuthor>,
    IJoin<BookAuthor,Author> {}

public class MyQueryServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    //Override with custom implementation
    public object Any(QueryBooks query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request)
            .Select<Book,Author>((b,a) => new { b, a });
        var results = db.SelectMulti<Book,Author>(q);

        var booksMap = new Dictionary<int,BookDto>();
        results.Each(t => {
            if (!booksMap.TryGetValue(t.Item1.Id, out var dto))
                booksMap[t.Item1.Id] = dto = t.Item1.ConvertTo<BookDto>();        
            if (dto.Authors == null) 
                dto.Authors = new List<Author>();
            dto.Authors.Add(t.Item2);
        });
        return new QueryResponse<BookDto> { Results = booksMap.Values.ToList() };
    }
}