none
OrderBy x is not working if x is not in select clause? RRS feed

  • Question

  • Hi,

    I wrote simple linq below to get top frequently used data sorted by count. But orderby had no impact on result.

    Then just to check, i have un-commented '//, count = procedureGroup.Count()' and it worked as expected?

    In SQL it is not mandatory to have columns in order by clause to be in select clause. Looks like LinQ is expecting so.

    Am i missing something?

    var favoritesServiceItems =
                    (from pp in m_ProcedureRepository.GetPatientProcedures()
                     join p in GetProceduresQuery(serviceItemLibraryId, queryOptions) on pp.service_item_id equals p.service_item_id
                     group p by p into procedureGroup
                     orderby procedureGroup.Count() descending
                     select new
                            {
                                service_item_id = procedureGroup.Key.service_item_id,
                                delete_ind = procedureGroup.Key.delete_ind,
                                cpt4_code_id = procedureGroup.Key.cpt4_code_id,
                                description = procedureGroup.Key.description,
                                hidden_flag = procedureGroup.Key.hidden_flag
                                //, count = procedureGroup.Count()
                            }).ToList();

    Friday, February 24, 2017 3:45 PM

Answers

  • Your query compiles and executes just fine, so...what's the problem?

    Here I made a bunch of fake data to try it out.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    
    class PatientProcedure
    {
        internal object cpt4_code_id;
        internal object delete_ind;
        internal object description;
        internal object hidden_flag;
        internal int service_item_id;
    }
    
    class ProcedureRepository
    {
        internal IEnumerable<PatientProcedure> GetPatientProcedures()
        {
            return new PatientProcedure[] {
                    new PatientProcedure() { cpt4_code_id = 101 },
                    new PatientProcedure() { cpt4_code_id = 102 },
                    new PatientProcedure() { cpt4_code_id = 103 }
                    };
        }
    }
    
    class Program
    {
        private static IEnumerable<PatientProcedure> GetProceduresQuery( object serviceItemLibraryId, object queryOptions )
        {
            var a = new PatientProcedure() { cpt4_code_id = 101 };
            var b = new PatientProcedure() { cpt4_code_id = 102 };
            return new PatientProcedure[] {
                    a, a, a, b, b, b, b // more b's than a's to verify order by is working
                    };
        }
    
        static void Main( string[] args )
        {
            var m_ProcedureRepository = new ProcedureRepository();
            object serviceItemLibraryId = null;
            object queryOptions = null;
            var favoritesServiceItems =
                            (from pp in m_ProcedureRepository.GetPatientProcedures()
                             join p in GetProceduresQuery( serviceItemLibraryId, queryOptions ) on pp.service_item_id equals p.service_item_id
                             group p by p into procedureGroup
                             orderby procedureGroup.Count() descending
                             select new
                             {
                                 service_item_id = procedureGroup.Key.service_item_id,
                                 delete_ind = procedureGroup.Key.delete_ind,
                                 cpt4_code_id = procedureGroup.Key.cpt4_code_id,
                                 description = procedureGroup.Key.description,
                                 hidden_flag = procedureGroup.Key.hidden_flag
                                 //, count = procedureGroup.Count()
                             }).ToList();
            Console.WriteLine( string.Join( Environment.NewLine, favoritesServiceItems ) );
        }
    }
    

    Perhaps you did not intend to write "group p by p"  It is, after all, suspicious to require that the same instance of an object occur multiple times in your GetProceduresQuery results.  Note my {a, a, a, b, b, b, b} in the example to verify that it's working to group by object instance.  I provided 3 a's and 4 b's so the b's get listed first (because you ordered by group count descending.)

    Friday, February 24, 2017 6:41 PM

All replies

  • Your query compiles and executes just fine, so...what's the problem?

    Here I made a bunch of fake data to try it out.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    
    class PatientProcedure
    {
        internal object cpt4_code_id;
        internal object delete_ind;
        internal object description;
        internal object hidden_flag;
        internal int service_item_id;
    }
    
    class ProcedureRepository
    {
        internal IEnumerable<PatientProcedure> GetPatientProcedures()
        {
            return new PatientProcedure[] {
                    new PatientProcedure() { cpt4_code_id = 101 },
                    new PatientProcedure() { cpt4_code_id = 102 },
                    new PatientProcedure() { cpt4_code_id = 103 }
                    };
        }
    }
    
    class Program
    {
        private static IEnumerable<PatientProcedure> GetProceduresQuery( object serviceItemLibraryId, object queryOptions )
        {
            var a = new PatientProcedure() { cpt4_code_id = 101 };
            var b = new PatientProcedure() { cpt4_code_id = 102 };
            return new PatientProcedure[] {
                    a, a, a, b, b, b, b // more b's than a's to verify order by is working
                    };
        }
    
        static void Main( string[] args )
        {
            var m_ProcedureRepository = new ProcedureRepository();
            object serviceItemLibraryId = null;
            object queryOptions = null;
            var favoritesServiceItems =
                            (from pp in m_ProcedureRepository.GetPatientProcedures()
                             join p in GetProceduresQuery( serviceItemLibraryId, queryOptions ) on pp.service_item_id equals p.service_item_id
                             group p by p into procedureGroup
                             orderby procedureGroup.Count() descending
                             select new
                             {
                                 service_item_id = procedureGroup.Key.service_item_id,
                                 delete_ind = procedureGroup.Key.delete_ind,
                                 cpt4_code_id = procedureGroup.Key.cpt4_code_id,
                                 description = procedureGroup.Key.description,
                                 hidden_flag = procedureGroup.Key.hidden_flag
                                 //, count = procedureGroup.Count()
                             }).ToList();
            Console.WriteLine( string.Join( Environment.NewLine, favoritesServiceItems ) );
        }
    }
    

    Perhaps you did not intend to write "group p by p"  It is, after all, suspicious to require that the same instance of an object occur multiple times in your GetProceduresQuery results.  Note my {a, a, a, b, b, b, b} in the example to verify that it's working to group by object instance.  I provided 3 a's and 4 b's so the b's get listed first (because you ordered by group count descending.)

    Friday, February 24, 2017 6:41 PM
  • Sorry. My bad. I was having data issue. It works fine. The query generated was little weird and I got confused.
    Thursday, March 2, 2017 12:47 PM