Situation :

I used a Dataset to map to my Access Database. Once Dataset created we can use it with LINQ like we use LINQ to SQL or LINQ to Entity.

Problem:

My database allowed to have columns with DB Null specially in case with Dates. Though its not a good idea to have nulls in Date column but its required in certain situation. Next I wrote the following LINQ Query

MSDS.ppMasterDataTable ppmtb = new MSDS.ppMasterDataTable();

MSDSTableAdapters.ppMasterTableAdapter ta = new LQ2DS.MSDSTableAdapters.ppMasterTableAdapter();
ta.Fill(ppmtb);

var q = from s in ppmtb
select new { s.ppID, s.ppDate };

Array arr = q.ToArray();  // Exception

When I tried executing var q to get an array , There was an exception thrown because data coloumns had DBnull.

The problem was when I created Dataset, the date columns were mapped with System.DateTime which doesn’t accept null value. So whenever the query was executed, it was trying to create an object by assigning null value. This resulted in an exception.

Solution:

I wasnt able to find any direct solution for this but there is an alternate method which worked for me. System.DateTime doesnt accept date but System.Data.SqlTypes.SqlDateTime does. I replaced all instances of System.DateTime with System.Data.SqlTypes.SqlDateTime in the Dataset I created initially to map the access database.  You might also have to update column property setting for these columns in the designer by allowing columns to accept null.

Result :

After this there was no problem and I had my var q executing for any of the cases even for creating arrays.