Question 87435 in ASP.NET Question posted in
The official documentation can be found here.

Solution for complex LINQ query with ROW_NUMBER() and PARTITION BY – Asp.net

This is my first question. For a school assignment I’m writing a program in ASP.net MVC with Rider. It is gonna be cinema webapp. The query gets the show which is played in every hall at the moment. So, for 6 halls I have 6 Id’s and all of the ID’s should give me back:

  • HallId
  • MovieTitle
  • Showtime (Starttime)

The code I build was this and it works in my Query-console:

SELECT "HallId", "Title", "StartAt" 
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "HallId"  ORDER BY "StartAt") rn
      FROM "Showtime" where "StartAt"::time < now()::time) x
JOIN "Movie" M ON "MovieId" = M."Id"
WHERE x.rn = 1
ORDER BY "HallId"

I need a LINQ-query for this, but I couldn’t get it working. I use Postgres by the way. That is why the “”.
Does someone has a answer for me?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This was my solution:

    After a long search, I found the next (magical) solution. Works like hell for me:

    public IEnumerable<Showtime> MovieNext(){

  return _context.Showtime
   .FromSqlRaw("SELECT tbl.* FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY "HallId" ORDER BY "StartAt") row 
    FROM myDb."Showtime" 
    WHERE "StartAt" > now()) tbl 
    JOIN myDb."Movie" M ON "MovieId" = M."Id" 
    WHERE tbl.row = 1 ORDER BY "HallId"");
}

  2. 0

    your question is not clear enough about the columns names but you can use the same as following linq query

    var result = 
(from s in  dbentities.Showtime 
join r in  dbEntities.Movie on s.Mid equals r.Mid
where s.StartAt < DateTime.Now && r.rn == 1).ToList();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search