How to get data from a Many-To-Many relation

[mongeon]

Hello!

I'm trying to get related entities from a single query but I cannot make it work :(

Overview

I 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.

Tables

author

• Id (pk)
• First Name
• Last Name

book

• id (pk)
• title

recipe

• id (pk)
• name
• book_id (fk)
• page

book_author (for the Many-To-Many relation)

• book_id (pk, fk)
• author_id (pk, fk)

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 Question

I 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?

[s3312345]

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

await client
            .From<Book>()
              .Select("*, Author_info:Author!inner(*)")
              .Filter("Author.id", Constants.Operator.Equals, authorId)
              .Get();
		

[mongeon]

Thanks @s3312345 !

With your small code snippet, I manage to get it to work!

[baileyfrye1]

@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!