none
OrderBy Problem RRS feed

  • Question

  • Hi,

    I'm trying to modify code that a contractor wrote for us, but I've never worked with linq before.  All I want to do is sort the data by the text description. I've been working on this for the last couple of days with no luck.  Can someone please tell me how to accomplish this sort?  This is driving me crazy!

    I want to order the data based on SubRegionDesc.


    if (RegionID > 0)
                    {
                        var query = (from s in ctx.CDM_Region_SubRgn_XRs
                                     join sr in ctx.CDM_SubRegions on s.SubRegionID equals sr.SubRegionID
                                     join srl in ctx.CDM_SubRegion_Locales on sr.SubRegionID equals srl.SubRegionID into SubRegionLocales
                                     where s.RegionID == RegionID                                
                                     select new SubRegion
                                    
                                     {
                                         SubRegionType = (sr.SubRegionCode == "state" ? SubRegionType.State : SubRegionType.Country),
                                         SubRegionCode = sr.SubRegionAK,
                                         SubRegionID = s.SubRegionID                                     ,
                                         Locales = SubRegionLocales.Where(l => cultures.Count == 0 || cultures.Contains(l.CultureID)).Select(l => new SubRegionLocale
                                         {
                                             SubRegionID = l.SubRegionID,
                                             CultureID = l.CultureID,
                                             Description = l.SubRegionDesc,
                                             SortOrder = l.SortOrder,
                                             SortAlpha = l.SortAlpha,
                                             ZoneDisplay = l.ZoneDisplay,
                                             DateCreated = l.DateCreated,
                                             DateUpdated = l.DateUpdated
                                         }).ToList()
                                     }
                                    
                                                                     
                                     );


                        subRegionsList = query.ToList();
                    }

    Thanks,
    C.R.
    Tuesday, April 21, 2009 4:16 PM

Answers

  • You are constructing a hierarchy in the LINQ query, since for every subregion you are producing a single object that has a collection of locales.  The query you have written is explicitly orderig the items in the collection.  Since each locale has a SubRegionDesc which one would you use to order the SubRegion with?  So either you want a different query with different results or possibly there is a problem in your data model.
    Wayward LINQ Lacky
    • Marked as answer by C_R Friday, April 24, 2009 1:28 PM
    Thursday, April 23, 2009 3:58 PM
    Moderator

