RadzenDataGridColumn filter by related entity with n:n relationship doesn't create the filter with the any operator

Radzen.Blazor Components
1

Hi,

First of all thanks for such a great set of components in Radzen and all the effort behind it, I really enjoy the work done here.

I wanted to ask regarding the RadzenDataGridColumn using OData with relationships.

The scenario presents an entity EntityA related to an entity EntityB with a n:n relationship. When applying a filter using RadezDropdown in RadzenDataGridColumn, the OData filter generated is not accepted by the OData endpoint.

OData Url generated: https://localhost/api/odata/EntityA?$filter=EntityB/Type+in+('TypeA'%2C'TypeB')
OData Url expected: https://localhost/api/odata/EntityA?$filter=EntityBs/any(entityB:+entityB/Type+in+('TypeAId'%2C'TypeBId'))) (or something equivalent)

Code:

<RadzenDataGrid @ref=radzenDataGrid
    AllowFiltering=true
    AllowPaging=true
    AllowSorting=true
    AllowColumnResize=true
    Count=@entityAsCount
    ColumnWidth="180px"
    Data=@entityAs
    FilterCaseSensitivity="FilterCaseSensitivity.CaseInsensitive"
    FilterMode="FilterMode.Advanced"
    FilterPopupRenderMode="PopupRenderMode.OnDemand"
    IsLoading=@isLoading
    KeyProperty=@nameof(EntityA.Id)
    LoadData=@LoadData
    PagerHorizontalAlign="HorizontalAlign.Center"
    PageSize=PAGE_ITEMS
    TItem=EntityA>
	<Columns>
...
		<RadzenDataGridColumn Property="EntityBs"
		                  FilterProperty="EntityB/Type"
		                  TItem=EntityA
		                  Title="Entity Bs"
		                  Type=@typeof(IEnumerable<EntityB>)
		                  FilterValue=@selectedEntityBs
		                  FilterOperator=FilterOperator.Contains
		                  Sortable=false>
			<FilterTemplate>
			    <RadzenDropDown @bind-Value=@selectedEntityBs
			                    class="w-100"
			                    Change=@OnSelectEntityB
			                    Data=@entityBTypes  <-- Enum values
			                    TextProperty="Text"
			                    ValueProperty="Value"
			                    AllowClear=true
			                    AllowSelectAll=false
			                    Multiple=true />
			</FilterTemplate>
			<Template Context="entityA">
			    @entityA.EntityBsJoinedByCommaString
			</Template>
		</RadzenDataGridColumn>
...
@code {
    [Inject]
    private EntityARepository EntityARepository { get; set; } = default!;

    public bool isLoading = true;
    public int entityAsCount = default!;
    public RadzenDataGrid<EntityA> radzenDataGrid = default!;
    public IEnumerable<EntityA> entityAs = default!;

    public IEnumerable<object> entityBsTypes = Enum.GetValues(typeof(EntityByTypes)).Cast<Enum>().Select(t => new { Text = t.ToString(), Value = t });
    public IEnumerable<Enum> selectedEntityBs = default!;

    async Task OnSelectEntityB(object value)
    {
        await radzenDataGrid.FirstPage();
    }

    async Task LoadData(LoadDataArgs args)
    {
        var result = await EntityARepository.Get(
            count: true,
            expand: @nameof(EntityA.EntityBs),
            filter: args.Filter,
            orderby: args.OrderBy,
            select: $@"
                {nameof(EntityA.Id)},
                {nameof(EntityA.Description)},
                {nameof(EntityA.CreatedDateTime)},
            ",
            skip: args.Skip,
            top: args.Top
        );

        entityAsCount = result.Count;
        entityAs = result.Value.AsODataEnumerable();

        isLoading = false;
    }
}

EntityARepository.cs: 

