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

Question

Question:

I am using a modified version of Linq.Dynamic to sort datatable by ajax request (see code below). While it is used , it works fine.

However, I have a problem:

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

Object must be of type "string" 

and if several neighboring NULL values:

 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 is that the CopyToDataTable extension method in the line:

 if (!e.MoveNext ()) 

This allows you to sort the table, which means that it calls the Compare in class System.Linq.SortSequenceContext function

where the error is selected on this line

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

Here's a mono version with my corrections that make this mono method really work.

However, an error occurs in a simple old version of MS.NET 4.0.
(I need mono to backup my Linq usage method in .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); } 

The problem occurs when even one value in one column of one row is NULL ...

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

Right now I have “fixed” it by catching an exception in Compare.
But this is only a fix for .NET 2.0, where I can do this because this class does not 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 version of DataTable-OrderBy-Enhanced Linq.Dynamic see here:
http://pastebin.com/PuqtQhfa

Answer 1

OK, I got a solution:

It was very obvious that he had something like System.DbNull

Firstly, because in my DataTable I only had rows, and I received an error message:

 Object must be of type "string" 

But how can a string not contain a string of type? Of course, only if it is DbNull.

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

 At least one object must implement IComparable 

Since DbNull does not implement IComparable.

In the end, the funny thing is that this only happened when the sort column was a column with NULL values, but it worked fine if it was a column with no NULL values.

Since the table itself contains all null values, regardless of order, it would be illogical that CopyToDataTable sometimes does not work, because it copied all the values ​​every time, regardless of order.

The only logical conclusion was that OrderBy does NOT start when it is called in code, but only when some method actually used the data created by OrderBy.

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

Of these, only the first few lines had to be read to find out what the problem was:

This post covers one of the most important and often misunderstood LINQ features. Understanding the delayed execution ritual of passage that LINQ developers must go through before they can hope to use the full power of this technology.

So, it became clear to me that I just passed the right of passage :)

Obviously, the disadvantage is that e.MoveNext() compares which is not executed on DbNull when ordering triggers, because DbNull, as said, does not implement IComparable.

Ironically, a datatable can also be sorted using a select statement that I did not know initially (in the end, I would expect the "order" method to be called "order" and not "select" ..) So I just changed 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, the error has disappeared.
And since it can only be filtered using Select alone more efficiently than using Linq.Dynamic (which is about 3 copies of all data), I decided to completely abandon Linq.Dynamic.
I am still using Linq and will skip, but in the future I will, of course, no longer use Linq at all.

Delayed execution is directly dangerous because it leads to very terrible errors.
All that boooom needs is a null value in the wrong place or a missing interface (and a missing check or no constraint for generics in this case) ...