convertion issue with DateTime being null in a LINQ query

Question

Here is the configuration of my datatables that I use in my linq query: I have 2 dataset files (all the columns of all the tables have a DataType specified and their AllowDbNull property set to True): * deposit_position_imbalance.xsd: Contains 2 datables : - Imbalance - ImbalanceDetailForRealTime * dep_pos_imbalance_detail.xsd: Contains 1 datatable : - Table

In the code below, the problem lies in the 2 lines "deal_date = b.deal_date". Indeed, when I retrieve from the database b.deal_date that has a null value, it says in deposit_position_imbalance.Designer.cs : "StrongTypingException was unhandled by user code" "The value for column 'deal_date' in table 'ImbalanceDetailForRealTime' is DBNull." "Specified cast is not valid". Here is where it throws the error:

    [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
            public System.DateTime deal_date {
                get {
                    try {
                        return ((global::System.DateTime)(this[this.tableImbalanceDetailForRealTime.deal_dateColumn]));
                    }
                    catch (global::System.InvalidCastException e) {
                        throw new global::System.Data.StrongTypingException("The value for column \'deal_date\' in table \'ImbalanceDetailForRealTime\' is DBNull." +
                                "", e);//ERROR THROWN HERE
                    }
                }
                set {
                    this[this.tableImbalanceDetailForRealTime.deal_dateColumn] = value;
                }
            }

• I have tried to replace the line "deal_date = b.deal_date" by "deal_date = (DateTime?)b.deal_date" But I get 2 compilation errors: "The best overloaded method match for dep_pos_imbalance_detail.TableDataTable.AddTableRow(string, System.DateTime)' has some invalid arguments" and "Argument '2': cannot convert from 'System.DateTime?' to 'System.DateTime'"
• I have also tried to replace the line "deal_date = b.deal_date" by "deal_date = b.deal_date == null ? (DateTime)DBNull.Value : b.deal_date" But I get a compilation error: "Cannot convert type 'System.DBNull' to System.DateTime'"
• I have then tried to replace the line "deal_date = b.deal_date" by "deal_date = b.deal_date == null ? (DateTime?)DBNull.Value : b.deal_date" But I get a compilation error: "Cannot convert type 'System.DBNull' to System.DateTime?'"
• I have tried another thing : replacing "deal_date = b.deal_date" by "deal_date = b.Isdeal_dateNull() ? default(DateTime?) : b.deal_date" But again, I have the following errors: "The best overloaded method match for dep_pos_imbalance_detail.TableDataTable.AddTableRow(string, System.DateTime)' has some invalid arguments" and "Argument '2': cannot convert from 'System.DateTime?' to 'System.DateTime'" The following image (sorry I am not yet allowed to insert an image in stackoverflow, so I put the link instead) shows the definition of my column deal_date in my dataset: https://lh5.googleusercontent.com/-TEZZ9Hdnkl4/T1aRxF_i7II/AAAAAAAAAAg/BwzrVXIlOHE/s323/deal_date.jpg We can see that I don't seem to have the possibility to set "System.DateTime?" but only "System.DateTime". And I don't want anything else than null as default value (do we have to put something else than the default value "" in order to make it work?) UPDATE--> I've tried to put null instead of and the designer gives this error : "The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.".

So I don't understand how I can manage to retrieve null values (I didn't put it in the code, but I have the same problem with the type double). I have the impression that my columns are set to enable null values but obviously not... Also, when I try to modify the NullValue property to go from "(Throw Exception)" to "(Empty)" or "(Null)", the designer gives this error: "The value entered is not valid for the current data type." Thank you for your help. Here is my LINQ query:

deposit_position_imbalance.ImbalanceDataTable dtImbalanceForRealTime;
deposit_position_imbalance.ImbalanceDetailForRealTimeDataTable dtImbalanceDetailForRealTime;

