CODEWITHSUNDEEP
entity-framework-core
Francisco Goldenstein
Francisco Goldenstein

Reputation: 13767

EF Core applying TOP (x) in the wrong place - Take and OrderBy

I'm using EF Core 2.1 and the generated SQL query is wrong because it applies the TOP(x) in the wrong place.

This is the EF Core:

            IQueryable<OfferRequest> query = dbContext.OfferRequests;
            if (includes != null)
            {
                query = includes(query);
            }
            if (offerProgressStageId.HasValue)
            {
                query = query.Where(p => p.OfferProgressStageId == offerProgressStageId.Value);
            }
            if (offerStageId.HasValue)
            {
                query = query.Where(p => p.OfferStageId == offerStageId.Value);
            }
            if (isRelatedToStage)
            {
                query = query.Where(p => p.OfferStageId != null);
            }
            else
            {
                query = query.Where(p => p.OfferProgressStageId == null);
            }

            query = query.Where(p => p.OfferId == offerId && p.IsActive);

            if (maxCount.HasValue)
            {
                query = query.Take(maxCount.Value);
            }



            return query.OrderBy(p => p.RequestStatusId).ThenByDescending(p => p.RequestType.DisplayNumber).ToList();

And this is the generated SQL:

declare @__p_1 int = 50, @__offerId_0 int = 1

SELECT [t].[OfferRequestId], [t].[DateCreated], [t].[DateUpdated], [t].[EndDate], [t].[EndUserId], [t].[EventBlock], [t].[IsActive], [t].[IsSpecificToOfferProgressStage], [t].[Message], [t].[OfferId], [t].[OfferProgressStageId], [t].[OfferStageId], [t].[OfferRequestProgressId], [t].[RequestStatusId], [t].[RequestTypeId], [t].[StartDate], [t].[StartUserId], [t].[UserId], [p.RequestType].[RequestTypeId], [p.RequestType].[AllowsFreeText], [p.RequestType].[DateCreated], [p.RequestType].[DateUpdated], [p.RequestType].[Description], [p.RequestType].[DisplayNumber], [p.RequestType].[IsActive], [p.RequestType].[IsMain], [p.RequestType].[ShortDescription], [p.RequestType].[UserId]
FROM (
    SELECT TOP(@__p_1) [p].[OfferRequestId], [p].[DateCreated], [p].[DateUpdated], [p].[EndDate], [p].[EndUserId], [p].[EventBlock], [p].[IsActive], [p].[IsSpecificToOfferProgressStage], [p].[Message], [p].[OfferId], [p].[OfferProgressStageId], [p].[OfferStageId], [p].[OfferRequestProgressId], [p].[RequestStatusId], [p].[RequestTypeId], [p].[StartDate], [p].[StartUserId], [p].[UserId]
    FROM [OfferRequest] AS [p]
    WHERE [p].[OfferProgressStageId] IS NULL AND (([p].[OfferId] = @__offerId_0) AND ([p].[IsActive] = 1))
) AS [t]
INNER JOIN [RequestType] AS [p.RequestType] ON [t].[RequestTypeId] = [p.RequestType].[RequestTypeId]
ORDER BY [t].[RequestStatusId], [p.RequestType].[DisplayNumber] DESC

Also EF complains because I'm doing a TOP(x) without ORDER BY but that's because it's generating the SQL in a wrong way.

Upvotes: 0

Views: 147

Answers (1)

Francisco Goldenstein
Francisco Goldenstein

Reputation: 13767

I solved it by changing the LINQ query a little bit. When you have a Take and an OrderBy it's important to first call OrderBy and then Take.

            IQueryable<OfferRequest> query = dbContext.OfferRequests;
            if (includes != null)
            {
                query = includes(query);
            }
            if (offerProgressStageId.HasValue)
            {
                query = query.Where(p => p.OfferProgressStageId == offerProgressStageId.Value);
            }
            if (offerStageId.HasValue)
            {
                query = query.Where(p => p.OfferStageId == offerStageId.Value);
            }
            if (isRelatedToStage)
            {
                query = query.Where(p => p.OfferStageId != null);
            }
            else
            {
                query = query.Where(p => p.OfferProgressStageId == null);
            }

            query = query.Where(p => p.OfferId == offerId && p.IsActive);

            query = query.OrderBy(p => p.RequestStatusId).ThenByDescending(p => p.RequestType.DisplayNumber);

            if (maxCount.HasValue)
            {
                query = query.Take(maxCount.Value);
            }

            return query.ToList();

Upvotes: 1

Related Questions