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?

Answer 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)