Using order by clause with skip and take fails

Question

I am using EF Core for my .NET Core 2.1 project to interact with Azure SQL Server.

I have query which fetches around 1.5L rows by joining multiple tables. It's taking a long time to return the result and it is getting timed out (more than 30 seconds). So I am going with pagination using skip and take.

So my final call looks like

query.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();

This call works absolutely fine and give the expected results.

But when I add order by clause to this query:

query.OrderBy(sortField)
     .Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList()

Below is the generated SQL

SELECT [p].[Id], [p].[CreatedById], [p].[CreatedOn], [p].[CurrencyId], [p].[CustomerId], [p].[DataMigrationLogId], [p].[FollowUp], [p].[IsActive], [p].[ProjectName], [p].[PlantCode], [p].[ShipToDistanceFromPlant], [p].[StatusId], 
[p].[UpdatedById], [p].[UpdatedOn], [p.DataMigrationLog].[Id], [p.DataMigrationLog].[CreatedOn], [p.DataMigrationLog].[GeneratedOn], [p.DataMigrationLog].[HasEdsBid], [p.DataMigrationLog].[HasMbiBid], [p.DataMigrationLog].[Log], 
[p.DataMigrationLog].[RequestXml], [p.DataMigrationLog].[Status], [p.DataMigrationLog].[Xq1ProjectId], [p.UpdatedBy].[Id], [p.UpdatedBy].[CellPhone], [p.UpdatedBy].[CreatedOn], [p.UpdatedBy].[Discriminator], [p.UpdatedBy].[Email], 
[p.UpdatedBy].[FirstName], [p.UpdatedBy].[HasAccessToDoors], [p.UpdatedBy].[HasAccessToWindows], [p.UpdatedBy].[IsActive], [p.UpdatedBy].[LastName], [p.UpdatedBy].[Prefix], [p.UpdatedBy].[RecentProjectId], [p.UpdatedBy].[UpdatedOn], 
[p.UpdatedBy].[WorkPhone], [p.UpdatedBy].[XQ1LoginName], [p.UpdatedBy].[IsSuperAdmin], [p.UpdatedBy].[HasAccessToRestrictedReports], [p.UpdatedBy].[HasEdsProgramAndDealerPriceAccess], [p.UpdatedBy].[IsIss], 
[p.UpdatedBy].[ProjectVisibilityId], [p.UpdatedBy].[CustomerId], [p.UpdatedBy].[IsApiUser], [p.CreatedBy].[Id], [p.CreatedBy].[CellPhone], [p.CreatedBy].[CreatedOn], [p.CreatedBy].[Discriminator], [p.CreatedBy].[Email],
[p.CreatedBy].[FirstName], [p.CreatedBy].[HasAccessToDoors], [p.CreatedBy].[HasAccessToWindows], [p.CreatedBy].[IsActive], [p.CreatedBy].[LastName], [p.CreatedBy].[Prefix], [p.CreatedBy].[RecentProjectId], [p.CreatedBy].[UpdatedOn],
[p.CreatedBy].[WorkPhone], [p.CreatedBy].[XQ1LoginName], [p.CreatedBy].[IsSuperAdmin], [p.CreatedBy].[HasAccessToRestrictedReports], [p.CreatedBy].[HasEdsProgramAndDealerPriceAccess], [p.CreatedBy].[IsIss],
[p.CreatedBy].[ProjectVisibilityId], [p.CreatedBy].[CustomerId], [p.CreatedBy].[IsApiUser], [p.Status].[Id], [p.Status].[Code], [p.Status].[Name], [p.Customer].[Id], [p.Customer].[AxCustomerNumber], [p.Customer].[CreatedById],
[p.Customer].[CreatedOn], [p.Customer].[CreditTermId], [p.Customer].[CurrencyId], [p.Customer].[CustomerTypeId], [p.Customer].[DefaultPricing], [p.Customer].[FabricSystemFreight], [p.Customer].[IsActive], [p.Customer].[IsSpecialFreight],
[p.Customer].[LocalityRepId], [p.Customer].[MinFreightCharge], [p.Customer].[CompanyName], [p.Customer].[Notes], [p.Customer].[PrimaryBusinessId], [p.Customer].[ProgramAccountId], [p.Customer].[Prospect], 
[p.Customer].[ShippingDollarsThreshold], [p.Customer].[ShippingMilesThreshold], [p.Customer].[SpecialNote], [p.Customer].[SpecialPlantInstruction], [p.Customer].[UpdatedById], [p.Customer].[UpdatedOn], 
[p.Customer].[Xq1ProgGroupName], [p.Customer].[Xq1SrNo], [p.Customer].[XqCustomerNumber], [p.Customer.ProgramAccount].[Id], [p.Customer.ProgramAccount].[AxCustomerNumber], [p.Customer.ProgramAccount].[CreatedById], 
[p.Customer.ProgramAccount].[CreatedOn], [p.Customer.ProgramAccount].[CreditTermId], [p.Customer.ProgramAccount].[CurrencyId], [p.Customer.ProgramAccount].[CustomerTypeId], [p.Customer.ProgramAccount].[DefaultPricing],
[p.Customer.ProgramAccount].[FabricSystemFreight], [p.Customer.ProgramAccount].[IsActive], [p.Customer.ProgramAccount].[IsSpecialFreight], [p.Customer.ProgramAccount].[LocalityRepId], [p.Customer.ProgramAccount].[MinFreightCharge],
[p.Customer.ProgramAccount].[CompanyName], [p.Customer.ProgramAccount].[Notes], [p.Customer.ProgramAccount].[PrimaryBusinessId], [p.Customer.ProgramAccount].[ProgramAccountId], [p.Customer.ProgramAccount].[Prospect], 
[p.Customer.ProgramAccount].[ShippingDollarsThreshold], [p.Customer.ProgramAccount].[ShippingMilesThreshold], [p.Customer.ProgramAccount].[SpecialNote], [p.Customer.ProgramAccount].[SpecialPlantInstruction],
[p.Customer.ProgramAccount].[UpdatedById], [p.Customer.ProgramAccount].[UpdatedOn], [p.Customer.ProgramAccount].[Xq1ProgGroupName], [p.Customer.ProgramAccount].[Xq1SrNo], [p.Customer.ProgramAccount].[XqCustomerNumber],
[p.Customer.CustomerType].[Id], [p.Customer.CustomerType].[Code], [p.Customer.CustomerType].[Name]
FROM [Projects] AS [p]
LEFT JOIN [DataMigrationLogs] AS [p.DataMigrationLog] ON [p].[DataMigrationLogId] = [p.DataMigrationLog].[Id]
INNER JOIN [Security].[XQUsers] AS [p.UpdatedBy] ON [p].[UpdatedById] = [p.UpdatedBy].[Id]
INNER JOIN [Security].[XQUsers] AS [p.CreatedBy] ON [p].[CreatedById] = [p.CreatedBy].[Id]
INNER JOIN [ProjectStatuses] AS [p.Status] ON [p].[StatusId] = [p.Status].[Id]
LEFT JOIN [Customers] AS [p.Customer] ON [p].[CustomerId] = [p.Customer].[Id]
LEFT JOIN (
    SELECT [p.Customer.LocalityRep].*
    FROM [Security].[XQUsers] AS [p.Customer.LocalityRep]
    WHERE [p.Customer.LocalityRep].[Discriminator] = N'SALES_SP'
) AS [t] ON [p.Customer].[LocalityRepId] = [t].[Id]
LEFT JOIN [Customers] AS [p.Customer.ProgramAccount] ON [p.Customer].[ProgramAccountId] = [p.Customer.ProgramAccount].[Id]
LEFT JOIN (
    SELECT [p.Customer.ProgramAccount.LocalityRep].*
    FROM [Security].[XQUsers] AS [p.Customer.ProgramAccount.LocalityRep]
    WHERE [p.Customer.ProgramAccount.LocalityRep].[Discriminator] = N'SALES_SP'
) AS [t0] ON [p.Customer.ProgramAccount].[LocalityRepId] = [t0].[Id]
LEFT JOIN [CustomerTypes] AS [p.Customer.CustomerType] ON [p.Customer].[CustomerTypeId] = [p.Customer.CustomerType].[Id]
WHERE ([p.UpdatedBy].[Discriminator] IN (N'INT_SYSADMMIN', N'XqInternalUser', N'EXT', N'SALES_SP', N'XqUser') AND [p.CreatedBy].[Discriminator] IN (N'INT_SYSADMMIN', N'XqInternalUser', N'EXT', N'SALES_SP', N'XqUser')) AND (EXISTS (
    SELECT 1
    FROM [Security].[BusinessUserRegions] AS [r]
    WHERE [r].[RegionId] IN (CAST(6 AS bigint), CAST(8 AS bigint), CAST(9 AS bigint)) AND ([t].[Id] = [r].[BusinessUserId])) OR ([p.Customer].[ProgramAccountId] IS NOT NULL AND EXISTS (
    SELECT 1
    FROM [Security].[BusinessUserRegions] AS [r0]
    WHERE [r0].[RegionId] IN (CAST(6 AS bigint), CAST(8 AS bigint), CAST(9 AS bigint)) AND ([t0].[Id] = [r0].[BusinessUserId]))))
ORDER BY [p].[CreatedOn]

It takes more than 30 seconds to execute and Azure SQL Server has query timeout of maximum 30 second, so it results in exception

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

From my layman's understanding about SQL I think SQL us fetching the entire dataset for sorting and then trying to do pagination and hence it's taking more than 30 seconds. Please correct me if I am wrong.

I need to sort the result in query in self otherwise it will not provide the desired result set.

Is their any way to deal with this situation.

Any help is highly appreciated.