fix: Sorting a column that contains a null value throws exception in FluentDataGrid

[thebarrettlo]

🐛 Bug Report

I've created a data grid with sortable columns from a System.Data.DataTable object as a source. The columns are of varying types--string, bool, Guid, etc. When sorting a column that contains a System.DBNull value, an ArgumentException is thrown and the web app crashes. This bug was identified when sorting a column that contains Guid types but has one or more empty cells. Columns of string types with empty cells do not have the same issue.

💻 Repro or Code Sample

In the below sample, Table is a component property of System.Data.DataTable? type. FilteredData is a component property of System.Linq.IQueryable<DataRow>? type.

<FluentDataGrid Items="@FilteredData" Pagination="@pagination" ResizableColumns="true" Style="overflow-x: auto;">
    <ChildContent>
        @foreach (DataColumn col in Table?.Columns)
        {
            <PropertyColumn Property="@(res => res[col])" Title="@col.ColumnName" Sortable="true" />
        }
    </ChildContent>
    <EmptyContent>
        @if (FilteredData == null)
        {
            <div>
                <p>@PromptMessage</p>
            </div>
        }
        else if (FilteredData.Count() == 0)
        {
            <div>
                <p>@EmptyMessage</p>
            </div>
        }
    </EmptyContent>
</FluentDataGrid>

The table rows are loaded into an IQueryable<DataRow> object through the protected synchronous OnParametersSet() method:

/// <inheritdoc/>
protected override void OnParametersSet()
{
    if (Table != null)
    {
        _data = Queryable.AsQueryable(Table.AsEnumerable());
    }
}

🤔 Expected Behavior

When sorting a column with DBNull values, cells with a value of DBNull should be treated the same as empty/null cells and not throw an exception.

😯 Current Behavior

Below is the stack trace when a column with a null (DBNull) value is sorted:

[2024-01-16T19:55:37.001Z] Error: System.ArgumentException: Object must be of type String.
   at System.String.CompareTo(Object value)
   at System.Collections.Comparer.Compare(Object a, Object b)
   at System.Linq.EnumerableSorter`2.CompareAnyKeys(Int32 index1, Int32 index2)
   at System.Linq.EnumerableSorter`2.PartialQuickSort(Int32[] map, Int32 left, Int32 right, Int32 minIdx, Int32 maxIdx)
   at System.Linq.EnumerableSorter`1.Sort(TElement[] elements, Int32 count, Int32 minIdx, Int32 maxIdx)
   at System.Linq.OrderedEnumerable`1.GetEnumerator(Int32 minIdx, Int32 maxIdx)+MoveNext()
   at Microsoft.FluentUI.AspNetCore.Components.FluentDataGrid`1.ResolveItemsRequestAsync(GridItemsProviderRequest`1 request) in /_/src/Core/Components/DataGrid/FluentDataGrid.razor.cs:line 420
   at Microsoft.FluentUI.AspNetCore.Components.FluentDataGrid`1.RefreshDataCoreAsync() in /_/src/Core/Components/DataGrid/FluentDataGrid.razor.cs:line 341
   at Microsoft.FluentUI.AspNetCore.Components.FluentDataGrid`1.RefreshDataAsync() in /_/src/Core/Components/DataGrid/FluentDataGrid.razor.cs:line 314
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

💁 Possible Solution

Haven't looked into the code for the FluentDataGrid component yet, but willing to contribute a fix. May just require converting certain types to null or string.Empty before processing/comparison, or if there's a location in the code higher up to avoid performing this conversion on every sort.

🔦 Context

I created a custom component that creates a FluentDataGrid out of an inputted System.Data.DataTable. This table is obtained through a Kusto query using the .NET Kusto SDKs, and the data often includes columns that are Guid type.

🌍 Your Environment

• OS & Device: Windows 11 on PC
• Browser: Microsoft Edge
• .NET 8.0
• Fluent UI 4.3.1

[vnbaaij]

Would be great if you can contribute a fix!

[thebarrettlo]

Upon further investigation, this seems to be an issue with DBNull itself not having built-in conversions to other types. When a column in the FluentDataGrid in sorted, Linq's OrderBy()/OrderByDescending() methods are called. Linq uses System.Collections.Comparer.Compare(Object a, Object b) (as seen in the stack trace above) to compare objects; for System.Data.DataTable's with columns of type string, a cell with a valid string value is compared to DBNull, which cannot be cast to string.

Local patch

Currently, I have a patch in my own team's component to check each DataTable cell, and if the cell's value is DBNull, convert it to an appropriate value for that column. Example:

foreach (DataColumn col in data.Columns)
{
    foreach (DataRow row in data.Rows)
    {
        if (row[col] is DBNull dbNull)
        {
            switch (col.DataType)
            {
                case Type t when t == typeof(string):
                    row[col] = string.Empty;
                    break;
                case Type t when t == typeof(int):
                    row[col] = 0;
                    break;
                case Type t when t == typeof(double):
                    row[col] = 0.0;
                    break;
                case Type t when t == typeof(decimal):
                    row[col] = 0.0m;
                    break;
                case Type t when t == typeof(DateTime):
                    row[col] = default(DateTime);
                    break;
                case Type t when t == typeof(TimeSpan):
                    row[col] = default(TimeSpan);
                    break;
                default:
                    row[col] = null;
                    break;
            }
        }
    }
}

Next steps

The only change to make to the data grid/related code that I see at the moment is to add an explicit check and conversion for DBNull. However, I understand that this issue is somewhat niche use case with DataTables in combination with the FluentDataGrid component, and a special check for DBNull within the component code may not be desired. (Though DBNull does have a value reserved in the System.TypeCode enum.)

Should this type of fix be added to the components or left to consumers of this library to implement their own fix as needed?

Similar issues across the internet over years past

• https://stackoverflow.com/questions/17825858/sort-the-value-with-dbnull-using-queryable-orderby-it-throws-exception
• https://stackoverflow.com/questions/23283360/conversion-from-type-dbnull-to-type-string-is-not-valid