如何使用自动查询返回多对多关系的嵌套对象 [英] How to return nested objects of many-to-many relationship with autoquery
问题描述
假设我有 3 个班级:
公开课书{[自动递增]公共 int Id {get;放;}公共字符串标题{get;放;}[参考]公开列表BookAuthors {get;放;}}公开课 BookAuthor{[外键(typeof(Book))]公共 int BookId {get;放;}[参考]公共书本{get;放;}[外键(typeof(作者))]公共 int AuthorId {get;放;}[参考]公共作者 作者 {get;放;}}公开课作者{[自动递增]公共 int Id {get;放;}公共字符串名称{get;放;}} 书籍和作者之间存在多对多关系.
这是我目前正在构建的应用程序的常见问题，我需要向前端提供这样的 DTO:
公共类BookDto{公共 int Id {get;放;}公共字符串标题{get;放;}公开列表<作者>作者{get;放;}}前端需要嵌入作者.我需要一种在单个查询中让作者嵌套在 DTO 中的方法.
这可能吗?
我添加了一个活生生的例子来做你想做的事 你可以在 Gistlyn 上玩.
在 OrmLite 中，每个数据模型类与底层表 1:1 映射，并且没有对 M:M 查询的神奇支持，您必须将它们用作不同的表，因为它们存储在 RDBMS 中.
此外，OrmLite 中的每个表都需要一个唯一的主 ID，我添加的 BookAuthor 中缺少该 ID，我还添加了一个 [UniqueConstraint] 以强制执行不重复的关系，通过这些更改，产生的类看起来像:
公开课书{[自动递增]公共 int Id {get;放;}公共字符串标题{get;放;}[参考]公共列表BookAuthors {get;放;}}[UniqueConstraint(nameof(BookId), nameof(AuthorId))]公开课 BookAuthor{[AutoIncrement] public int Id {get;放;}[外键(typeof(Book))]公共 int BookId {get;放;}[外键(typeof(作者))]公共 int AuthorId {get;放;}}公开课作者{[自动递增]公共 int Id {get;放;}公共字符串名称{get;放;}}公开课 BookDto{公共 int Id { 获取；放;}公共字符串标题{获取；放;}公开列表<作者>作者{得到;放;}} 然后创建表并添加一些示例数据:
db.CreateTable();db.CreateTable<作者>();db.CreateTable();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 中的单个查询中选择多个表，您可以使用 SelectMulti，例如:
var q = db.From().Join().Join().Select((b,a) => new { b, a });var 结果 = db.SelectMulti(q); 作为属性名称遵循参考约定 不需要显式指定它们的连接，因为它们可以隐式推断.
这将返回一个List，然后您可以使用字典将所有作者与他们的书籍拼接起来:
var booksMap = new Dictionary();结果.Each(t => {if (!booksMap.TryGetValue(t.Item1.Id, out var dto))bookMap[t.Item1.Id] = dto = t.Item1.ConvertTo();如果(dto.Authors == null)dto.Authors = new List();dto.Authors.Add(t.Item2);}); 我们可以从 Dictionary Values 中获取书籍列表:
var dtos = booksMap.Values;dtos.PrintDump();书籍在何处填充其作者并打印出来:
<预><代码>[{编号:1，书名:第 1 册，作者:[{编号:1，姓名: 作者 A},{编号:2，姓名: 作者 B}]},{编号:2，书名:第 2 册，作者:[{编号:2，姓名: 作者 B}]},{编号:3，书名:第 3 册，作者:[{编号:2，姓名: 作者 B}]}]自动查询
AutoQuery 只能实现它可以自动化的隐式查询，如果您需要进行任何自定义查询或您需要提供自定义自动查询实现的预测，因为可以隐式推断连接，您可以让 AutoQuery 构造连接查询，这样您只需提供自定义 Select() 投影和自己映射，例如:
[Route("/books/query")]公共类 QueryBooks : QueryDb,IJoin,IJoin{}公共类 MyQueryServices :服务{公共 IAutoQueryDb 自动查询 { 获取；放;}//覆盖自定义实现公共对象 Any(QueryBooks 查询){var q = AutoQuery.CreateQuery(query, base.Request).Select((b,a) => new { b, a });var 结果 = db.SelectMulti(q);var bookMap = new Dictionary();结果.Each(t => {if (!booksMap.TryGetValue(t.Item1.Id, out var dto))bookMap[t.Item1.Id] = dto = t.Item1.ConvertTo();如果(dto.Authors == null)dto.Authors = new List();dto.Authors.Add(t.Item2);});返回新的 QueryResponse{ 结果 = booksMap.Values.ToList() };}} Lets say I have 3 classes:
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;}
}
There is a many-to-many relationship between books and authors.
This is common issue for app I am currently building and I need to give a DTO like this to front end:
public class BookDto
{
public int Id {get; set;}
public string Title {get; set;}
public list<Author> Authors {get; set;}
}
The front end needs the Author embedded. I need a way of getting the Authors nested inside the DTO in a single query.
Is this possible?
I've added a live example to do what you want you can play with on Gistlyn.
In OrmLite every Data Model class maps 1:1 with the underlying table and there's no magic support for M:M queries, you have to use them as the different tables as their stored in the RDBMS.
Also every table needs a unique Primary Id in OrmLite which is missing in BookAuthor which I've added, I've also added a [UniqueConstraint] to enforce no duplicate relationships, with these changes the resulting classes looks like:
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; }
}
Then create tables and add some sample data:
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 });
Then to select multiple tables in a single query in OrmLite you can use SelectMulti, 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);
As the property names follows the reference conventions their joins don't need to be explicitly specified as they can be implicitly inferred.
This will return a List<Tuple<Book,Author>> which you can then use a dictionary to stitch all the authors with their books:
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);
});
We can get the list of books from the Dictionary Values:
var dtos = booksMap.Values;
dtos.PrintDump();
Where the books are populated with its Authors and prints out:
[
{
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
}
]
}
]
AutoQuery
AutoQuery can only implement implicit queries that it can automate, if you need to do any custom queries or projections you would need to provide a custom AutoQuery implementation, since the joins can be implicitly inferred it's possible you could let AutoQuery construct the joined query so you only have to provide the custom Select() projection and mapping yourself, e.g:
[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() };
}
}
这篇关于如何使用自动查询返回多对多关系的嵌套对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
