DataFilter component with DynamicData/LoadData DataGrid

[Nick_Thijssen]

I have a datagrid with DynamicData set up as in the example on Blazor DataGrid supports dynamic data sources. However i've also implemented LoadData so it only loads the page an no more since loading the full table is very slow. I want to use a DataFilter since I find the inline filters on the Datagrid quite ugly.

You can't use the dynamic Linq coming from the DataFilter since that expects Dictionary<string, object?> and what I'm getting from the context is my own Entity-Attribute-Value setup. I can't convert it right away since entity framework doesn't accept certain methods I need to convert the objects into a row and thus I have to pull the entire table into memory, which again is slow. Also you cant apply paging before filtering the data.

<RadzenStack Orientation="Orientation.Horizontal" Gap="0.5rem" AlignItems="AlignItems.Center" Class="rz-p-4 rz-border-radius-1" Style="border: var(--rz-grid-cell-border);">
    <RadzenCheckBox @bind-Value="@_auto" Name="auto" />
    <RadzenLabel Text="Auto filter" Component="auto" Class="rz-me-6" />
    <RadzenButton Text="Apply Filter" Click="@(args => _dataFilter!.Filter())" Disabled="@_auto" />
</RadzenStack>
<RadzenDataFilter @ref="_dataFilter" Auto="true" TItem="Dictionary<string, object?>" ViewChanged=@(view => _crudGrid!.Reload())>
    <Properties>
        @foreach (var column in _columns!.Where(x => x.Value != null))
        {
            <RadzenDataFilterProperty TItem="Dictionary<string, object?>" Property="@GetColumnPropertyExpression(column.Key, column.Value)" Title="@_fieldLookup![column.Key].Description" Type="@column.Value" />
        }
    </Properties>
</RadzenDataFilter>

I've come up with 3 possible avenues to proceed.

• Apply the .Filters of the DataFilter to the processed data in the DataGrid but i don't see any facility for that
• Write my own implementation of filtering the data
• Use the filter buttons on the DataGrid anyway

I've started on point 2 but it is very hard so I wanted to know if I'm going in the right direction or if there is another angle i might use

private void LoadPage(LoadDataArgs args)
{
	_refreshingData = true;
	PortalContext context = _contextFactory.CreateDbContext();

	IQueryable<Item> query = context
		.Items
		.AsNoTracking()
		.Include(x => x.ItemFields)
		.ThenInclude(x => x.ArtifactField)
		.Where(x => x.Artifact == _artifact);
	query = query.Where(x => x.IsDeleted == _showDeleted);
	IOrderedQueryable<Item>? orderedQuery = null;
	var gridSort = _crudGrid!.ColumnsCollection.Select(x => new GridSort(x.Property, x.OrderIndex, x.SortOrder)).Where(x => x.SortOrder != null).OrderBy(x => x.SortIndex).ToList();

	if (gridSort.Any())
	{
		foreach (var sorting in gridSort)
		{
			if (sorting == gridSort[0])
			{
				if (sorting.SortOrder == SortOrder.Ascending)
				{
					orderedQuery = query.OrderBy(i =>
						i.ItemFields
						.Where(f => f.ArtifactFieldId.ToString() == sorting.ColumnName)
						.Select(f => new object[] { f.ValueBit, f.ValueDateTime, f.ValueDecimal, f.ValueInt, f.ValueString, f.ValueStringMax }
						.FirstOrDefault(v => v != null))
						.FirstOrDefault());
				}
				else
				{
					orderedQuery = query.OrderByDescending(i =>
						i.ItemFields
						.Where(f => f.ArtifactFieldId.ToString() == sorting.ColumnName)
						.Select(f => new object[] { f.ValueBit, f.ValueDateTime, f.ValueDecimal, f.ValueInt, f.ValueString, f.ValueStringMax }
						.FirstOrDefault(v => v != null))
						.FirstOrDefault());
				}
			}
			else
			{
				if (sorting.SortOrder == SortOrder.Ascending)
				{
					orderedQuery = orderedQuery.ThenBy(i =>
						i.ItemFields
						.Where(f => f.ArtifactFieldId.ToString() == sorting.ColumnName)
						.Select(f => new object[] { f.ValueBit, f.ValueDateTime, f.ValueDecimal, f.ValueInt, f.ValueString, f.ValueStringMax }
						.FirstOrDefault(v => v != null))
						.FirstOrDefault());
				}
				else
				{
					orderedQuery = orderedQuery.ThenByDescending(i =>
						i.ItemFields
						.Where(f => f.ArtifactFieldId.ToString() == sorting.ColumnName)
						.Select(f => new object[] { f.ValueBit, f.ValueDateTime, f.ValueDecimal, f.ValueInt, f.ValueString, f.ValueStringMax }
						.FirstOrDefault(v => v != null))
						.FirstOrDefault());
				}
			}

		}
		query = orderedQuery;
	}
	if (_dataFilter is not null && _dataFilter!.Filters.Any())
	{
		// ?
	}
	query = query.Skip(args.Skip.Value).Take(args.Top.Value);
	_items = query!.ToList();
	_count = _items.Count;
	_data = query!.Select(x => CreateRowFromItem(x, _columns, _parentsLookup, _artifactRelations, _fieldLookup)).ToList();
	_refreshingData = false;
}