All replies

  • If you're ordering the inner sequence (Locales), you'd put at the end of the .Select: .OrderBy(l => l.Description).ToList(), instead of just .ToList()
    Wednesday, April 22, 2009 1:36 AM
  • Thanks for the response jessic4h!

    This is what I've been trying to figure out (how to add code to order by Description).  The OrderBy somewhat works.  The results returned to the drop-down list on the web form were unexpected.    It alphabetizes the countries in the drop-down, but not all of them. 

    Example:  All the countries that start with M are grouped together, but they are not truely alphabetical. (Mail, Moldova, Malta, Morocco, Mauritania, Mauritus,).
    Also, there are a handfull of countries that are totally out of order. (Yemen, Yugoslavia, Zambia, Zimbabwe, United Kingdom).

    This is the code that I'm currently running:



                    {
                        var query = (from s in ctx.CDM_Region_SubRgn_XRs
                                     join sr in ctx.CDM_SubRegions on s.SubRegionID equals sr.SubRegionID
                                     join srl in ctx.CDM_SubRegion_Locales on sr.SubRegionID equals srl.SubRegionID into SubRegionLocales
                                     where s.RegionID == RegionID
                                     select new SubRegion
                                     {
                                         SubRegionType = (sr.SubRegionCode == "state" ? SubRegionType.State : SubRegionType.Country),
                                         SubRegionCode = sr.SubRegionAK,
                                         SubRegionID = s.SubRegionID,
                                         Locales = SubRegionLocales.Where(l => cultures.Count == 0 || cultures.Contains(l.CultureID)).Select(l => new SubRegionLocale
                                         {
                                             SubRegionID = l.SubRegionID,
                                             CultureID = l.CultureID,
                                             Description = l.SubRegionDesc,
                                             SortOrder = l.SortOrder,
                                             SortAlpha = l.SortAlpha,
                                             ZoneDisplay = l.ZoneDisplay,
                                             DateCreated = l.DateCreated,
                                             DateUpdated = l.DateUpdated
                                         }).OrderBy(l => l.Description).ToList()
                                     });


                        subRegionsList = query.ToList();                   
                    }

    Thanks for your help!
    C.R.
    Wednesday, April 22, 2009 2:11 PM
  • Which are out of order, the sub regions or the sub region locales?   You are ordering the locales per sub-region but are not ordering the sub-regions themselves.
    Wayward LINQ Lacky
    Wednesday, April 22, 2009 3:23 PM
    Moderator
  • I want the results sorted by SubRegionDesc. 

    I placed the following in LINQPage:

                        int RegionID = 2;
                        List<int> cultures = null;
                        if (cultures == null)
                        {
                            cultures = new List<int> {};
                        }

                        var query = (from s in CDM_Region_SubRgn_XRs
                                     join sr in CDM_SubRegions on s.SubRegionID equals sr.SubRegionID
                                     join srl in CDM_SubRegion_Locales on sr.SubRegionID equals srl.SubRegionID into SubRegionLocales

                                     where s.RegionID == RegionID
                                     select new
                                     {
                                         SubRegionCode = sr.SubRegionAK,
                                         SubRegionID = s.SubRegionID,
                                         Locales = SubRegionLocales.Where(l => cultures.Count == 0 || cultures.Contains(l.CultureID)).Select(l => new
                                         {
                                             SubRegionID = l.SubRegionID,
                                             CultureID = l.CultureID,
                                             Description = l.SubRegionDesc,
                                             SortOrder = l.SortOrder,
                                             SortAlpha = l.SortAlpha,
                                             ZoneDisplay = l.ZoneDisplay,
                                             DateCreated = l.DateCreated,
                                             DateUpdated = l.DateUpdated
                                         }).OrderBy(l => l.Description).ToList()
                                     });


                        query.ToList().Dump();
                        //OrderBy<subRegionsList.OrderBy<subRegionsLis                   


    When I view the SQL results, it gives me the following:
    -- Region Parameters
    DECLARE @p0 Int SET @p0 = 2
    -- EndRegion
    SELECT [t1].[SubRegionAK] AS [SubRegionCode], [t0].[SubRegionID], [t2].[SubRegionID] AS [SubRegionID2], [t2].[CultureID], [t2].[SubRegionDesc] AS [Description], [t2].[SortOrder], [t2].[SortAlpha], [t2].[ZoneDisplay], [t2].[DateCreated], [t2].[DateUpdated], (
        SELECT COUNT(*)
        FROM [CDM_SubRegion_Locale] AS [t3]
        WHERE [t1].[SubRegionID] = [t3].[SubRegionID]
        ) AS [value]
    FROM [CDM_Region_SubRgn_XR] AS [t0]
    INNER JOIN [CDM_SubRegion] AS [t1] ON [t0].[SubRegionID] = [t1].[SubRegionID]
    LEFT OUTER JOIN [CDM_SubRegion_Locale] AS [t2] ON [t1].[SubRegionID] = [t2].[SubRegionID]
    WHERE [t0].[RegionID] = @p0
    ORDER BY [t0].[RegionID], [t0].[SubRegionID], [t1].[SubRegionID], [t2].[SubRegionDesc], [t2].[CultureID]


    If I were modifying the SQL, I would want the following for the Order By:

    ORDER BY [t2].[SubRegionDesc]

    I don't really care what comes after [t2].[SubRegionDesc].

    Thanks,
    C.R.


    Wednesday, April 22, 2009 6:23 PM
  • You are constructing a hierarchy in the LINQ query, since for every subregion you are producing a single object that has a collection of locales.  The query you have written is explicitly orderig the items in the collection.  Since each locale has a SubRegionDesc which one would you use to order the SubRegion with?  So either you want a different query with different results or possibly there is a problem in your data model.
    Wayward LINQ Lacky
    • Marked as answer by C_R Friday, April 24, 2009 1:28 PM
    Thursday, April 23, 2009 3:58 PM
    Moderator
  • Thanks for you help Matt. After reviewing and understanding what is going on, it looks like there is a problem with the data model and the query will have to be re-written.
    Friday, April 24, 2009 1:28 PM