none
Remove Order By SQL generated statement by .GroupJoin Linq EF RRS feed

  • 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
    Moderator
  • 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
    Moderator
  • 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