[Nick_Thijssen]

I eventually solved this by moving my EAV (Entity-Attribute-Value) object into the row Dictionary.
The row dict now contains instances of this class:

public partial class ItemField
{
    public int ItemFieldId { get; set; }
    public int ItemId { get; set; }
    public int ArtifactFieldId { get; set; }
    public bool? ValueBit { get; set; }
    public DateTime? ValueDateTime { get; set; }
    public decimal? ValueDecimal { get; set; }
    public int? ValueInt { get; set; }
    public string ValueString { get; set; }
    public string ValueStringMax { get; set; }
    public ArtifactField ArtifactField { get; set; }
}

I have a method that checks which of the value columns contains a value and prints that.

<RadzenDataGridColumn TItem="Dictionary<string, ItemField>" Title="@_artifactFieldLookup![column.Key].Description" Type="@column.Value.GetTypeFromDataTypeId()" Property="@GetColumnPropertyExpression(column.Key, column.Value)">
	<Template Context="data">
		@if (data.ContainsKey(column.Key) && data[column.Key] is not null)
		{
			@data[column.Key].PrintValue();
		}
	</Template>
</RadzenDataGridColumn>

The DataFilter is configured similarly:

<RadzenDataFilter @ref="_dataFilter" Auto="_auto" TItem="Dictionary<string, ItemField>" ViewChanged=@(view => _crudGrid!.Reload())>
	<Properties>
		@foreach (var column in _columns!.Where(x => x.Value != null))
		{
			<RadzenDataFilterProperty TItem="Dictionary<string, ItemField?>" Property="@GetColumnPropertyExpression(column.Key, column.Value)" Title="@_artifactFieldLookup![column.Key].Description" Type="@column.Value.GetTypeFromDataTypeId()" />
		}
	</Properties>
</RadzenDataFilter>

i have to implement my own sorting and filtering and that works thusly:

private void LoadPage(LoadDataArgs args)
{
	_refreshingData = true;
	PortalContext context = _contextFactory.CreateDbContext();

	IQueryable<Item> query = context
		.Items
		.AsNoTracking()
		.Include(x => x.ItemFields)
		.ThenInclude(x => x.ArtifactField)
		.Where(x => x.ArtifactId == _artifact.ArtifactId);
	query = query.Where(x => x.IsDeleted == _showDeleted);

	var sortList = args.Sorts.Where(x => x.SortOrder != null).ToList();


	if (_dataFilter is not null && _dataFilter!.Filters.Any())
	{
		query = ApplyFilters(query, _dataFilter!.Filters);
	}

	// Apply ordering based on the Value* fields
	for (int i = 0; i < sortList.Count; i++)
	{
		var sortDescriptor = sortList[i];
		var sortString = sortDescriptor.Property;
		var sortDirection = sortDescriptor.SortOrder;
		// Define a regular expression to match the new format
		// It will match either of the three possibilities
		Regex regex = new(@"it\[""(?<id>\d+)""\]\.(?<field>Value\w+)");

		Match match = regex.Match(sortString);

		if (match.Success)
		{
			string artifactFieldId = match.Groups["id"].Value;
			string valueField = match.Groups["field"].Value;

			string ordering = $"ItemFields.FirstOrDefault(ifield => ifield.ArtifactFieldId == \"{artifactFieldId}\").{valueField}";

			if (sortDirection == SortOrder.Descending)
			{
				ordering += " descending";
			}

			if (i == 0)
			{
				query = query.OrderBy(ordering);
			}
			else
			{
				query = ((IOrderedQueryable<Item>)query).ThenBy(ordering);
			}
		}
	}

	_count = query.Count();  // Get the total count before applying pagination
	query = query.Skip(args.Skip.Value).Take(args.Top.Value);
	_items = query.ToList();
	_data = _items!.Select(x => CreateRowFromItem(x, _columns, _parentsLookup, _artifactRelations, _artifactFieldLookup)).ToList();
	_refreshingData = false;
}

