Solution for complex LINQ query with ROW_NUMBER() and PARTITION BY

Question

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?

I'm not aware of any LINQ query providers that support window functions. You may have exceeded the bounds of what LINQ can generate for you. Consider using a view or a stored procedure. — madreflection
– madreflection, Commented Mar 1, 2022 at 20:02
Well, at the moment I use the above SQL query but the problem is that I get the data from the showtime table, but it won’t return the movie table. It works in my Query Tool, I get all the needed data. But when I pass it to the View via my controller it just gives empty Movie values. I get only the Showtime values — iFritsWester
– iFritsWester, Commented Mar 1, 2022 at 20:07
Does this answer your question? Row_number over (Partition by xxx) in Linq? — Developer
– Developer, Commented Mar 1, 2022 at 20:14

Developer · Accepted Answer · 2022-03-01 19:38:51Z

1

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

answered Mar 1, 2022 at 19:38

Developer

2911 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

madreflection Over a year ago

How, exactly, does that apply the ROW_NUMBER() window function? rn is synthesized in the derived table that's aliased as x; it's not a column in the Showtime table so r.rn won't exist.

Developer Over a year ago

you can use row.SERRIA_NUMBER

madreflection Over a year ago

Doing a google search on SERRIA_NUMBER, the question that comes up had a SERRIA_NUMBER column, so that doesn't appear to be of any use here, 1) because it doesn't exist here, and 2) because wasn't functionally equivalent to the ROW_NUMBER function in that other question.

Developer Over a year ago

refer to this you will get full idea about the row_number and partition in linq stackoverflow.com/questions/9980568/…

madreflection Over a year ago

In that case, you should suggest a duplicate instead of posting an answer that doesn't work and a link that provides all the relevant details.

iFritsWester · Accepted Answer · 2022-03-02 10:26:08Z

1

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

answered Mar 2, 2022 at 10:26

iFritsWester

911 silver badge7 bronze badges

Collectives™ on Stack Overflow

Solution for complex LINQ query with ROW_NUMBER() and PARTITION BY

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related