-
|
Hello! I'm trying to get related entities from a single query but I cannot make it work :( OverviewI have a simple scenario with 3 entity types: recipe, book and author. I have a many-to-many relation between the book table and the author table. Tablesauthor
book
recipe
book_author (for the Many-To-Many relation)
Classes[Table("author")]
public class Author : BaseModel
{
[PrimaryKey("id")]
public int Id { get; set; }
[Column("first_name")]
public string FirstName { get; set; } = "";
[Column("last_name")]
public string LastName { get; set; } = "";
[Reference(typeof(Book), useInnerJoin: false, includeInQuery: true)]
public List<Book> Books { get; set; } = default!;
}[Table("book")]
public class Book : BaseModel
{
[PrimaryKey("id")]
public int Id { get; set; }
[Column("title")]
public string Title { get; set; } = "";
[Reference(typeof(Author), includeInQuery: true, useInnerJoin: false)]
public List<Author> Authors { get; set; } = default!;
}[Table("recipe")]
public class Recipe : BaseModel
{
[PrimaryKey("id")]
public int Id { get; set; }
[Column("name")]
public string Name { get; set; } = "";
[Reference(typeof(Book), joinType: ReferenceAttribute.JoinType.Left, true)]
public Book? Book { get; set; }
[Column("page")]
public int? Page { get; set; }
}The QuestionI want to retrieve all the books by an author, I've tried some linq, filters and it never work... I wish I could do something simple as this: public async Task<IEnumerable<Book>> GetBooksByAuthor(int authorId)
{
var result = await client
.From<Book>()
.Where(b => b.Authors.Contains(authorId)
.Get();
}Any clue to point me in the right direction? |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment · 2 replies
-
|
You need to specify an inner join such as below. I also had a problem getting my head around the querying format, it doesn't work like linq as it does not translate it into a sql query like a traditional direct linq query to the SQL DB. The other (more expensive) alternative is to bring back all the books and then you can use linq to filter the "Models" collection as each Book item will have it's Author filled. But beware that each item in Models has a null 'BaseUrl' hence you can't use that item to call Update on it. Good luck |
Beta Was this translation helpful? Give feedback.
-
|
Thanks @s3312345 ! With your small code snippet, I manage to get it to work! |
Beta Was this translation helpful? Give feedback.
-
|
@s3312345 I've been struggling with correctly linking my many-to-many table for over a week now and your small code snippet was the answer I've been looking for. Thank you for saving my sanity! |
Beta Was this translation helpful? Give feedback.
You need to specify an inner join such as below. I also had a problem getting my head around the querying format, it doesn't work like linq as it does not translate it into a sql query like a traditional direct linq query to the SQL DB.
The other (more expensive) alternative is to bring back all the books and then you can use linq to filter the "Models" collection as each Book item will have it's Author filled. But beware that each item in Models has a null 'BaseUrl' hence you can't use that item to call Update on it.
Good luck