We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Excel Filtering on ForeignKey Column foreignKeyValue

So I know the following is true:
I'd like to know if there is a workaround to this limitation.  Using the foreignKeyField seems like a use case that would be a lot less prevalent than using foreignKeyValue.  I'd like to be able to use the Excel filtering, in particular the Search field on the Excel dropdown.  However, due to the limitation above, I get an ODATA error.

5 Replies

MS Manivel Sellamuthu Syncfusion Team September 5, 2019 04:15 PM UTC

Hi Kelly, 

Thanks for contacting us. 

By default in Grid, Sorting and Grouping will be performed based on foreignKey field only, Since it is the unique value that maps the two values and foreignKeyValue column may have a duplicate values. Due to the above reason ForeignKeyField value is used for Grouping and sorting.  

So please explain more about your requirement that will help us to provide better solution as soon as possible. 


KH Kelly Harrison September 5, 2019 05:49 PM UTC

Now I'm not so sure that's what's going on.  Here's the relevant portion of my model:

    public partial class ProductInventory
        public int ProductInventoryId { get; set; }
        public int ProductId { get; set; }
        public int LocationId { get; set; }
        public int Quantity { get; set; }
        public DateTime CreatedDate { get; set; }
        public DateTime ModifiedDate { get; set; }
        public virtual Location Location { get; set; }
        public virtual Product Product { get; set; }

    public partial class Product
        public int ProductId { get; set; }
        public string Material { get; set; }
        public string MaterialDesignation { get; set; }
        public string Notes { get; set; }

        public virtual ICollection ProductInventories { get; set; }
        public virtual ICollection Transactions { get; set; }

OData config from WebApiConfig.cs:

            ODataModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntityType().HasKey(p => p.ProductId);

            builder.EntityType().HasKey(l => l.LocationId);

            builder.EntityType().HasKey(t => t.TransactionId);

            builder.EntityType().HasKey(p => p.ProductInventoryId);
            builder.EntityType().ContainsRequired(p => p.Product);

When I try to search in the Excel filter bar, it hangs and I get the error below:

The url being generated by the grid is this:

TS Thavasianand Sankaranarayanan Syncfusion Team September 10, 2019 12:52 PM UTC

Hi Kelly, 

Thanks for the update. 

From that, we suspect that you have not handled the $apply query in the server side, So please ensure that in your end. If you still face the issue, please share us the server side code and data-manager code, to validate further. 

Thavasianand S. 

KH Kelly Harrison September 10, 2019 04:00 PM UTC

It's a generic Web API OData controller.  $apply should work out of the box.

        // GET: odata/Products
        [EnableQuery(MaxTop = 1000)]
        public IQueryable GetProducts()
            return db.Products;

The following url works fine:

Things only break when the filter parameter is added.  Likewise, the filter parameter by itself works without issue.

I've opened an incident and I'll post the solution we come up with here.

TS Thavasianand Sankaranarayanan Syncfusion Team September 11, 2019 02:15 PM UTC

Hi Kelly, 

Please followup on that incident for further updates.  

Thavasianand S. 

Live Chat Icon For mobile
Up arrow icon