I have 4 tables book => book_genre => genre => genreType. I made a SQL query that displays books related to the specified genreType.

SELECT book.title, genre.name, typegenre.name from book 
JOIN book_genre ON book.id = fk_bookid 
JOIN Genre ON genre.Id = fk_genreid 
JOIN TypeGenre ON Fk_TypeGenreId = TypeGenre.id 
WHERE typegenre.NameForUrl = "Applied-literature" GROUP BY book.id;

Since each book can have several genres, query displayed several equals books, so I made a group by Id to fix it. But how to do it in linq? I remade this query in linq, but I do not understand how to add group by correctly. This is why the problem with displaying equals books several times remained.

public async Task<IEnumerable<BookWithAuthorsVM>> GetAllByTypeGenre(string genre, PaginationParams @params)
{
        var result = await (from b in _context.Book
                     join gb in _context.Book_Genre on b.Id equals gb.Fk_BookId
                     join g in _context.Genre on gb.Fk_GenreId equals g.Id
                     join gt in _context.TypeGenre on g.Fk_TypeGenreId equals gt.Id
                     where gt.NameForUrl == genre
                     select new BookWithAuthorsVM()
                     {
                          Id = b.Id,
                          Title = b.Title,
                          Pages = b.Pages,
                          Format = b.Format,
                          LongDescription = b.LongDescription,
                          ShortDescription = b.ShortDescription,
                          Amount = b.Amount,
                          Price = b.Price,
                          ImageUrl = b.ImageUrl,
                          IsFavor = b.IsFavor,
                          ResealeDate = b.ResealeDate,
                          PublisherName = b.Publisher.Name,
                          AuthorNames = b.Book_Author.Select(n => n.Author.FullName).ToList(),
                          GenreNames = b.Book_Genre.Select(g => g.Genre.Name).ToList()
                      }).ToListAsync();

         var items = result.Skip((@params.Page - 1) * @params.ItemsPerPage)
         .Take(@params.ItemsPerPage);
         return items;
}

Classes of my model:

    public class Book
    {
        [Key]
        public int Id { get; set; }
        public string Title { get; set; }
        public int Pages { get; set; }
        public string Format { get; set; }
        public string LongDescription { get; set; }
        public string ShortDescription { get; set; }
        public int Amount { get; set; }
        public float Price { get; set; }
        public string ImageUrl { get; set; }
        public bool IsFavor { get; set; }
        public DateTime? ResealeDate { get; set; }

        public int Fk_PublisherId { get; set; }
        [ForeignKey("Fk_PublisherId")]
        public Publisher Publisher { get; set; }

        public List<Book_Author> Book_Author { get; set; }
        public List<Book_Genre> Book_Genre { get; set; }
    }

    public class Book_Genre
    {
        public int Fk_BookId { get; set; }
        public Book Book { get; set; }
        public int Fk_GenreId { get; set; }
        public Genre Genre { get; set; }
    }

    public class Genre
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string NameForUrl { get; set; }
        public string Description { get; set; }
        public int Fk_TypeGenreId { get; set; }
        [ForeignKey("Fk_TypeGenreId")]
        public TypeGenre TypeGenre { get; set; }
        public List<Book_Genre> Book_Genre { get; set; }
    }

    public class TypeGenre
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string NameForUrl { get; set; }
        public string Description { get; set; }
        public List<Genre> Genre { get; set; }
    }

ViewModel that I use in the query:

    public class BookWithAuthorsVM
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public int Pages { get; set; }
        public string Format { get; set; }
        public string LongDescription { get; set; }
        public string ShortDescription { get; set; }
        public int Amount { get; set; }
        public float Price { get; set; }
        public string ImageUrl { get; set; }
        public bool IsFavor { get; set; }
        public DateTime? ResealeDate { get; set; }
        public string PublisherName { get; set; }
        public List<string> AuthorNames { get; set; }
        public List<string> GenreNames { get; set; }
    }

Please help me to correctly add the group by to the request. Thank you.

CodePudding user response：

Try the following query:

var query = 
    from b in _context.Book
    where b.Book_Genre.Any(bg => bg.Genre.TypeGenre.NameForUrl == genre)
    select new BookWithAuthorsVM()
    {
        Id = b.Id,
        Title = b.Title,
        Pages = b.Pages,
        Format = b.Format,
        LongDescription = b.LongDescription,
        ShortDescription = b.ShortDescription,
        Amount = b.Amount,
        Price = b.Price,
        ImageUrl = b.ImageUrl,
        IsFavor = b.IsFavor,
        ResealeDate = b.ResealeDate,
        PublisherName = b.Publisher.Name,
        AuthorNames = b.Book_Author.Select(n => n.Author.FullName).ToList(),
        GenreNames = b.Book_Genre.Select(g => g.Genre.Name).ToList()
    };