dtImbalanceForRealTime = (deposit_position_imbalance.ImbalanceDataTable)(((deposit_position_imbalance)(dataManager.GetConfig(grid1).ParentDataSource)).Imbalance);
dtImbalanceDetailForRealTime = this.detailForRealTime;

// we separate security_id null and not null
// Security id is not null
deposit_position_imbalance.ImbalanceDataTable iWithSecurityIdNotNull = new deposit_position_imbalance.ImbalanceDataTable();
deposit_position_imbalance.ImbalanceRow[] dr1 = (deposit_position_imbalance.ImbalanceRow[])dtImbalanceForRealTime.Select("security_id is not null");
if (dr1.Count<deposit_position_imbalance.ImbalanceRow>() > 0)
{
    DataTable looselyTypedDT1 = dr1.CopyToDataTable<deposit_position_imbalance.ImbalanceRow>();
    iWithSecurityIdNotNull.Merge(looselyTypedDT1, true);
}

// Security id is null
deposit_position_imbalance.ImbalanceDataTable iWithSecurityIdNull = new deposit_position_imbalance.ImbalanceDataTable();
deposit_position_imbalance.ImbalanceRow[] dr2 = (deposit_position_imbalance.ImbalanceRow[])dtImbalanceForRealTime.Select("security_id is null");
if (dr2.Count<deposit_position_imbalance.ImbalanceRow>() > 0)
{
    DataTable looselyTypedDT2 = dr2.CopyToDataTable<deposit_position_imbalance.ImbalanceRow>();
    iWithSecurityIdNull.Merge(looselyTypedDT2, true);
}

var queryWithSecurityIdFound =
    from a in iWithSecurityIdNotNull
    join b in dtImbalanceDetailForRealTime
    on new
    {
        a.situation_date,
        a.security_id,
        a.deposit_location_id,
        a.account_keeper_id
    }
        equals new
        {
            b.situation_date,
            b.security_id,
            b.deposit_location_id,
            b.account_keeper_id
        }
    where a.situation_date == situation_date
       && a.security_id == security_id
       && a.deposit_location_id == deposit_location_id
       && a.account_keeper_id == account_keeper_id
    select new
    {
        name = a.bo_source_name,
        deal_date = b.deal_date
    };

var queryWithSecurityIdNotFound =
    from a in iWithSecurityIdNull
    join b in dtImbalanceDetailForRealTime
        on new
        {
            a.situation_date,
            a.security_code,
            a.deposit_location_id,
            a.account_keeper_id
        }
        equals new
        {
            b.situation_date,
            b.security_code,
            b.deposit_location_id,
            b.account_keeper_id
        }
    where a.situation_date == situation_date
       && a.security_id == security_id
       && a.deposit_location_id == deposit_location_id
       && a.account_keeper_id == account_keeper_id
    select new
    {
        name = a.bo_source_name,
        deal_date = b.deal_date
    };

var query_final = queryWithSecurityIdFound.Union(queryWithSecurityIdNotFound);
//We fill the 'dep_pos_imbalance_detail Table'
grid1.Clear();
foreach (var item in query_final)
{
    ((dep_pos_imbalance_detail.TableDataTable)grid1.DataSet.Tables["Table"]).AddTableRow(item.name, item.deal_date);
}

Answer 1

I found a way to solve my issue. In my LINQ queries, I replace "deal_date = b.deal_date" by "deal_date = b.Field('deal_date')". The convertion can then be made. I can then not use the Designer auto-generated method "AddTableRow" because it doesn't expect the right types. But I write this, a little bit longer but effective:

dep_pos_imbalance_detail.TableDataTable dt = ((dep_pos_imbalance_detail.TableDataTable)grid1.DataSet.Tables["Table"]);
dep_pos_imbalance_detail.TableRow dr = dt.NewTableRow();
foreach (var item in query_final)
{
   dr = dt.NewTableRow();
   dr.name = item.name;
   if (item.deal_date.HasValue)
       dr.deal_date = item.deal_date.Value;
   else
       dr.Setdeal_dateNull();
   dt.AddTableRow(dr);
}