Remove Order By SQL generated statement by .GroupJoin Linq EF

Question

Hi,

Could somebody help me to know how use .GroupJoin() without generate the Order By

Here is part of the generated SQL statemen.

[Project1].[Step2] AS [Step2],

[Project1].[Step3] AS [Step3],

[Project1].[SequenceType] AS [SequenceType]

FROM ( SELECT

                [Extent1].[Id] AS [Id],

                [Extent1].[FacilityId] AS [FacilityId],

                [Extent1].[Gender] AS [Gender],

...

INNER JOIN [dbo].[HD_Location] AS [Extent4] ON [Extent3].[LocationId] = [Extent4].[Id]

                INNER JOIN [dbo].[HD_LocationAssociation] AS [Extent5] ON [Extent4].[Unit] = [Extent5].[ReferringLocationKey]

                LEFT OUTER JOIN [dbo].[ActionItems] AS [Extent6] ON ([Extent6].[Type] IN (N'DIS',N'TX')) AND ([Extent2].[VisitId] = [Extent6].[PatientId])

                WHERE ((([Extent2].[FacilityId] = @p__linq__0) AND (N'Registered' = [Extent2].[Status])) OR (N'Admitted' = [Extent2].[Status])) AND ([Extent1].[FacilityId] = @p__linq__1) AND (N'Discharged' <> [Extent2].[Status]) AND ([Extent4].[FacilityId] = @p__linq__2) AND (N'Inactive' <> [Extent4].[Status])

)  AS [Project1]

ORDER BY [Project1].[Id] ASC, [Project1].[Id1] ASC, [Project1].[Id2] ASC, [Project1].[Id3] ASC, [Project1].[Id5] ASC, [Project1].[C1] ASC

I don't want the sub-query just for order my result set.

Thanks in advance

Peter

Answer 1

Hi Peter,

Could you please provide your LINQ query with GroupJoin method? 

Good day!

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thank you Michael for your interest in this issue.

Here is the LINQ statement

 var assignedPatientsQuery = locations.GroupJoin(
                        locationAssociations,
                        x => x.Unit,
                        y => y.ReferringLocationKey,
                        (x, y) => new
                        {
                            Location = x,
                            LocationAssociations = y.DefaultIfEmpty()
                        })
                        .SelectMany(a => a.LocationAssociations.Select(x => new { Location = a.Location, LocationAssociation = x }))
                        .GroupJoin(
                        patientLocations,
                        x => x.Location.Id,
                        y => y.LocationId,
                        (x, y) => new
                        {
                            LocationAssociation = x,
                            PatientLocation = y.DefaultIfEmpty()
                        })
                        .SelectMany(a => a.PatientLocation.Select(x => new { LocationAssociation = a.LocationAssociation, PatientLocation = x }))
                        .GroupJoin(
                        activePatients,
                        x => x.PatientLocation.PatientId,
                        y => y.patientVisitResultRow.VisitId,
                        (x, y) => new
                        {
                            PatientLocation = x,
                            PatientVisit = y.DefaultIfEmpty()
                        })
                        .SelectMany(a => a.PatientVisit.Select(x => new { PatientLocation = a.PatientLocation, PatientVisit = x })
                        .Where(x => x.PatientLocation.LocationAssociation.Location.Unit == strLocation)
                        .GroupJoin(
                        _actionItems,
                        x => x.PatientVisit.patientVisitResultRow.VisitId,
                        y => y.PatientId,
                        (x, y) => new
                        {
                            ResultRow = x,
                            ActionItemRow = y
                        }));

Peter

Answer 3

Hi Peter,

I will involve some senior engineer to check this issue. 

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

The way to achieve this would be using the following article-

http://social.msdn.microsoft.com/Forums/en-SG/adodotnetentityframework/thread/5a071357-7d00-450e-8d29-12f8cfee5407