LINQ, how to add group by

Question

I made a working request, but the output data is duplicated. I think that it is necessary to add a GROUP BY book ID for this.

Code:

var result = from b in _context.Book
             join oi in _context.OrderItem on b.Id equals oi.Fk_BookId
             join o in _context.Order on oi.Fk_OrderId equals o.Id
             join u in _context.Users on o.UserID equals u.Id
             where EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) >= 17 && EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) <= 27
             orderby b.Id
             select new BookVM()
             {
                 Id = b.Id,
                 Title = b.Title,
                 Pages = b.Pages

             };

I made a similar request in mysql, and with group by everything works right.

SELECT book.Id, book.Title FROM book 
JOIN orderitem ON book.Id = orderitem.Fk_BookId 
JOIN webbookshop.order ON webbookshop.order.Id = orderitem.Fk_OrderId 
JOIN aspnetusers ON aspnetusers.Id = webbookshop.order.UserId
WHERE TIMESTAMPDIFF(YEAR, aspnetusers.DateofBirth, CURDATE()) >=17 AND TIMESTAMPDIFF(YEAR, aspnetusers.DateofBirth, CURDATE()) <= 27 GROUP BY book.Id ORDER BY book.Id asc;

How can I add a GROUP BY here or something else that can remove duplicate data? Thank you.

Given that we cannot look into your head and find out *what* you want to group by... are you just looking for where to put the group by clause? What did you try? Did you get any errors? — nvoigt, Jul 11 '22 at 05:31
Alsom this does not seem to have anything to do with asp.net, but instead entity framework. You may want to edit your tags to reflect this. I would, but I don't know what versions you are working with. — nvoigt, Jul 11 '22 at 05:32
Does this answer your question? [Group by in LINQ](https://stackoverflow.com/questions/7325278/group-by-in-linq) — Nitin Sawant, Jul 11 '22 at 05:40
Assuming that you're using Entity Framework, first start using navigation properties. You'll probably find out that group isn't necessary at all. — Gert Arnold, Jul 11 '22 at 06:20

Nitin Sawant · Accepted Answer · 2022-07-11T06:24:24.277

If the records are duplicating, you can use Distinct()

var result = (from b in _context.Book
         join oi in _context.OrderItem on b.Id equals oi.Fk_BookId
         join o in _context.Order on oi.Fk_OrderId equals o.Id
         join u in _context.Users on o.UserID equals u.Id
         where EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) >= 17 && EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) <= 27
         orderby b.Id
         select new BookVM()
         {
             Id = b.Id,
             Title = b.Title,
             Pages = b.Pages

         }).Distinct();

or

var result = (from b in _context.Book
         join oi in _context.OrderItem on b.Id equals oi.Fk_BookId
         join o in _context.Order on oi.Fk_OrderId equals o.Id
         join u in _context.Users on o.UserID equals u.Id
         where EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) >= 17 && EF.Functions.DateDiffYear(u.DateofBirth, DateTime.Now) <= 27
         orderby b.Id
         select b).GroupBy(x => new {Id = x.Id, Title = x.Title, Pages = x.Pages}).Select(b => new BookVM()
         {
             Id = b.Key.Id,
             Title = b.Key.Title,
             Pages = b.Key.Pages
         })

Thanks for the answer. For some reason, Order by doesnt work in the first option, but for me this is important. The second one gives an error with fields of BookWithAuthorsVM: 'IGrouping' does not contain a definition for 'Id' and no accessible extension method 'Id' accepting a first argument of type 'IGrouping' could be found (are you missing a using directive or an assembly reference?) — Adrian, Jul 11 '22 at 05:57

LINQ, how to add group by

1 Answers1