Object cannot be cast from DBNull to other types in linq expression

Question

var gFieldList = new List<string>() { "Supplier", "Country" };
    var sFieldList = new List<string>() { "Sales"};

     var gField = string.Join(", ", gFieldList.Select(x => "it[\"" + x + "\"] as " + x));
     var sField = string.Join(", ", sFieldList.Select(y => "Sum(Convert.ToDouble(it[\""+y+"\"])) as "+y));


         var newSort = dataTable
                        .AsEnumerable()
                        .AsQueryable()
                        .GroupBy("new("+gField+")", "it")
                        .Select("new("+sField+",it.Key as Key, it as Data)");

var groupedData = (from dynamic dat in newSort select dat).ToList();

throwing exception

Object cannot be cast from DBNull to other types.

in the last line,

var groupedData = (from dynamic dat in newSort select dat).ToList();

How to solve this issue?

newSort is var type, where in I'm getting value from a Dynamic Linq Expression. — Varun Chaturvedi, Oct 23 '13 at 05:05
The values in the dataTable are coming from the Excel files, so the values can be blank there. — Varun Chaturvedi, Oct 23 '13 at 05:38

Grundy · Accepted Answer · 2013-10-29T13:47:34.983

1

EDIT2: try change your Convert like this

sFieldList.Select(y => "Sum(Convert.ToDouble(iif(it[\""+y+"\"] == @0,0,it[\""+y+"\"]))) as "+y)

and then

var newSort = dataTable
                .AsEnumerable()
                .AsQueryable()
                .GroupBy("new("+gField+")", "it")
                .Select("new("+sField+",it.Key as Key, it as Data)",DBNull.Value);

UPDATE2: for round Sum result try this:

sFieldList.Select(y => "Sum(Math.Round(Convert.ToDouble(iif(it[\""+y+"\"] == @0,0,it[\""+y+"\"])),2)) as "+y)

edited Oct 29 '13 at 13:47

answered Oct 23 '13 at 05:26

Grundy

13,356
3
35
55

none of them seems to be working, both of them throwing the same exception..! – Varun Chaturvedi Oct 23 '13 at 05:36
@VarunChaturvedi you can specify which namespace are used that extension GroupBy and Select? Is used 3rd party library? – Grundy Oct 23 '13 at 14:45
I used System.Linq.Dynamic; – Varun Chaturvedi Oct 24 '13 at 07:48
Bingo.! it works..!! thanks a lot Grundy! can you help me with one more question I posted a day before, http://stackoverflow.com/questions/19513663/how-can-i-access-the-result-of-dynamic-linq-with-dynamic-key-value-pairs-of-ienu – Varun Chaturvedi Oct 24 '13 at 10:21
any idea how can I round of the result of Sum above to 2 decimal digits? – Varun Chaturvedi Oct 29 '13 at 12:30
@VarunChaturvedi use [Math.Round](http://msdn.microsoft.com/ru-ru/library/wyk4d9cy(v=vs.110).aspx) – Grundy Oct 29 '13 at 13:13
am not able to embedd with this dynamic query, should I use Math.Round in the dounble quotes itself like Sum()? – Varun Chaturvedi Oct 29 '13 at 13:14
exactly, I used it this way only, but it says **Argument types do not match** – Varun Chaturvedi Oct 29 '13 at 13:28
@VarunChaturvedi you right, it because Sum function in dynamic linq don't retun value `void Sum(decimal selector);` – Grundy Oct 29 '13 at 13:48
see my answer in http://stackoverflow.com/questions/19658343/how-to-set-round-of-the-result-of-aggregate-function-used-in-dynamic-linq-query/19674780 – Grundy Oct 30 '13 at 05:49

score 0 · Answer 2 · answered Oct 23 '13 at 07:44

0

Try this :

var gFieldList = new List<string ?>() { "Supplier", "Country" };
var sFieldList = new List<string ?>() { "Sales"};

answered Oct 23 '13 at 07:44

kaushik0033

679
6
12

The type 'string' must be a non-nullable value type in order to use it as parameter 'T' in the generic type or method 'System.Nullable' – Varun Chaturvedi Oct 23 '13 at 10:39
you should be put Where condition to check Null. – kaushik0033 Oct 23 '13 at 11:37

Object cannot be cast from DBNull to other types in linq expression

2 Answers2