CopyToDataTable: Why exactly does it throw an error if one field is NULL, and how to fix it?

Question

Question:

I'm using a modified version of Linq.Dynamic to sort a datatable upon an ajax request (see code below). So far it used to work fine.

However, I have a problem:

If I have a datatable, that contains a NULL value, even if in only one field in only one row is NULL, I get those two exceptions:

Object must be of type "string"

and if several adjacent values are NULL:

At least one object must implement IComparable

This is my code

using System.Linq;
using System.Data;
using System.Linq.Dynamic;


   // Pre:
   // sidx: Sort Field
   // sord: Sort order ["ASC", "DESC"]
   // page: current page number
   // rows: pagesize in rows
   public static string TestPaging(string sidx, string sord, string page, string rows)
   {
        string strReturnValue = null;

        using (System.Data.DataTable dtAllData = GetDataTable())
        {

            IQueryable<System.Data.DataRow> iqOrderedPagedData = null;


            if (string.IsNullOrEmpty(sidx) || string.IsNullOrEmpty(sord))
                iqOrderedPagedData = dtAllData.AsEnumerable().AsQueryable();
            else
                iqOrderedPagedData = dtAllData.AsEnumerable().AsQueryable().OrderBy(sidx + " " + sord);


            Int32 iPageSize = string.IsNullOrEmpty(rows) ? 100 : System.Convert.ToInt32(rows);
            Int32 iPageIndex = System.Convert.ToInt32(page) - 1;

            iqOrderedPagedData = iqOrderedPagedData.Skip(iPageIndex * iPageSize).Take(iPageSize);


            //using (System.Data.DataTable dtOrderedPagedData = MyCopyToDataTable(iqOrderedPagedData))
            using (System.Data.DataTable dtOrderedPagedData = iqOrderedPagedData.CopyToDataTable())
            {
                cjqGrid jqGrid = new cjqGrid();
                //jqGrid.total = dtAllData.Rows.Count / iPageSize + 1;
                jqGrid.total = (int)Math.Ceiling((float)dtAllData.Rows.Count / (float)iPageSize);
                jqGrid.page = iPageIndex + 1;
                jqGrid.records = dtAllData.Rows.Count;
                jqGrid.data = dtOrderedPagedData;
                strReturnValue = null; // Serialize(jqGrid, true);
                jqGrid = null;
            } // End Using dtOrderedPagedData 

        } // End Using dtAllData

        //Response.ContentType = "application/json";
        return strReturnValue;
    }


TestPaging("USR_Domain", "desc", "1", "10");

The problem seems to be the extension method CopyToDataTable, at the line:

if (!e.MoveNext ())

This makes it sort the table, which means it calls the function Compare in class System.Linq.SortSequenceContext

where the error is thrown at this line

comparison = comparer.Compare(keys[first_index], keys[second_index]);

Here the version from mono with my fixes which make that mono method actually work.
However, the bug occurs in plain old MS .NET 4.0 as well.
(I need mono to backport my Linq-using method to .NET 2.0)

public static DataTable CopyToDataTable<T> (this IEnumerable<T> source)
            where T : DataRow
        {
            DataTable dt = new DataTable ();
            IEnumerator<T> e = source.GetEnumerator ();
            if (!e.MoveNext ())
                throw new InvalidOperationException ("The source contains no DataRows");
            foreach (DataColumn col in e.Current.Table.Columns)
                dt.Columns.Add (new DataColumn (col.ColumnName, col.DataType, col.Expression, col.ColumnMapping));
            CopyToDataTable<T> (source, dt, LoadOption.PreserveChanges);
            return dt;
        }

        public static void CopyToDataTable<T> (this IEnumerable<T> source, DataTable table, LoadOption options)
            where T : DataRow
        {

            if (object.ReferenceEquals(typeof(T), typeof(System.Data.DataRow)))
            {

                foreach (System.Data.DataRow drRowToCopy in source)
                {
                    System.Data.DataRow drNewRow = table.NewRow();

                    for (int i = 0; i < drRowToCopy.ItemArray.Length; ++i)
                    {
                        drNewRow[i] = drRowToCopy[i];
                    } // Next i

                    table.Rows.Add(drNewRow);
                } // Next dr

            }
            else
                CopyToDataTable<T>(source, table, options, null);
        }

