CODEWITHSUNDEEP
c#asp.net-mvcdatetimedatatable
Tsar
Tsar

Reputation: 170

Querying from a DataTable to retrieve information based on DateTime

My question might be a bit long coz i've tried to explain the context behind it.

I have a DataTable (retrieved from a database) which contains the following data:

----------------------------------
-  State -         Date          -
==================================
-   on   -  02/01/2016 15:38:13  -
----------------------------------
-   off  -  22/01/2016 16:45:33  -
----------------------------------
-   on   -  27/01/2016 10:30:16  -
----------------------------------
-   off  -  01/02/2016 10:03:43  -
----------------------------------

I need to check the state depending on the date. Any date in between will have the previous date's state. Example: 29/01/2016 will have the state as 'On'.

I tried to query from the DataTable as such :

DateTime test = DateTime.Parse("27 / 01 / 2016"); //temporary part
var queryResults = table.AsEnumerable()
                           .Where(x => DateTime.Parse(x.Field<string>("Date")).Date == test);

Right now that query above returns the row where the condition is fulfilled. If i change the where statement in the query to Where(x => DateTime.Parse(x.Field<string>("Date")).Date <= test); it returns the first three rows.

My question is .. If i change the test value to 28/01/2016 with the less than or equal query it will still return the first three rows. How can i get the last one only? The last state.

Any help is much appreciated :)

Upvotes: 1

Views: 182

Answers (2)

Mir Gulam Sarwar
Mir Gulam Sarwar

Reputation: 2648

You should order by them in descending date then get the first one

 queryResults = table.Where(x => DateTime.Parse(x.Field<string>("Date")).Date <= test).OrderByDescending(y=>y.Field<string>("Date")).FirstOrDefault();

Upvotes: 0

user3559349
user3559349

Reputation:

Use .LastOrDefault() to get the last item in the collection

var queryResults = table.Where(x => DateTime.Parse(x.Field<string>("Date")).Date <= test).LastOrDefault();

Sid enote: You should check for null before accessing the State property

Upvotes: 2

Related Questions