locked
GroupBy and OrderBy - Always orders by my group, then my orderby clause RRS feed

  • Question

  •  

    I'm very new to Linq, so any help is greatly appreaciated.  I've now spent 3 days scouring the internet for any example that can help, and I'm still bewildered.

     

    Here is the query I am using:

     

    var query = from attributes in dc.vwProgramAttributes

    where attributes.programID == programID

    orderby attributes.CategorySortOrder, attributes.AttributeSortOrder

    group attributes by attributes.AttributeGroupName into grouping

    select new {AttributeGroupName = grouping.Key, Attributes = grouping};

     

     

    The problem I have is that when I get the results back, it is sorted alphabetically by the AttributeGroupName, then by CategorySortOrder and AttributeSortOrder.  The categorysortorder is unique to each attributeGroupName, so grouping shouldn't create a "distinct" order for any of my group names.

     

    Here is the SQL that creates the view:

     

    select

    p.programID

    , a.attributeID

    , p.ProgramName

    , c.CategoryName

    , ag.AttributeGroupName

    , a.AttributeName

    , a.AttributeDescription

    , pa.AttributeValue

    , at.attributeTypeName

    , ct.SortOrder as CategorySortOrder

    , aga.SortOrder as AttributeSortOrder

    from tbPrograms p (nolock)

    join tbProgramCategory pc (nolock) on pc.programID = p.programID

    join tbCategory c (nolock) on c.categoryID = pc.categoryID

    join tbCategoryTemplate ct (nolock) on ct.CategoryID = c.CategoryID

    join tbAttributeGroup ag (nolock) on ag.attributeGroupID = ct.AttributeGroupID

    join tbAttributeGroupAttribute aga (nolock) on aga.attributeGroupID = ag.AttributeGroupID

    join tbAttribute a (nolock) on a.attributeID = aga.attributeID

    join tbAttributeType at (nolock) on at.attributeTypeID = a.attributeTypeID

    join tbProgramAttribute pa (nolock) on pa.attributeID = a.attributeID and isNull(pa.attributeValue, '') <> ''

     

     

    Perhaps there is a better way for me to create the view to make this easier, I'm not sure.

     

    They end result is I want to display a nested list view with the AttributeGroupNames being the top list, and the attributes and values being the nested list. 

     

    Here is the listview:

     

    <asp:ListView ID="lvAttributes" runat="server">

    <LayoutTemplate>

    <tr runat="server" id="itemPlaceholder"></tr>

    </LayoutTemplate>

    <ItemTemplate>

    <tr id="Tr1" runat="server">

    <td align="left" class="smalltextwhtbk"><div class="header"><%# Eval("AttributeGroupName") %></div></td>

    </tr>

    <asp:ListView ID="lvAttributeDetail" runat="server" DataSource='<%# DataBinder.Eval(Container.DataItem, "Attributes") %>' >

    <LayoutTemplate>

    <tr runat="server" id="itemPlaceholder"></tr>

    </LayoutTemplate>

    <ItemTemplate>

    <tr id="tr1" runat="server">

    <td class="smalltextwhtbk">-<%# DataBinder.Eval(Container.DataItem, "AttributeName") %>: <%# DataBinder.Eval(Container.DataItem, "AttributeValue") %> </td>

    </tr>

    </ItemTemplate>

    </asp:ListView>

    </ItemTemplate>

    <AlternatingItemTemplate>

    <tr id="Tr1" runat="server">

    <td align="left" class="smalltextgrtbk"><div class="header"><%# Eval("AttributeGroupName")%></div></td>

    </tr>

    <asp:ListView ID="lvAttributeDetail" runat="server" DataSource='<%# DataBinder.Eval(Container.DataItem, "Attributes") %>' >

    <LayoutTemplate>

    <tr runat="server" id="itemPlaceholder"></tr>

    </LayoutTemplate>

    <ItemTemplate>

    <tr id="tr1" runat="server">

    <td class="smalltextgrtbk">-<%# DataBinder.Eval(Container.DataItem, "AttributeName") %>: <%# DataBinder.Eval(Container.DataItem, "AttributeValue") %> </td>

    </tr>

    </ItemTemplate>

    </asp:ListView>

    </AlternatingItemTemplate>

    </asp:ListView>

     

    Let me know if more information is helpful for this.

     

    Monday, September 15, 2008 8:09 PM

Answers

  • When you use an order-by operator before a group-by you end up only specifing the ordering of the items within each group.  The ordering of the groups themselves are non-deterministic as per SQL behavior.  You can apply an additional order-by after the group-by to order the groups specificly.  However, the only information you have immediately available is the grouping key or an aggregate over the items in each group. 

     

    Since your CategorySortOrder is a member of each element, are they always the same for each group?  If so, you could use a Min or Max aggregate for ordering.

     

    var query = from attributes in dc.vwProgramAttributes

    where attributes.programID == programID

    orderby attributes.AttributeSortOrder

    group attributes by attributes.AttributeGroupName into grouping

    orderby grouping.Max(x => x.CategorySortOrder)

    select new {AttributeGroupName = grouping.Key, Attributes = grouping};

    Tuesday, September 16, 2008 5:10 AM

All replies

  • When you use an order-by operator before a group-by you end up only specifing the ordering of the items within each group.  The ordering of the groups themselves are non-deterministic as per SQL behavior.  You can apply an additional order-by after the group-by to order the groups specificly.  However, the only information you have immediately available is the grouping key or an aggregate over the items in each group. 

     

    Since your CategorySortOrder is a member of each element, are they always the same for each group?  If so, you could use a Min or Max aggregate for ordering.

     

    var query = from attributes in dc.vwProgramAttributes

    where attributes.programID == programID

    orderby attributes.AttributeSortOrder

    group attributes by attributes.AttributeGroupName into grouping

    orderby grouping.Max(x => x.CategorySortOrder)

    select new {AttributeGroupName = grouping.Key, Attributes = grouping};

    Tuesday, September 16, 2008 5:10 AM
  •  

    That was exactly the problem!  I don't understand WHY it works, but it does.  Looks like I better go get a book JUST on Linq.  The couple I have for asp.net touch on it, but don't even come close to showing the heavy issues like this.

     

    I've looked for online resources to go in depth on this, and so far, haven't found anything beyound just an "overview".

     

    Thanks again!

    Tuesday, September 16, 2008 1:50 PM
  • Ok, one more question.  I am a die hard stored procedure person.  Normally, I would not query from a view like this, I would use a stored procedure.

     

    So, the question is, in linq, can I group and sort if I use a stored procedure??  I guess I've been trained so long to seperate my data layer from my business layer I am just having a hard time putting SQL on the actual code behind lol.

     

    Thanks again for all your help.

     

    Tuesday, September 16, 2008 10:23 PM