Sorting/Filtering dynamic RadzenDataGrid data with null cell values

Radzen.Blazor Components
1

Hello,
I'm new here (and new to Blazor), so I apologize if I'm missing something obvious here. I am retrieving data dynamically from a database so that I can display it without having to know the structure ahead of time. My data is returned in the form of a list of dictionaries. I used this as a template:

My component loads correctly and retrieves and displays the data correctly. However, the problem occurs as soon as I try to sort or filter my data, because some rows contain null values for certain columns. Columns with no null values sort and filter correctly.

Here is my template:

<RadzenDataGrid @bind-Value=@selectedItems Data="@data" TItem="IDictionary<string, object>" AllowFiltering="true" FilterPopupRenderMode="PopupRenderMode.OnDemand" AllowColumnResize="true" FilterMode="FilterMode.SimpleWithMenu" AllowSorting="true" PageSize="@pageSize" AllowPaging="true" PageSizeOptions="new int[] {5, 10, 20, 50}" PagerHorizontalAlign="HorizontalAlign.Left" ShowPagingSummary="true" ColumnWidth="200px">
    <Columns>
    @foreach (var column in columns)
      {
          <RadzenDataGridColumn TItem="IDictionary<string, object>" Title="@column" Type="@columnTypes[columns.IndexOf(column)]" Property="@GetColumnPropertyExpression(column, columnTypes[columns.IndexOf(column)]">
              <Template>
                  @context[column]
              </Template>
          </RadzenDataGridColumn>
      }
    </Columns>
</RadzenDataGrid>

This code gives me the error: "Input string was not in a correct format"

public string GetColumnPropertyExpression(string name, Type type)
    {
        var expression = $@"it[""{name}""].ToString()";

        if (type == typeof(int))
        {
            return $"int.Parse({expression})";
        }
        else if (type == typeof(DateTime))
        {
            return $"DateTime.Parse({expression})";
        }

        return expression;
    }

And if I adjust it to this, I get the error "Object must be of type Int32" or "Object must be of type String" etc., depending on the column's data type.

public string GetColumnPropertyExpression(string name, Type type)
    {
        var expression = $@"it[""{name}""]";

        return expression;
    }

Finally, if I adjust the code as below, it works in all cases, but obviously the sorting is incorrect because it sorts everything "alphabetically," which is especially problematic for things like IDs because it wants to order them 1, 10, 1002, 109, 11, 12 instead of 1, 2, 3...

public string GetColumnPropertyExpression(string name, Type type)
    {
        var expression = $@"it[""{name}""].ToString()";

        return expression;
    }

I'm guessing I'm doing something wrong or couldn't find the right resource because sorting and filtering where some values might be null seems like it should definitely be an out of the box feature. Thank you in advance for your help!

2

If you have null you need to use nullable types, for example int? not plain int.

3

Thank you for the quick reply! If I adjust the method as below, my columns all sort and filter correctly (even null values), but the order is wrong.

public string GetColumnPropertyExpression(string name, Type type)
    {
        var expression = $@"it[""{name}""].ToString()";

        if (type == typeof(int?))
        {
            return $"int.Parse({expression})";
        }
        else if (type == typeof(DateTime?))
        {
            return $"DateTime.Parse({expression})";
        }

        return expression;
    }

For example, the IDs show up in the order 1, 10, 100, 101, 102 because it's treating them like 'string' instead of 'int'. Is there another place where I should have been making my values nullable? I tried manually changing all of my DBNull values in the Dictionary<string, object> to 'null', but that resulted in problems in trying to determine the data type of each column. Thanks again for your help!

4

I came up with a working solution that sorts/filters properly and handles null values. I had to first create a IDictionary<string, Type> and extract the names and data types from the first row of data returned from the database. Next, I created a IEnumerable<IDictionary<string, object>> and manually set all objects = null if they were DBNull. Perhaps not the best solution, but now that I know how it works I can optimize it further. Thanks for the tip, I hope this thread helps someone else!

5

With nullable type handling in method GetColumnPropertyExpression

if I take example of int.parse ,here {expression} will be empty for null value and because of this empty value int.parse is failing with error input string not in correct format.

Int.parse cant be used with null or empty values. How we can handle that.

public string GetColumnPropertyExpression(string name, Type type)
{
var expression = $@"it[""{name}""].ToString()";

    if (type == typeof(int?))
    {
        return $"int.Parse({expression})";
    }
    else if (type == typeof(DateTime?))
    {
        return $"DateTime.Parse({expression})";
    }

    return expression;
}
6

Hi. With nullable type handling in method GetColumnPropertyExpression

if I take example of int.parse ,here {expression} will be empty for null value and because of this empty value int.parse is failing with error input string not in correct format.

Int.parse cant be used with null or empty values. How you are handling that.