filtering out null values while converting datatable to dictionary using linq

Question

I am converting a datatable to dictionary using below code:

Dictionary<string, decimal> dict = dt.AsEnumerable()
    .Select(dr => new { Key = dr["key_col"], Value = dr["value_col"] })
    .ToDictionary(kvp => (string)kvp.Key, kvp => (decimal)kvp.Value);

but it throws me an 'input string not in correct format' exception. I am pretty sure its because of some null rows present in the datatable. how can I filter out those null rows from this code?

The exception cannot be thrown from the code you've shown. Where do you parse the key? — Tim Schmelter
– Tim Schmelter, Commented Jul 18, 2016 at 7:31
ok, that exception is not coming anymore. but my new exception is 'specified cast is not valid'. I am actually dumping an excel into datatable where one column is string and another column is decimal. when I am dumping excel into datatable there is no issue. but the moment I try to convert it to a dictionary it gives 'specified cast not valid'. — Arpit Shukla
– Arpit Shukla, Commented Jul 18, 2016 at 9:17
are you sure that the datatable contains decimal columns and not only string columns? Look at dt.Columns. If it only contains strings you can't cast it (or use my Field<decimal> approach). You have to parse it, f.e. decimal.Parse(dr.Field<string>("value_col")) — Tim Schmelter
– Tim Schmelter, Commented Jul 18, 2016 at 9:23
you are right @TimSchmelter, the column was in decimal format. however, in the lambda expression when I used decimal.Parse() it gave me an error. so I had to use Convert.ToDecimal(str) in the end. it worked. — Arpit Shukla
– Arpit Shukla, Commented Jul 18, 2016 at 9:29

smoksnes · Accepted Answer · 2016-07-18 07:33:19Z

2

I'm not sure about the exception, but if you want to remove all null-values you can add a Where-clause.

Dictionary<string, decimal> dict = dt.AsEnumerable()
    .Where(dr => dr["key_col"] != null && !string.IsNullOrEmpty(dr["key_col"].ToString()))
    .Select(dr => new { Key = dr["key_col"], Value = dr["value_col"] })
    .ToDictionary(kvp => (string)kvp.Key, kvp => (decimal)kvp.Value);

You may also want to try !string.IsNullOrEmpty(dr["key_col"] as string) in the Where-clause. But I've have experienced some problems with it when casting column values. Therefore I tend to use dr["key_col"] != null && !string.IsNullOrEmpty(dr["key_col"].ToString()) instead.

edited Jul 18, 2016 at 7:33

answered Jul 18, 2016 at 7:27

smoksnes

10.9k5 gold badges60 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arpit Shukla Over a year ago

i have edited the question and put the exact exception message in the above question. You can check if you want to get some more idea about it. meanwhile, I am trying the solution provided by you.

Arpit Shukla Over a year ago

ok, that exception is not coming anymore. but my new exception is 'specified cast is not valid'. I am actually dumping an excel into datatable where one column is string and another column is decimal. when I am dumping excel into datatable there is no issue. but the moment I try to convert it to a dictionary it gives 'specified cast not valid'.

smoksnes Over a year ago

It's probably the cast to decimal. Take a look at the great answer from @Tim Schmelter. Instead of casting you can try to parse the value with double.TryParse

Arpit Shukla Over a year ago

the column was in decimal format. however, in the lambda expression when I used decimal.Parse() it gave me an error. so I had to use Convert.ToDecimal(str) in the end. it worked.

Tim Schmelter · Accepted Answer · 2016-07-18 07:37:28Z

2

The only string in the query is the key of the dictionary. The exception suggests that you're trying to convert it to a numeric type later(f.e. int.Parse).

You should do this in the query and use a Dictionary<int, decimal>. For example:

int key;
Dictionary<int, decimal> dict = dt.AsEnumerable()
    .Where(dr => int.TryParse(dr.Field<string>("key_col"), out key))
    .Select(dr => new { Key = key, Value = dr.Field<decimal>("value_col") })
    .ToDictionary(x=> x.Key, x=> x.Value);

If it's actually not an int but a different type, f.e.double use double.TryParse

answered Jul 18, 2016 at 7:37

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Collectives™ on Stack Overflow

filtering out null values while converting datatable to dictionary using linq

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related