Current filter:
                                You should refresh the page.
                                  • Description:
                                    I wish to filter the DataView instance by using an active filter criteria of the grid or the FilterControl. Is there any way to convert the current filter expression to a valid one to filter the DataView?

                                    Answer:
                                    We use the DevExpress**.Data.Filtering.CriteriaToWhereClauseHelper** class for similar purposes. In simple cases, you can use CriteriaToWhereClauseHelper in your application as long this class suits your needs. Please keep in mind that CriteriaToWhereClauseHelper is intended for internal purposes only. We neither provide any warranties/documentation nor have plans to add new features to this class in the future.

                                    You can use the following methods to build SQL, Oracle, Access queries and to filter the DataView object:
                                    - GetAccessWhere,
                                    - GetDataSetWhere,
                                    - GetDynamicLinqWhere,
                                    - GetMsSqlWhere,
                                    - GetOracleWhere
                                    Below is an example illustrating how to filter the DataView object based upon an active filter criteria of the grid by using the static DevExpress.Data.Filtering.CriteriaToWhereClauseHelper.GetDataSetWhere method.

                                    [C#]
                                    CriteriaOperator op = gridView1.ActiveFilterCriteria; //filterControl1.FilterCriteria string filterString = DevExpress.Data.Filtering.CriteriaToWhereClauseHelper.GetDataSetWhere(op); dataView1.RowFilter = filterString;
                                    [VB.NET]
                                    Dim op As CriteriaOperator = gridView1.ActiveFilterCriteria 'filterControl1.FilterCriteria Dim filterString As String = DevExpress.Data.Filtering.CriteriaToWhereClauseHelper.GetDataSetWhere(op) dataView1.RowFilter = filterString
                                Show all comments
                                • John Smith 209 11.28.2013

                                  Is there a helper function for MongoDB? Thanks!

                                • Michael (DevExpress Support) 11.28.2013

                                  We do not provide similar functions for other database systems. You can create your own converter by extending (inheriting from) the DevExpress.Data.Filtering.Helpers.BaseWhereGenerator class and overriding its Visit methods to return appropriate string representations of criteria operators.

                                • John Smith 209 11.28.2013

                                  Thanks! Wish to see a helper function in future version since NoSQL is very important now.

                                • Michael (DevExpress Support) 11.29.2013

                                  Thank you for your feedback.

                                • Julien V. 02.03.2014

                                  Hello,
                                  Is it possible to do the reverse (creating filters on a gridview from SQL, Oracle, Access queries)?
                                  Thank you

                                • Michael (DevExpress Support) 02.03.2014

                                  @Julien: We do not provide methods to parse SQL queries and create CriteriaOperator instances from them. In fact, this is not always possible.

                                • Chris D 09.18.2014

                                  This is a really cool function but i have one problem.
                                  When i convert to a sql-String, the string will contain the displayname instead of the database name , any idea ?
                                  Thanks

                                • Anatol (DevExpress Support) 09.19.2014

                                  Hello Chris,

                                  To process your post more efficiently, I created a separate ticket on your behalf: T152725: CriteriaToWhereClauseHelper.GetDataSetWhere returns a string that contains the displayname instead of the database name. This ticket is currently in our processing queue. Our team will address it as soon as we have any updates.

                                • Janis Gattringer 11.25.2014

                                  Hi,
                                  really nice function thanks for that one i was searching for this!
                                  But now im facing another Problem. Some of the columns in my Grid has the type int32 or datetime. As you know i cant use the LIKE Operator fot These columns. Is there any way to solve this Problem whithout cast the columns to another data type in my data source?
                                  Best regards

                                • Uriah (DevExpress Support) 11.25.2014

                                  Hello Janis,

                                  To process your recent post more efficiently, I created a separate ticket on your behalf: T178396: Is there any way to solve the problem with Int32 or DateTime columns when using the approach described in the K18434 article?. This ticket is currently in our processing queue. Our team will address it as soon as we have any updates.

                                • Janis Gattringer 11.25.2014

                                  Thanks!

                                • David Sanchez 5 06.24.2016

                                  I found that the GetMsSqlWhere does not convert the filter expression at all, it only adds N'<expression>' but I was expecting the conversion of StartWith to Like 'ABC%'.  Is this all the function does which is not useful for converting?

                                • Svetlana (DevExpress Support) 06.27.2016

                                  Hi,

                                  I have created a small project to test how CriteriaToWhereClauseHelper converts criteria containing the StartsWith filter function. On my side, everything operates as expected. Please refer to the attached console project. Feel free to modify it to show what I am missing to see the issue. 

                                • Henry Thong 04.16.2018

                                  Hi,

                                  I have a MVC Core API (target framework .Net Core 2.0), it provide data for my dxDataGrids.
                                  How to add DevExpress.Data.Filtering.CriteriaToWhereClauseHelper into my project and using helpers method like as above guideline?
                                  I cannot find DevExpress.Data.Filtering  library file (.dll) in anywhere.  I also connect to DevExpress Nuget server and searching keywork "DevExpress.Data" but it's no results.

                                  Current code:
                                  using DevExtreme.AspNet.Mvc;
                                  using DevExtreme.AspNet.Data;

                                  [HttpGet]
                                          public virtual object Get(DataSourceLoadOptions loadOptions)
                                          {
                                                var list = _dbContext.Products.Where(x => x.ParentId == parentId);
                                                return DataSourceLoader.Load(list, loadOptions);
                                          }

                                  Regards,

                                • Nadezhda (DevExpress Support) 04.16.2018

                                  Hello Henry,

                                  I've created a separate ticket on your behalf ( How to use CriteriaToWhereClauseHelper in a MVC application ). It has been placed in our processing queue and will be answered shortly.

                                0 Solutions

                                Creation Date Importance Sort by