I skimmed the original article. Did it mention the database engine being used? That SQL looks pretty horrible, performance wise. Especially if it's hand written.

What application is generating those screenshots? I want it :)

why do you say that the query has to evaluate all possible results to just take top few? The order is on publish date , filter on soft deleted, so if you have an index on these there should be no need to check all the records.

Anyway, over the last several years i'm gradually transitioning from 'everything in the application/use ORM and forget about SQL' back to 'just do it in SQL and nothing will beat it'. Dont know about your experiences, but after repeatedly having to meticulously build and adjust linq queries to generate the sql i'd like to see (and never mention the fact that its too easy to find Linq constructs unsupported by the provider) i started wondering why go through this at all if i already know what the query should be. And another thing is transactions, its so easy to shoot yourself in the foot when you try to make some transactional operations in the application code..

"I don’t think that there is much that can be done here, given the relational nature of the data access "...

There's still a ton of low hanging fruit in that query. Any time you look at the query plan and see a table being accessed multiple times, and/or multiple index seeks for one statement there's a potential for improvement.

First off, without even changing the SQL, you can create a covering index on Review.BookId, Review.NumStars so you're not scanning two indexes.

Next, combine the subqueries to one so you're not hitting Review twice for no reason:

SELECT TOP(@pageSize) [b].[BookId], [b].[Title], 
     [b].[Price], [b].[PublishedOn], 
CASE
    WHEN [p.Promotion].[PriceOfferId] IS NULL
    THEN [b].[Price] ELSE [p.Promotion].[NewPrice]
END AS [ActualPrice], 
[p.Promotion].[PromotionalText] AS [PromotionPromotionalText], 
[dbo].AuthorsStringUdf([b].[BookId]) AS [AuthorsOrdered], 
r.*
FROM [Books] AS [b]
LEFT JOIN [PriceOffers] AS [p.Promotion] 
    ON [b].[BookId] = [p.Promotion].[BookId]
OUTER APPLY (
    SELECT
        COUNT(*) AS [ReviewsCount], 
         AVG(CAST([y].[NumStars] AS float)) AS [ReviewsAverageVotes]
    FROM [Review] AS [y]
    WHERE [b].[BookId] = [y].[BookId]
) r
WHERE ([b].[SoftDeleted] = 0) 
ORDER BY [ReviewsAverageVotes] DESC

I would also try selecting the page of reviews first and then joining in Book, but the SoftDeleted check may preclude that depending on whether the requirements are to return exactly @pageSize records or @pageSize or less.

Oren... in the source article you referenced, step 2 of 4 was using Dapper to run an optimized query. It's about optimizing SQL access in general, not EF core in particular -- 2/3 of the article is non EF ways to optimize, or ways to use EF to warehouse fields vs reading them.

That's a standard technique with OR/M's -- use them for CRUD where performance isn't an issue, but once you're optimizing it only makes sense to hand code SQL vs throwing up your hands and saying "I don’t think that there is much that can be done here, given the relational nature of the data access".

At that point, it's not about generating an EF query, but a SQL query, and I'd bet that an optimized query would be pretty close to the warehoused version -- at least close enough to prefer it.

The last picture is too vague to see clearly.