asp.netlinq按id分組

我有 4 個表 book => book_genre => Genre => GenreType。我做了一個 SQL 查詢，顯示與指定流派型別相關的書籍。

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;

由于每本書可以有幾種型別，查詢顯示了幾本相等的書，所以我按 Id 做了一個組來修復它。但是如何在linq中做到這一點？我在 linq 中重新制作了這個查詢，但我不明白如何正確添加 group by。這就是為什么多次顯示 equals 書籍的問題仍然存在。

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;
}

我的模型類：

    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：

    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; }
    }

請幫助我正確地將組添加到請求中。謝謝你。

uj5u.com熱心網友回復：

嘗試以下查詢：

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()
    };

標籤：C＃ 网 。网 林克

上一篇：從一個串列中選擇全部，替換另一個串列中存在的值

下一篇：呼叫“DbSet<>.Find”的位置0處的鍵值是“字串”型別，與“長？”的屬性型別不匹配。