[Solved]-CopyToDataTable: Why exactly does it throw an error if one field is NULL, and how to fix it?-LINQ,C#

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