How to use ORDER BY COUNT in LINQ and SELECT COUNT it?

Question

I have a query and I don't know change it to LINQ

select distinct m.id,m.name, sch.id as schedule, COUNT(tk.id) as tiketSold
from movies m, schedules sch, tickets tk
where m.id = sch.movie_id and sch.id = tk.schedule_id
group by m.id,m.name, sch.id
order by COUNT(tk.id) desc

I'm trying:

var hotMovie = from m in _db.movies
                           from sch in _db.schedules
                           from tk in _db.tickets
                           where m.id == sch.movie_id && sch.id == tk.schedule_id
                           group m by m.id into g
                           orderby g.Count()
                           select new { Movie = g};

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jan 06 '21 at 19:18

score 0 · Answer 1 · answered Jan 06 '21 at 03:54

I do not have your database, so, I have created 3 classes like your table as I can anticipate. Then I have created 3 list like you table in the "TestMethod". In the linq query, I have joined the 3 list as you shown in sql query segment "where m.id = sch.movie_id and sch.id = tk.schedule_id" and then I perform the group by, order by an select. Here is my code, please try it and let me know it works or not.

 public class movies
{
    public int id { get; set; }
    public string name { get; set; }
 
}

public class schedules
{
    public int id { get; set; }
    public int movie_id { get; set; }
}

public class tickets
{
    public int id { get; set; }
    public int schedule_id { get; set; }
}
 void TestMethod()
 {
        //Add Movies to the list
        List<movies> moviesItems = new List<movies>();
        moviesItems.Add(new movies() { id = 1, name = "A" });
        moviesItems.Add(new movies() { id = 2, name = "B" });

        //Add Schedules to the list
        List<schedules> schedulesItems = new List<schedules>();
        schedulesItems.Add(new schedules() { id = 1, movie_id = 1 });
        schedulesItems.Add(new schedules() { id = 2, movie_id = 2 });
        schedulesItems.Add(new schedules() { id = 3, movie_id = 1 });
        schedulesItems.Add(new schedules() { id = 4, movie_id = 2 });

        //Add Tickets to the list
        List<tickets> ticketsItems = new List<tickets>();
        ticketsItems.Add(new tickets() { id = 1, schedule_id = 1 });
        ticketsItems.Add(new tickets() { id = 2, schedule_id = 1 });
        ticketsItems.Add(new tickets() { id = 3, schedule_id = 2 });
        ticketsItems.Add(new tickets() { id = 4, schedule_id = 2 });
        ticketsItems.Add(new tickets() { id = 5, schedule_id = 2 });
        ticketsItems.Add(new tickets() { id = 6, schedule_id = 3 });
        ticketsItems.Add(new tickets() { id = 7, schedule_id = 3 });
        ticketsItems.Add(new tickets() { id = 8, schedule_id = 3 });
        ticketsItems.Add(new tickets() { id = 9, schedule_id = 3 });
        ticketsItems.Add(new tickets() { id = 10, schedule_id = 4 });

        var query = from final in (from m in moviesItems
                                   join sch in schedulesItems on m.id equals sch.movie_id
                                   join tk in ticketsItems on sch.id equals tk.schedule_id
                                   select new { movieID = m.id, movieName = m.name, schID = sch.id, tkID = tk.id })
                    group final by new { final.movieID, final.movieName, final.schID } into g
                    orderby g.Count() descending
                    select new { g.Key.movieID, g.Key.movieName, g.Key.schID, tiketSold = g.Count() };
}

score 0 · Answer 2 · answered Jan 06 '21 at 06:31

This query is closest to your SQL but probably you need LEFT JOIN. Also it can be simplified using navigation properties if you provide model.

var hotMovie = 
   from m in _db.movies
   join sch in _db.schedules on m.id equals sch.movie_id
   join tk in _db.tickets on sch.id equals tk.schedule_id
   group tk by new { movieID = m.id, movieName = m.name, scheduleId = sch.id } into g
   orderby g.Sum(x => x.id != null ? 1 : 0) descending
   select new 
   { 
      g.Key.movieID, 
      g.Key.movieName, 
      g.Key.scheduleId, 
      tiketSold = g.Sum(x => x.id != null ? 1 : 0) 
   };

How to use ORDER BY COUNT in LINQ and SELECT COUNT it?

2 Answers2