linq using order by, where, and select vb.net

Question

I have a Linq query that I am passing to a list, and then to the view through the viewbag. I am trying to keep that list in a specific order, so that when I iterate through it I have control over the order in which it's displayed.

Here is the query:

ViewBag.attributes = (From row In db.tblCategory_Attributes
                                 Where row.Item_Type_Identifier = itemType
                                 Order By row.Category_Attribute_Identifier
                                 Select CStr(row.Attribute_Name)
                                 Distinct).ToList()

I am successfully passing this list to the view and iterating through it, but no matter what the values are always displayed in alphabetical order. Category_Attribute_Identifier is an integer that aligns with the order I would like these values to be displayed in.

I've played around with the order of my statements quite a bit and I'm not having any luck.

Can you tell me how to distinctly select the Attribute_Name's that correlate with my specific Item_Type_Identifier and order my results by the Category_Attribute_Identifier?

Gert Arnold · Accepted Answer · 2015-07-31T19:06:12.187

2

The Distinct is creating its own ordering again (because it shuffles through the result to filter out duplicates). Just do the sorting after the Distinct:

(From row In db.tblCategory_Attributes
 Where row.Item_Type_Identifier = itemType
 Select row
 Distinct)
 .OrderBy(Function(row) row.Category_Attribute_Identifier)
 .Select(Function(row) CStr(row.Attribute_Name))

edited Jul 31 '15 at 19:06

answered Jul 31 '15 at 18:56

Gert Arnold

105,341
31
202
291

trying to implement this I encountered error: `category_attribute_Identifier` is not a member of string. So I added `Category_Attribute_Identifer` to the select statement as an `and`. Afterwards I get error: CatAttID is not a member of Long. Any thoughts? – rogerdeuce Jul 31 '15 at 19:03

Olivier Jacot-Descombes · Answer 2 · 2015-07-31T20:13:54.517

1

Try using Group By instead of Distinct

ViewBag.attributes = (From row In db.tblCategory_Attributes _
                      Where row.Item_Type_Identifier = itemType _
                      Order By row.Category_Attribute_Identifier) _
                    .AsEnumerable() _
                    .GroupBy(Function(r) r.Attribute_Name) _
                    .Select(Function(g) g.Key) _
                    .ToList()

Or use the extension method syntax which gives you the freedom of applying the extension methods in any order:

ViewBag.attributes = db.tblCategory_Attributes _
    .Where(Function(row) row.Item_Type_Identifier = itemType) _
    .Select(Function(row) New With {row.Attribute_Name, row.Category_Attribute_Identifier}) _
    .Distinct() _
    .OrderBy(Function(a) a.Category_Attribute_Identifier) _
    .Select(Function(a) a.Attribute_Name) _
    .ToList()

This simple test demonstrates that GroupBy preserves the order:

Public Shared Sub TestGroupOrder()
    Dim a = New Integer() {6, 2, 4, 2, 7, 5, 3, 4}
    Dim query = a.GroupBy(Function(i) i).[Select](Function(g) g.Key)
    For Each i As Integer In query
        Console.Write("{0} ", i)
    Next
End Sub

Result in the console:

6 2 4 7 5 3

edited Jul 31 '15 at 20:13

answered Jul 31 '15 at 19:09

Olivier Jacot-Descombes

104,806
13
138
188

Grouping after ordering has the same effect. – Gert Arnold Jul 31 '15 at 19:12
According to http://stackoverflow.com/questions/204505/preserving-order-with-linq: "GroupBy - The IGrouping objects are yielded in an order based on the order of the elements in source that produced the first key of each IGrouping. Elements in a grouping are yielded in the order they appear in source. " – Olivier Jacot-Descombes Jul 31 '15 at 19:38
Yes, so that's `Attribute_Name`, but the required ordering is `Category_Attribute_Identifier`. – Gert Arnold Jul 31 '15 at 19:41
I don't think this means that the groups are reordered by the key, but that the order in which the keys are encountered first is kept. – Olivier Jacot-Descombes Jul 31 '15 at 19:45
The proof of the pudding... I know by experience that in LINQ-to-Entities the sort order of grouping key determines the ordering, but even then I never count on it. Often the ordering is even totally ignored in the generated SQL. – Gert Arnold Jul 31 '15 at 19:48
Remember that LINQ-to-Entities is not the same as LINQ in memory. It's really different. Just try. – Gert Arnold Jul 31 '15 at 20:02
Okay, I aggree on that. I added a `AsEnumerable()` in between, in order to separate the LINQ-to-EF from the LINTO-to-Objects part. – Olivier Jacot-Descombes Jul 31 '15 at 20:15

linq using order by, where, and select vb.net

2 Answers2