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

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.

 

Answer 1

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};

Answer 2

 

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!

Answer 3

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.