private static string GetOperator(FilterOperator filterOperator, string parameter)
{
	return filterOperator switch
	{
		FilterOperator.Equals => $"== {parameter}",
		FilterOperator.NotEquals => $"!= {parameter}",
		FilterOperator.LessThan => $"< {parameter}",
		FilterOperator.LessThanOrEquals => $"<= {parameter}",
		FilterOperator.GreaterThan => $"> {parameter}",
		FilterOperator.GreaterThanOrEquals => $">= {parameter}",
		FilterOperator.Contains => $".Contains({parameter})",
		FilterOperator.StartsWith => $".StartsWith({parameter})",
		FilterOperator.EndsWith => $".EndsWith({parameter})",
		FilterOperator.DoesNotContain => $"!.Contains({parameter})",
		FilterOperator.In => $"in ({parameter})",
		FilterOperator.NotIn => $"not in ({parameter})",
		FilterOperator.IsNull => "== null",
		FilterOperator.IsEmpty => "== \"\"",
		FilterOperator.IsNotNull => "!= null",
		FilterOperator.IsNotEmpty => "!= \"\"",
		_ => throw new NotSupportedException($"Filter operator '{filterOperator}' is not supported"),
	};
}

public IQueryable<Item> ApplyFilters(IQueryable<Item> query, IEnumerable<CompositeFilterDescriptor> filterDescriptors)
{
	// TODO: account for possible combinations of filter/filterValue
	foreach (var filterDescriptor in filterDescriptors.Where(x => !string.IsNullOrWhiteSpace(x.Property)))
	{
		// Extract the artifactFieldId and the valueField from the Property
		Regex regex = new Regex(@"it\[""(?<id>\d+)""\]\.(?<field>Value\w+)");
		Match match = regex.Match(filterDescriptor.Property);

		if (!match.Success) continue;

		string artifactFieldId = match.Groups["id"].Value;
		string valueField = match.Groups["field"].Value;

		// Build the filter condition based on the valueField and FilterOperator
		string predicate = $"ItemFields.Any(ArtifactFieldId.ToString() == @0 && {valueField} {GetOperator(filterDescriptor.FilterOperator, "@1")})";
		query = query.Where(predicate, artifactFieldId, filterDescriptor.FilterValue);
	}

	return query;
}

private static Dictionary<string, ItemField> CreateRowFromItem(Item newItem, List<KeyValuePair<string, string?>> columns)
{
	Dictionary<string, ItemField> row = new();
	foreach (KeyValuePair<string, string?> column in columns!)
	{
		if (column.Key == "Id")
		{
			row.Add(column.Key, new ItemField() { ValueInt = newItem.ItemId, ArtifactField = new ArtifactField() { DataTypeId = "Int" } });
			continue;
		}
		ItemField itemField = newItem.ItemFields.SingleOrDefault(x => x.ArtifactField.ArtifactFieldId.ToString() == column.Key);
		row.Add(column.Key, itemField);
	}

	return row;
}

As you can see I still have to fix it so every permutation of the filters works.

I hope this is of use to someone who is working with the DataGrid.