Search DataTable using LINQ

Question

           table
Sr. No. | Arrival    | Departure  | 
-----------------------------------
1       | 10/08/2018 | 11/08/2018 |
-----------------------------------
2       | 11/08/2018 | 12/08/2018 |
-----------------------------------
3       | 12/08/2018 | 13/08/2018 |
-----------------------------------
4       | 13/08/2018 | 14/08/2018 |

I have the dataTable name table. and i want to search data greater than 11/08/2018 and show result in dataGridView1 . I am using below code but its not working.

  var dtCurrnet = DateTime.Now.Date;

        EnumerableRowCollection<DataRow> query = from arrvDate in dtReservation.AsEnumerable()
                                     where arrvDate.Field<DateTime>("ArrvDate") > dtCurrnet
                                     select arrvDate;


        DataView view = query.AsDataView();
        DataTable dtTest = view.ToTable(); 

        dataGridView1.DataSource = dtTest;

I am having problem with "where arrvDate.Field("ArrvDate") > dtCurrnet" Getting Error Message "Specified cast is not valid"

https://stackoverflow.com/questions/10855/linq-query-on-a-datatable — Md. Abdul Alim, Aug 13 '18 at 07:45
Hi, and welcome to stack overflow. To maximize the chances of getting help, I would advise to read the guidance on how to ask a good question (https://stackoverflow.com/help/how-to-ask). For this question, can you explain what you mean by 'its not working'? Do you get an error message? — jeroenh, Aug 13 '18 at 07:49
*but its not working* what exactly isn't working? Do you get any errors? — Izzy, Aug 13 '18 at 07:53
Whats the current output/error and whats the expected output — Gauravsa, Aug 13 '18 at 08:09
I am having problem with "where arrvDate.Field("ArrvDate") > dtCurrnet" Getting Error Message "Specified cast is not valid" — Ajeet Kumar Singh, Aug 13 '18 at 18:45
Possible duplicate of [Linq Greater and Less Than operator on string containing date](https://stackoverflow.com/questions/35365740/linq-greater-and-less-than-operator-on-string-containing-date) — Necoras, Aug 13 '18 at 19:02
when i am trying to search other filed like ... Name == "A" it working but when i try to get value greater than current date value , its showing error. i am confessing with typecast — Ajeet Kumar Singh, Aug 13 '18 at 19:32
I got it. the data in dataTable ArrvDate filed is Convert(VARCHAR, date, 103) now i am using date instead VARCHAR — Ajeet Kumar Singh, Aug 13 '18 at 20:50

score 1 · Answer 1 · answered Aug 24 '18 at 00:49

Please make sure that the column type in your table is indeed a DateTime type. I've tried your query using the following test code and it worked just fine. I'm suspecting that the column type of your ArrvDate is not a DateTime, maybe it's a 'String' type.

One way to check the data type is with the following code, although it'll be much easier to set a breakpoint, debug, then inspect your data table

Console.WriteLine(dtReservation.Columns["ArrvDate"].DataType.FullName);

Anyway, following is the code example I used to validate your Linq query; it seems to work just fine.

var dtReservation = new DataTable();

dtReservation.Columns.Add("Sr. No.", typeof(Int32));
dtReservation.Columns.Add("ArrvDate", typeof(DateTime));
dtReservation.Columns.Add("Departure", typeof(DateTime));

dtReservation.Rows.Add(new Object[] { 1, new DateTime(2018, 8, 10), new DateTime(2018, 8, 11) });
dtReservation.Rows.Add(new Object[] { 2, new DateTime(2018, 8, 11), new DateTime(2018, 8, 12) });
dtReservation.Rows.Add(new Object[] { 3, new DateTime(2018, 8, 12), new DateTime(2018, 8, 13) });
dtReservation.Rows.Add(new Object[] { 4, new DateTime(2018, 8, 13), new DateTime(2018, 8, 14) });

var dtCurrnet = DateTime.Now.Date.AddDays(-11);

EnumerableRowCollection<DataRow> query =
    from arrvDate in dtReservation.AsEnumerable()
    where arrvDate.Field<DateTime>("ArrvDate") > dtCurrnet
    select arrvDate;

PS: I had to change your code slightly by subtracting 11 days to have it actually return a row from the query; since it's currently the 23rd and the example values you gave go until the 13th.

Search DataTable using LINQ

1 Answers1