public async Task<ODataServiceResult<EntityA>> Get(
    bool? count = default,
    int? skip = default,
    int? top = default,
    string? expand = default,
    string? filter = default,
    string? orderby = default,
    string? select = default
)
{
    var uri = routeRepository.GetUri(entityARouteId);
    var oDataUriRoute = uri.GetODataUri(
        count: count,
        expand: expand,
        filter: filter,
        orderby: orderby,
        select: select,
        skip: skip,
        top: top
    ).ToString();

    return await httpClient.GetFromJsonAsync<ODataServiceResult<EntityA>>(
        oDataUriRoute,
        jsonSerializerOptions
    ) ?? default!;
}

I'm trying to follow the Radzen components documentation examples, but I'm not able to make it work in such scenario.

Any idea will be more than welcome.

And again thanks to the team for such amazing components and community.

Javier

Context info:

<TargetFramework>net7.0</TargetFramework>
<PackageReference Include="BuildWebCompiler2022" Version="1.14.10" />
<PackageReference Include="Microsoft.AspNetCore.Components.WebAssembly" Version="7.0.5" />
<PackageReference Include="Microsoft.AspNetCore.Components.WebAssembly.DevServer" Version="7.0.5" PrivateAssets="all" />
<PackageReference Include="Microsoft.Authentication.WebAssembly.Msal" Version="7.0.5" />
<PackageReference Include="Microsoft.AspNetCore.WebUtilities" Version="2.2.0" />
<PackageReference Include="Microsoft.Extensions.Http" Version="7.0.0" />
<PackageReference Include="Radzen.Blazor" Version="4.12.0" />
2

Hi @Reibax,

The OData expression generated by our DataGrid in this case will use actually any() however instead in we are using or with eq :

https://services.odata.org/V4/Northwind/Northwind.svc/Employees?$filter=Orders%2fany(i%3ai%2fCustomer%2fCustomerID+eq+%27ALFKI%27+or+i%2fCustomer%2fCustomerID+eq+%27ANATR%27)&$top=4&$skip=0&$expand=Orders(%24expand%3dCustomer)&$count=true

image
image1920Ă—1080 224 KB

As far is we know expression like Orders/any(o: o.Id in (1,2,3)) is not supported while expression like this is supported:
Orders/any(o: o.Id eq 1 or o.Id eq 2 or o.Id eq 3)

3

Thanks for your fast response @enchev !!

The picture you provided is exactly the example I'm following as guideline, though I'm not able to generate the any filter expression in my example.
The difference I see in the entities relationships is the following:

Radzen example: Employee <-> Orders -> Customer
My scenario: EntityA <-> EntityBs

But I can't understand why the any expression isn't generated in my OData urls scenario.

4

The uniqueness in this filter type (In/NotIn) is that we gave the ability to specify different property names for Property and FilterProperty of the column:

image
image1920Ă—1080 292 KB

Property points to the collection property (each employee have multiple orders) while FilterProperty will be used to access the properties of the item in the collection (each order have customer).

5

Thanks again for your fast response. This is awesome! :smiley:

What if an employee has multiple orders, but I just want to filter by order.ShipCountry? How should I configure the RadzenDataGridColumn? (I think that's something similar to the scenario I'm trying to configure, where I'm finding the blocker because the any isn't added to the OData url)

EmployeeId      OrdersShipCountries  <= Adding a RadzenDropdown with CountryEnum to filter
===================================
1               EEUU,Canada

Thanks for helping me understand the difference between Property and FilterProperty, I find it clear now. :+1:

6

In this case Property=“Orders” and FilterProperty=“ShipCountry”

1 Like
7

You're right! That seems to work, it's missing the enums value quotes in the url, but it looks good... Then I don't understand where's the difference :sweat_smile:. Is the Radzen version running in the demos 4.12.0? Yes it is. I find it really strange that works here but not in my context. Probably it's a PICNIC error... but hard to find :mag:.

Screenshot 2023-06-01 193104
Screenshot 2023-06-01 1931041515Ă—854 108 KB

JIC you want to take a look:

@using RadzenBlazorDemos.Data
@using RadzenBlazorDemos.Models.Northwind

