CODEWITHSUNDEEP
vb.netlinq
user1431213
user1431213

Reputation: 31

VB LINQ datatable - latest dates in the ascending order

I have the following table and data:

Date  |Symbol|Return
Jan 1 |   A  |  1%
Jan 2 |   A  |  2%
Jan 3 |   A  |  4%
Jan 5 |   A  |  0%
Jan 6 |   A  | -1%
Jan 7 |   A  | -3%
Jan 8 |   A  |  5%

The table is already sorted in ascending order. What I'm trying to accomplish is to query for the last/latest 3 records and sort them in ascending order by date:

Date  |Symbol|Return
Jan 6 |   A  | -1%
Jan 7 |   A  | -3%
Jan 8 |   A  |  5%

Here's my code:

Dim ReturnArray = (From row In DT_Symbols.AsEnumerable()
    Where row.Field(Of String)("Symbol") = Symbol.Key 'Dictionary Key
    Take 3
    Select row.Field(Of Double)("Return")).Distinct()

The problem with the above is that when I Order By row.Field(Of Date)("Date") Ascending I'm getting Jan1, Jan2, & Jan3 dates and that's not what I want.

When I Order By row.Field(Of Date)("Date") Descending - I get correct dates (Jan 6, 7, & 8) BUT in wrong order (Jan 8,7,6).

Can you please suggest a workaround?

Thank you!

Upvotes: 0

Views: 364

Answers (2)

ilans
ilans

Reputation: 2727

Use Skip.

Dim ReturnArray = (From row In DT_Symbols
Where row.Field(Of String)("Symbol") = Symbol.Key 'Dictionary Key
OrderBy Date
Skip DT_Symbols.Count - 3
Take 3
Select row.Field(Of Double)("Return")).Distinct()

Upvotes: 0

Nate Barbettini
Nate Barbettini

Reputation: 53670

Consider revising your code to:

Dim ReturnArray =
    (From row In DT_Symbols
    Where row.Field(Of String)("Symbol") = Symbol.Key 'Dictionary Key
    Take 3
    Select row.Field(Of Double)("Return")).Distinct().ToList().Reverse()

You shouldn't need to call AsEnumerable() at the beginning. At the end, we call ToList() to execute the query and return the 3 distinct results, then use Reverse() to order them descending (as Andrei suggested).

Upvotes: 1

Related Questions