Filter on Foreign Key Column values

[Andrea Perazzolo]

Hi,

I have a problem filtering a Foreign key column.

My Grid and the foreign Key Field Datasources are bound to an ODataV4 datamanager.

When I try to filter on the ForeignKey column, the filter gives me no data, even if there are data on the recordset, as shown in the screenshots attached.

Also I want to ask if there is a way to change the condition of theForeignKey filter to contains instead of startswith, like you have showed me long time ago for the string columns.

I attached the screenshots before and afer filtering and the Netowrk call to the OData.

After some investigatiorns I have discovered that there are 2 filters done on the grid, the first on the Foreign key data source to retrieve the key field values, and the second on the grid, filtering for the ForeignKeyvalue in the list of values. If the filter list is too long, the call fails  and gives me no data.

   Thanks in advance

   Andrea Perazzolo

Attachment: Filtering_fa465d44.zip

[Prasanna Kumar Viswanathan]

Hi Andrea, 

Thanks for contacting Syncfusion support. 

Queries	Response
“Filter gives me no data”	We checked with our sample by binding an ODataV4 Adaptor to the Grid and ForeignKey Field dataSource and we did not face any issue while filtering the ForeignKey column.  Find the code example and sample:  <script type="text/javascript">      var dm = ej.DataManager({ url: "/odata/Employee", adaptor: new ej.ODataV4Adaptor() });        var dataManager = ej.DataManager({          url: "/odata/Order",          adaptor: new ej.ODataV4Adaptor()      });        $(function () {          $("#FlatGrid").ejGrid({              dataSource: dataManager,              ------------------------------              columns: [                        { field: "OrderID", width: 80, isPrimaryKey: true, textAlign: ej.TextAlign.Right, validationRules: { required: true, number: true }, headerText: "Order ID" },                        { field: "EmployeeID", foreignKeyField: "EmployeeID", foreignKeyValue: "FirstName", dataSource: dm, width: 75, headerText: "First Name" },    ---------------------------------              ],  Sample:  Try the attached sample and still if you face the same issue confirm the following details.  1. Did you face any exception while filtering?  2. Share the screenshot of a response window of a console page during the POST request of a filtering.    3. Essential Studio Version details.    4. Code example of an controller.    5. If possible, reproduce the issue in the attached sample.
“if there is a way to change the condition of theForeignKey filter to contains instead of startswith”	Currently we do not have support to change the filter operator for the ForeignKey column. So, we consider this as an improvement and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates.     https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents

Regards, 

Prasanna Kumar N.S.V 

[Andrea Perazzolo]

I have attached a video with the flow and data exchanged from the grid and the OData Feed.

My data in the Foreign Key field is very similar, so when the filter involves many different items, the second call has a length that exceeds IIS maxQuerySize.

In IISExpress the result is in an empty data, but in IIS the result is that the grid is stuck with the WaitingPopup icon that continue running, and the only resolution is to refresh the page.

    Thanks in advance

    Andrea Perazzolo

Attachment: 20160811_1431_31_3a092aaf.zip

[Seeni Sakthi Kumar Seeni Raj]

Hi Andrea, 

 

Before proceeding with the reported issue, can you please provide the following information to analyze the issue. 

 

1)      Sample data 

2)      Code example of Grid and Code behind 

3)      If possible, replicate the issue in previously attached sample. 

 

Regards, 

Seeni Sakthi Kumar S. 

[Andrea Perazzolo]

HI,

as I have written in another post, the problem is related to a security setting in IIS that prevents to resolve too long URL, as it is generated in the case that the filter in the Foreign Key field returns too many results (In my case more than 400. In IIS the default setting for maxQuerySize is 2048, but I am not able to increase this value. The problem is that in this case, when I use IIS Express, the grid returns no records, but in IIS the page is stuck with the Waiting Popup and the only way to move on is to hit F5 and refresh the page.

I have prepared the sample data and all, But at this moment I am not able to upload because the Upload box remain opened after the upload is complete. I hope to resolve this problem and send you the data as soon as possible.

[Andrea Perazzolo]

Attached the sample data and page

Attachment: Data_cb4b0a7b.zip

[Prasanna Kumar Viswanathan]

Hi Andrea, 

We regret for the inconvenience.            

We created a sample using your code example and database. When we filter the ForeignKey column we faced below exception. 

The length of the query string for this request exceeds the configured maxQueryStringLength value. 

 

Please confirmed with the above screenshot this is the exception you are facing in your sample.  To avoid the above exception we need to extend the maxQueryString Length in the web.config file. 

Find the code example: 

<system.web> 

    <authentication mode="None" /> 

    ------------------------------ 

    <httpRuntime targetFramework="4.5" maxUrlLength="2097151" maxQueryStringLength="2097151"  /> 

    ----------------------------------- 

    <httpHandlers /> 

  </system.web> 

  <system.webServer> 

    <validation validateIntegratedModeConfiguration="false" /> 

    <security> 

      <requestFiltering> 

        <requestLimits maxUrl="2097151" maxQueryString="2097151" /> 

      </requestFiltering> 

    </security> 

----------------------------------------------------------------------- 

Controller.cs 

        public IQueryable<aProdotto> Get(ODataQueryOptions options) 

        { 

            ODataValidationSettings c = new ODataValidationSettings(); 

            c.MaxNodeCount = 500; 

            options.Validate(c); 

            return db.aProdottoes; 

        } 

    } 

For more information refer the following link 

http://stackoverflow.com/questions/8159321/request-exceeds-the-configured-maxquerystringlength-when-using-authorize 

If we misunderstood your query, confirm the following details, 

1. In your database we did not find any ForeignKey relationship. So, please ensure that you have bind the ForeignKey relationship datasource for the ForeignKey column. 

2. In the sample we found that you have render the number as strings in the ForeignKey column. So, while filtering the ForeignKey column it has been filter with “startswith” operator. So, confirm that you have render the number as string in ForeignKey column. 

Regards, 

Prasanna Kumar N.S.V