0

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);
}

julien
  • 3
  • 4
  • 1
    If this is a [strongly typed dataset](http://msdn.microsoft.com/en-us/library/esbykkzb%28v=vs.100%29.aspx), it autogenerates properties for nullable columns like `Isdeal_dateNull` which you should use instead. – Tim Schmelter Mar 06 '12 at 15:33
  • Is this your running code? There is no comma after the first `name = a.bo_source_name` – Gert Arnold Mar 06 '12 at 15:45
  • Indeed, this is my running code where I removed a lot of columns for a better visibility. I corrected it, thanks. – julien Mar 06 '12 at 16:08
  • What is this AddTableRow(string, System.DateTime) method you are calling? If the second parameter is expecting a DateTime then there is no way you can pass in either null or DBNull.Value. – sgmoore Mar 06 '12 at 18:58
  • It is an auto-generated method by the dataset designer of Visual Studio. Indeed it takes a "DateTime" and not a "DateTime?". But that's the thing, I don't know how to set via the designer, in the columns of the datatables, a type to "DateTime?". When we look at the image I've attached, we see the whole definition of my column. – julien Mar 07 '12 at 09:27
  • This looks to be more a typed Dataset issue rather than a Linq issue. Dataset has no problems with nulls, but the generated code looks horrible. (Probably written before System.DateTime? existed and has not been changed since). So it looks like you are going to have to dump AddTableRow and implement your own which accepts System.DateTime? and if this is null, you use DBNull.Value otherwise you use the actual date. – sgmoore Mar 07 '12 at 10:27
  • In the select block replace `deal_date = b.deal_date` with `deal_date = (b.Isdeal_dateNull) ? new DateTime?() : new DateTime?(b.deal_date)` to get the result `deal_date` as a nullable DateTime. – Luke Forder Mar 07 '12 at 20:04

2 Answers2

1

If this is a strongly typed DataSet, it autogenerates properties for nullable columns like Isdeal_dateNull which you should use instead.

if (!row.Isdeal_dateNull)
{
   //do something
}
Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Hello Tim, indeed this method exists but I don't see how I can insert it in my LINQ query. And I also don't want to do something in particular when I have null deal_date, I then want to insert null in my column. Any idea? Thanks – julien Mar 06 '12 at 16:15
  • Truying "deal_date = b.Isdeal_dateNull() ? default(DateTime?) : b.deal_date" gives: "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'" This image shows the definition of my column deal_date in my dataset: http://www.developpez.net/forums/attachments/p91000d1331057135/dotnet/langages/csharp/probleme-convertion-datetime-valant-null-requete-linq/deal_date.jpg – julien Mar 06 '12 at 18:38
  • When I try to modify the NullValu 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." – julien Mar 06 '12 at 18:57
  • We can see in the image that I've put in the previous comment 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.". – julien Mar 06 '12 at 19:05
0

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);
}
julien
  • 3
  • 4