Huge performance difference - EF Core/Linq/C# vs 'raw SQL'

Question

Hello

I have a query using EF Core and Linq in C#.

I can paraphrase the query as selecting the average item value for items that appear on an order within a date range (Order has many Items, Item belongs to 1 Order):

    var q = this.DbSet   
        .Where(i => i.TenantId == tenantId)   
        .Where(i => i.Order.StartsAt >= orderStartsOnOrAfter && i.Order.StartsAt < orderStartsBefore)   
        .Average(i => i.Value);   

My production database - which is hosted on Azure Sql Server - has several hundred thousand items and thousands of orders.

This query takes several minutes to execute as above, when run within my app. DataIO is reported as very heavy in that time.

I have analysed the resulting SQL - it seems sensible. The database tables are well index, there are no performance recommendations.

When I connect to the same database, from the same location, in Azure Data Studio and run equivalent SQL, the result is returned in a second or two. This is many orders of magnitude faster.

Can you please advise on why there is such a huge difference in performance and steps I can take to make the app's query perform as fast as the 'raw SQL' version.

I have checked query plans and query caches - they seem to be working as I would expect.

Thank you.