Querying Datatable DBNull using Linq Query

Question

I have a DataSet which contains a datatable called Vacs and it has a date column called VacDate which in some cases it holds null value.

Now when i try to query it using linq query i got the following exception

The value for column \'VacDate \' in table \'Vacs\' is DBNull

My Code is:

var Vacs = new MyDataSetTableAdapter.MyVacsTableAdapter();
DataTable Vacations = Vacs.GetData()
       .Where(s => s.VacDate == DateTime.Now)
       .ToList()
       .CopyToDataTable();

I did try the following but still getting the same error

DataTable Vacations = Vacs.GetData()
       .Where(s => s.VacDate != null && s.VacDate == DateTime.Now)
       .ToList()
       .CopyToDataTable();

I believe the solution would be simple but still i dont see it till now.

Thank you in advance.

Best regards...

Answer 1

It's hard to tell what is implementation of your MyVacsTableAdapter class. Seems like problem is in it's implementation - when you get VacDate property value you are not handling DbNull case. Anyway - it's easy to do such filtering with LINQ to DataSet. If Vacs is simple DataTable then creating new DataTable with only rows matching some condition looks like:

DataTable allVacations = new DataTable(); // get filled DataTable
DataTable nextVacations = allVacations.AsEnumerable()
                             .Where(r => r.Field<DateTime?>("VacDate") >= DateTime.Today)
                             .CopyToDataTable();

LINQ can handle DbNull case and convert cell value to appropriate data type.

Note: You are using DateTime.Now in your condition check. It's very unlikely that some time value will perfectly match ticks of current time at the moment of comparison. You should probably use >= or <= operator here. Or compare just date.