title: CopyToDataTable: Why exactly does it throw an error if one field is NULL, and how to fix it?
body: <p>Question:</p>
  
  <p>I'm using a modified version of Linq.Dynamic to sort a datatable upon an ajax request (see code below).
  So far it <strong>used to</strong> work fine.</p>
  
  <p>However, I have a problem:</p>
  
  <p>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: </p>
  
  <pre><code>Object must be of type "string"
  </code></pre>
  
  <p>and if several adjacent values are NULL:</p>
  
  <pre><code>At least one object must implement IComparable
  </code></pre>
  
  <p>This is my code</p>
  
  <pre><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&lt;System.Data.DataRow&gt; 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");
  </code></pre>
  
  <p>The problem seems to be the extension method <a href="http://vega.frugalware.org/tmpgit/mono/mcs/class/System.Data.DataSetExtensions/System.Data/DataTableExtensions.cs" rel="nofollow">CopyToDataTable</a>, at the line:</p>
  
  <pre><code>if (!e.MoveNext ())
  </code></pre>
  
  <p>This makes it sort the table, which means it calls the function Compare in class 
  <a href="http://source.db4o.com/db4o/trunk/db4o.net/Libs/compact-3.5/System.Linq.Expressions/System.Linq/SortSequenceContext.cs" rel="nofollow">System.Linq.SortSequenceContext </a></p>
  
  <p>where the error is thrown at this line</p>
  
  <pre><code>comparison = comparer.Compare(keys[first_index], keys[second_index]);
  </code></pre>
  
  <p>Here the version from mono with my fixes which make that mono method actually work. <br />
  However, the bug occurs in plain old MS .NET 4.0 as well.<br />
  (I need mono to backport my Linq-using method to .NET 2.0)<br /></p>
  
  <pre><code>public static DataTable CopyToDataTable&lt;T&gt; (this IEnumerable&lt;T&gt; source)
              where T : DataRow
          {
              DataTable dt = new DataTable ();
              IEnumerator&lt;T&gt; 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&lt;T&gt; (source, dt, LoadOption.PreserveChanges);
              return dt;
          }
  
          public static void CopyToDataTable&lt;T&gt; (this IEnumerable&lt;T&gt; 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 &lt; drRowToCopy.ItemArray.Length; ++i)
                      {
                          drNewRow[i] = drRowToCopy[i];
                      } // Next i
  
                      table.Rows.Add(drNewRow);
                  } // Next dr
  
              }
              else
                  CopyToDataTable&lt;T&gt;(source, table, options, null);
          }
  </code></pre>
  
  <p>To problem occurs as soon as even only one value in one column of one row is NULL...</p>
  
  <p>Can anyone suggest me how I can fix this problem ?<br />
  Or how I can otherwise make a DataTable from an IEnumerable without getting exceptions when one or two fields are NULL ?<br /></p>
  
  <p>Right now I "fixed" it by catching the exception in Compare.<br />
  But that's only a fix for .NET 2.0, where I can do this because this class doesn't exist. <br />
  I need a real fix that works for .NET 4.0 as well.<br /></p>
  
  <pre><code>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;
          }
  </code></pre>
  
  <p>PS: For my DataTable-OrderBy-Enhanced version of Linq.Dynamic, see here:<br />
  <a href="http://pastebin.com/PuqtQhfa" rel="nofollow">http://pastebin.com/PuqtQhfa</a></p>