How to create LINQ-compatible filter string on child entities?

Radzen.Blazor Components
1 
public class Order
{
    public int Id { get; set; }
    public string Name { get; set; }
    ICollection<OrderDetail> OrderDetails { get; set; }
}

public class OrderDetail
{
    public int Id { get; set; }
    public string Code { get; set; }

    public int OrderId { get; set; }
    public Order Order { get; set; }
}

Unfortunately Radzen Grid does not support filtering on child entries' properties, so I can't search for orders where order details have specific codes while in Order grid.

My idea of a workaround was to create a custom RadzenDataFilter< Order > which'd output LINQ-compatible OrderDetails filter string via .ToFilterString() that I'd feed to Order grid, however I am again not sure how to have this filter work with child collection.

Any suggestion or workaround is appreciated.

Overall adding a child collection filter support looks to be a great addition to Radzen.

2

How do you write it in plain C#/LINQ?

3 
Order[] orders1 = await dbContext.Order.Where(x => x.OrderDetails.Any(od => od.Code.StartsWith("0")))
    .ToArrayAsyncLinqToDB();

Order[] orders2 = await dbContext.Order.Where("OrderDetails.Any(Code.StartsWith(\"0\"))")
    .ToArrayAsyncLinqToDB();

Both expressions produce same SQL:

SELECT
        [x].[Id],
        [x].[Name]
FROM
        [Order] [x]
WHERE
        EXISTS(
                SELECT
                        *
                FROM
                        [OrderDetail] [od]
                WHERE
                        [x].[Id] = [od].[OrderId] AND [od].[Code] LIKE N'0%' ESCAPE N'~'
        )
4

There is a demo showing how to do that in the DataGrid FilterTemplate (Customer column):

image
image1920×1080 206 KB

5

But then again it does not showcase how to filter by child entities' properties.

CompanyName is just a regular Employee property, which is well covered by Radzen Grid basic functionality contrary to filtering by child properties.

6

Ah, sorry - I misunderstood the question. Check this column:

image
image1920×1080 220 KB

7

WorkStatuses is a property of parent as well, not child. There are no children in this demo.

And moreover the makeshift 'WorkStatuses' array can exist only in demo where the collection is initialized in code and it not comes from db. I can't imagine a real life scenario when such an IQueryable with array exists (except maybe in MongoDB)

This approach won't generate the following filter string: "OrderDetails.Any(Code.StartsWith("0"))"

8

Here is a simple example:

@using System.Linq.Dynamic.Core
@using RadzenBlazorDemos.Data
@using RadzenBlazorDemos.Models.Northwind
@using Microsoft.EntityFrameworkCore

@inherits DbContextPage

<RadzenDataGrid AllowPaging="true" AllowSorting="true" AllowFiltering="true" Data="@orders" Count="@count" LoadData="@LoadData">
    <Columns>
        <RadzenDataGridColumn Property="OrderDetails.Product.ProductName" Title="Product Name" FilterOperator="FilterOperator.Contains" Type="typeof(string)">
            <Template>
                @(string.Join(',', context.OrderDetails.Select(od => od.Product.ProductName)))
            </Template>
        </RadzenDataGridColumn>
    </Columns>
</RadzenDataGrid>

@code {
    IQueryable<Order> orders;

    int count;
    bool isLoading = false;

    async Task LoadData(LoadDataArgs args)
    {
        isLoading = true;

        await Task.Yield();

        var query = dbContext.Orders.Include("OrderDetails.Product");

        if (!string.IsNullOrEmpty(args.Filter))
        {
            if (args.Filters.Any(f => f.Property == "OrderDetails.Product.ProductName"))
            {
                foreach(var filter in args.Filters)
                {
                    if (filter.Property == "OrderDetails.Product.ProductName")
                    {
                        query = query.Where(i => i.OrderDetails.Any(od => od.Product.ProductName.Contains($"{filter.FilterValue}")));
                    }
                }
            }
            else
            {
                query = query.Where(args.Filter);
            }
        }

        if (!string.IsNullOrEmpty(args.OrderBy))
        {
            query = query.OrderBy(args.OrderBy);
        }

        count = query.Count();

        orders = query.Skip(args.Skip.Value).Take(args.Top.Value);

        isLoading = false;
    }
}

dg-collection-filter

9

I've pushed also little update to enable Contains/DoesNotContain on collection sub properties. It will be available in our release later today:

@using RadzenBlazorDemos.Data
@using RadzenBlazorDemos.Models.Northwind
@using Microsoft.EntityFrameworkCore

@inherits DbContextPage

<RadzenDataGrid AllowPaging="true" AllowSorting="true" AllowFiltering="true" Data="@orders">
    <Columns>
        <RadzenDataGridColumn Property="OrderDetails" FilterProperty="Product.ProductName" Title="Product Name"
                              FilterOperator="FilterOperator.Contains" Type="typeof(IEnumerable<OrderDetail>)">
            <Template>
                @(string.Join(',', context.OrderDetails.Select(od => od.Product.ProductName)))
            </Template>
        </RadzenDataGridColumn>
    </Columns>
</RadzenDataGrid>

@code {
    IQueryable<Order> orders;

    protected override async Task OnInitializedAsync()
    {
        await base.OnInitializedAsync();

        orders = dbContext.Orders.Include("OrderDetails.Product");
    }
}
10

Here is the new official demo:

11

Thanks for the demo, this is really helpful!

However I noticed that filtering occurs only when the grid has Data property specified. If the LoadData method is used instead, then LoadArgs.Filter is empty and completely ignores any input from OrderDetails column.