<RadzenDataGrid @ref="grid" @bind-Value=@selectedEmployees KeyProperty="EmployeeID" IsLoading="@isLoading" Count="@count" Data="@employees" LoadData="@LoadData" FilterPopupRenderMode="PopupRenderMode.OnDemand" FilterCaseSensitivity="FilterCaseSensitivity.CaseInsensitive" FilterMode="FilterMode.Advanced" AllowSorting="true" AllowFiltering="true" AllowPaging="true" PageSize="4" PagerHorizontalAlign="HorizontalAlign.Center" TItem="Employee" ColumnWidth="200px">
<Columns>
    <RadzenDataGridColumn TItem="Employee" Property="EmployeeID" Filterable="false" Title="ID" Frozen="true" Width="50px" TextAlign="TextAlign.Center" />
    <RadzenDataGridColumn 
        TItem="Employee" 
        Title="OrdersShipCountries" 
        Property="Orders" 
        FilterProperty="ShipCountry" 
        Sortable=false
        Type="typeof(IEnumerable<Customer>)" 
        FilterValue="@selectedCountries" 
        FilterOperator="FilterOperator.In"
    >
        <FilterTemplate>
            <RadzenDropDown
                @bind-Value=@selectedCountries 
                Style="width:100%"
                Change=@OnSelectedCustomersChange
                Data=@countries 
                AllowSelectAll="false" 
                Multiple="true" 
                MaxSelectedLabels="2" 
            />
        </FilterTemplate>
        <Template>
                @(string.Join(',', context.Orders != null ? context.Orders.Select(i => i.ShipCountry) : Enumerable.Empty<string>()))
        </Template>
    </RadzenDataGridColumn>
</Columns>
</RadzenDataGrid>
@code {
    public enum Country {
        Austria,
        Finland,
        Italy,
        EEUU,
        Canada
    }


    public IEnumerable<object> countries = Enum.GetValues(typeof(Country)).Cast<Enum>();
    public IEnumerable<Enum> selectedCountries = default!;
        
    bool isLoading;
    int count;
    IEnumerable<Employee> employees;
    IList<Employee> selectedEmployees;
    RadzenDataGrid<Employee> grid;

    List<string> titles = new List<string> {"Sales Representative", "Vice President, Sales", "Sales Manager", "Inside Sales Coordinator" };
    IEnumerable<string> selectedTitles;

    async Task OnSelectedTitlesChange(object value)
    {
        if (selectedTitles != null && !selectedTitles.Any())
        {
            selectedTitles = null;  
        }

        await grid.FirstPage();
    }

    IEnumerable<string> selectedCustomers;

    async Task OnSelectedCustomersChange(object value)
    {
        if (selectedCustomers != null && !selectedCustomers.Any())
        {
            selectedCustomers = null;
        }

        await grid.FirstPage();
    }

    NorthwindODataService service = new NorthwindODataService("https://services.odata.org/V4/Northwind/Northwind.svc/");

    IEnumerable<Customer> customers;

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

        var result = await service.GetCustomers();
        customers = result.Value;
    }

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

        var result = await service.GetEmployees(filter: args.Filter, top: args.Top, skip: args.Skip, orderby: args.OrderBy, count: true, expand: "Orders($expand=Customer)");
        // Update the Data property
        employees = result.Value.AsODataEnumerable();
        // Update the count
        count = result.Count;

        isLoading = false;
    }
}

Thanks for you patience, guidance and help :slightly_smiling_face:

1 Like
8

I found the issue. I leave it here just in case someone else is struggling with this too.

The issue in my scenario was in the FilterOperator value.
I started developing the feature with Radzen version 4.8.3. The FilterOperator.In is not availble in this version so I set it with FilterOperator.Contains. Because it wasn't working as I was expecting I updated the Radzen version but didn't change the FilterOperator value. With FilterOperator.In the OData url is created as expected.

Thanks for your help @enchev