Filtering on property of a navigation property

[jrlewis]

The code below shows the example grid I am working with.  I would like to be able to filter a grid on a property of a navigation property.  In this case the CompanyName of the Customer navigation property for an Order.  The grid doesn't quite handle this correctly.  I am guessing it can be handled similar to extending the onEachSort to alter the sorting query.  For this case I believe it would be the onEachWhere, but I'm not sure all the steps that need to be taken. 

The url below is for the "startswith" that is generated by the grid when trying to filter on the CompanyName for those starting with "A".  The problems with this are the complex property path in the select "$select=Customer/CompanyName" which would need to be "Customer", and the property name in the filter clause "$filter=startswith(tolower(CompanyName)" which would need to be "Customer/CompanyName". The other thing missing is the need to include an expand clause so the customer's company name is included in the query.

http://services.odata.org/V4/Northwind/Northwind.svc/Orders/?$select=Customer/CompanyName&$filter=startswith(tolower(CompanyName),'a')&$orderby=Customer/CompanyName

The url below shows what it is needed to get a valid return object.  I am not sure all the steps needed to create this url and then coerce the return into what needs to be displayed in the filter dialog.

http://services.odata.org/V4/Northwind/Northwind.svc/Orders/?$select=Customer&$expand=Customer($select=CompanyName)&$filter=startswith(tolower(Customer/CompanyName),'a')&$orderby=Customer/CompanyName

Grid Code

@{

    ViewBag.Title = "Home Page";

}

@(Html.EJ().Grid<object>("FlatGrid")

        .Datasource(ds =>

            ds.URL("http://services.odata.org/V4/Northwind/Northwind.svc/Orders")

            .Adaptor(AdaptorType.ODataV4Adaptor))

        .AllowFiltering()

        .FilterSettings(filter => { filter.FilterType(FilterType.Menu); })

        .AllowSorting()

        .SortSettings(b =>

            b.SortedColumns(col =>

                col.Field("Customer.CompanyName")

                .Direction(SortOrder.Ascending).Add())

            )

        .AllowPaging()

        .AllowSelection(false)

        .EnableRowHover(false)

        .Query("new ej.Query().select(['OrderID']).expand(['Customer($select=CompanyName)'])")

    .Columns(col =>

    {

        col.Field("OrderID").HeaderText("Order ID").Add();

        col.Field("Customer.CompanyName").HeaderText("Company Name").Add();

    })

)

<script>

    var onEachSort = ej.ODataV4Adaptor.prototype.onEachSort;

    

    ej.ODataV4Adaptor.prototype.onEachSort = function (e) {

        var res = [];

        if (!(e.name instanceof Array) && e.name.split(".").length > 1) {

            res.push(this._p(e.name) + (e.direction === "descending" ? " desc" : ""));

            return res.join(",");

        }

        return onEachSort.apply(this, [e]);

    };

</script>

[Venkatesh Ayothi Raman]

Hi Bob, 

 

Currently, we don’t have support for complex data operation in $expand with ODataV4Adaptor and we have considered this as a feature request in our database. We will implement this feature in any of our upcoming releases. The feature implementation would also greatly depend on the factors such as product design, code compatibility and complexity. 

Thanks, 

Venkatesh Ayothiraman.