CODEWITHSUNDEEP
sqlentity-frameworksql-order-byentity-framework-6
Devsined
Devsined

Reputation: 3521

How can I avoid the Entity Framework 6 add an ORDER BY in the generated query

Using EF 6 I am selecting a result set in the way bellow. The data is selected by id and then is also getting information form nested objects which are from related tables. My problem is that the generated query is adding OrderBy which I don't need. How can we set EF to not order my result set?

using (var repository = new Repository(uow))
      {
        var result= repository.FindAllIncluding(includeParams)
                              .Where(tr=>tr.Id==Id)
                              .FirstOrDefault();
        FillModel(modelToReturn, result);
      }

The fragment of the generated SQL is this and at the end you can see that is Ordering By ITreatmenetRequestId:

DECLARE @p__linq__0 uniqueidentifier = 'E72C5916-5CF1-4FDD-AF59-CCCCCCCCCC'

SELECT 
    [Project2].[statecode] AS [statecode], 
    ...
    [Project2].[EffectiveTo] AS [EffectiveTo], 
    [Project2].[PayerMemberNumber] AS [PayerMemberNumber], 
    [Project2].[LineOfBusiness] AS [LineOfBusiness], 
    [Project2].[statecode2] AS [statecode2]
    FROM ( SELECT 
        ...
        [Limit1].[Gender] AS [Gender], 
        [Limit1].[DateOfBirth] AS [DateOfBirth], 
        [Limit1].[WriteInStatus] AS [WriteInStatus], 
        [Limit1].[statecode1] AS [statecode1], 
        [Join2].[MemberPayerEligibilityId] AS [MemberPayerEligibilityId], 
        [Join2].[MemberId1] AS [MemberId2], 
        [Join2].[PayerId] AS [PayerId1], 
        [Join2].[EffectiveFrom] AS [EffectiveFrom], 
        [Join2].[EffectiveTo] AS [EffectiveTo], 
        [Join2].[PayerMemberNumber] AS [PayerMemberNumber], 
        [Join2].[LineOfBusiness] AS [LineOfBusiness], 
        [Join2].[statecode1] AS [statecode2], 
        CASE WHEN ([Join2].[MemberPayerEligibilityId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM   (SELECT TOP (1) 
          ...
            [Extent2].[MiddleName] AS [MiddleName], 
            [Extent2].[LastName] AS [LastName], 
            [Extent2].[Gender] AS [Gender], 
            [Extent2].[DateOfBirth] AS [DateOfBirth], 
            [Extent2].[WriteInStatus] AS [WriteInStatus], 
            [Extent2].[statecode] AS [statecode1]
            FROM  [dbo].[TreatmentRequestBase] AS [Extent1]
            LEFT OUTER JOIN [dbo].[MemberBase] AS [Extent2] ON [Extent1].[MemberId] = [Extent2].[MemberId]
            WHERE [Extent1].[TreatmentRequestId] = @p__linq__0 ) AS [Limit1]
        LEFT OUTER JOIN
        (SELECT [Extent3].[MemberPayerEligibilityId] AS [MemberPayerEligibilityId],
            [Extent3].[MemberId] AS [MemberId1], [Extent3].[PayerId] AS [PayerId], [Extent3].[EffectiveFrom] AS [EffectiveFrom],
            [Extent3].[EffectiveTo] AS [EffectiveTo], [Extent3].[PayerMemberNumber] AS [PayerMemberNumber],
            [Extent3].[LineOfBusiness] AS [LineOfBusiness], [Extent3].[statecode] AS [statecode1]
            ,[Extent4].[MemberId] AS [MemberId2]
            FROM  [dbo].[MemberPayerEligibilityBase] AS [Extent3]
            INNER JOIN [dbo].[MemberBase] AS [Extent4] ON [Extent4].[MemberId] = [Extent3].[MemberId]
            ) AS [Join2] ON [Limit1].[MemberId] = [Join2].[MemberId1]
    )  AS [Project2]
    
    ORDER BY [Project2].[TreatmentRequestId] ASC, [Project2].[MemberId1] ASC, [Project2].[C1] ASC

Upvotes: 9

Views: 872

Answers (1)

BoltBait
BoltBait

Reputation: 11489

You are asking for "FirstOrDefault()"... The word "First" is meaningless without order. Try using "SingleOrDefault()" instead. There are some limitations, of course. If your query returns more than 0 or 1 rows it will generate an error.

Upvotes: 1

Related Questions