Asked by:
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
Wednesday, December 12, 2012 3:00 PM
All replies
-
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.Thursday, December 13, 2012 2:10 PM -
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
Thursday, December 13, 2012 3:41 PM -
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.Monday, December 17, 2012 6:22 AM -
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
Monday, January 7, 2013 2:57 PM