To problem occurs as soon as even only one value in one column of one row is NULL...

Can anyone suggest me how I can fix this problem ?
Or how I can otherwise make a DataTable from an IEnumerable without getting exceptions when one or two fields are NULL ?

Right now I "fixed" it by catching the exception in Compare.
But that's only a fix for .NET 2.0, where I can do this because this class doesn't exist.
I need a real fix that works for .NET 4.0 as well.

public override int Compare (int first_index, int second_index)
        {
            int comparison = 0;
            try
            {
                comparison = comparer.Compare(keys[first_index], keys[second_index]);
            }
            catch (Exception ex)
            {
                //Console.WriteLine(ex.Message);
            }

            if (comparison == 0) {
                if (child_context != null)
                    return child_context.Compare (first_index, second_index);

                comparison = direction == SortDirection.Descending
                    ? second_index - first_index
                    : first_index - second_index;
            }

            return direction == SortDirection.Descending ? -comparison : comparison;
        }

PS: For my DataTable-OrderBy-Enhanced version of Linq.Dynamic, see here:
http://pastebin.com/PuqtQhfa

I don't know whether this can be back ported to 2.0, but you might want to take a look at the second answer (by Campbeln) to this question http://stackoverflow.com/questions/1253725/convert-ienumerable-to-datatable. — sgmoore, Aug 21 '12 at 08:30
@sgmoore: Nope, DataTable operates with an array of objects, not properties. — Stefan Steiger, Aug 21 '12 at 08:33

Stefan Steiger · Accepted Answer · 2012-08-25T13:48:34.867

OK, i got the solution:

It was very obvious that it had something todo with the type System.DbNull

First, because I had only strings in my DataTable, and I got the error message:

Object must be of type "string"

But how can a string not be of type string ? Of course only if it's DbNull.

And if you got two adjacent DbNull.Values, you of course get:

At least one object must implement IComparable

Because DbNull doesn't implement IComparable.

After all, the funny thing was, that it did only fail when the sort column was a column with NULL values, but it did work perfectly if it was a column with no NULL values.

Since the table itselfs contains all the null values irrespective of the ordering, it was illogical that CopyToDataTable would sometimes not work, because it copied all values every time irrespective of the ordering.

The only logical conclusion was that OrderBy does NOT get executed when it is called in code, but only when some method was actually using the data produced by OrderBy.

A quick google search brought me to this http://blogs.msdn.com/b/charlie/archive/2007/12/09/deferred-execution.aspx

Of which only the first few lines needed to be read to know what the problem was:

This post covers one of the most important and frequently misunderstood LINQ features. Understanding deferred execution is a rite of passage that LINQ developers must undergo before they can hope to harness the full power of this technology.

So it dawned to me that I just passed the right of passage :)

Obviously the flaw is that e.MoveNext() on ordering triggers a comparison which fails on DbNull, because DbNull as said does not implement IComparable.

Ironically, a datatable can also be sorted using the select statement, which I didn't know initially (after all, I'd expect a "order" method to be called "order", and not "select"...) So I just changed the OrderBy in Linq.Dynamic to

public static IQueryable OrderBy(this IQueryable source, string ordering, params object[] values)
{
    if (source == null) throw new ArgumentNullException("source");
    if (ordering == null) throw new ArgumentNullException("ordering");

    if (object.ReferenceEquals(source.ElementType, typeof(System.Data.DataRow)))
    {
        using (DataTable dt = source.Cast<System.Data.DataRow>().CopyToDataTable())
        {
            return dt.Select("", ordering).AsQueryable();
        }

And voila, bug gone.
And since it can be filtered with Select alone more efficiently than when using Linq.Dynamic (which makes about 3 copies of all the data), I decided to abandon Linq.Dynamic completely.
I'm still using Linq take and skip, but in the future, I'll certainly be more reluctant to use Linq at all.

Deferred execution is outright dangerous, because it leads to very badly tracable bugs.
All that's needed for "boooom" is a null value at the wrong place, or a missing interface (and a missing check thereof or a missing generics restriction as in this case)...

CopyToDataTable: Why exactly does it throw an error if one field is NULL, and how to fix it?

1 Answers1