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. Also, we cannot guarantee that it will not be changed 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;
Visual BasicDim op As CriteriaOperator = gridView1.ActiveFilterCriteria 'filterControl1.FilterCriteria
Dim filterString As String = DevExpress.Data.Filtering.CriteriaToWhereClauseHelper.GetDataSetWhere(op)
dataView1.RowFilter = filterString
Is there a helper function for MongoDB? Thanks!
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.
Thanks! Wish to see a helper function in future version since NoSQL is very important now.
Thank you for your feedback.
Hello,
Is it possible to do the reverse (creating filters on a gridview from SQL, Oracle, Access queries)?
Thank you
@Julien: We do not provide methods to parse SQL queries and create CriteriaOperator instances from them. In fact, this is not always possible.
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
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.
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
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.
Thanks!
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?
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.
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,
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.
I'm a bit late to the party but I just found this because I needed it.
These methods are useless and I had to do it manually.
The attached screenshot shows it:
GetOracleWhere result
"
""EVENT_DESCRIPTION"",
""contains"",
""asasasasasgg""
"
GetMsSqlWhere result (same as oracle)
"
""EVENT_DESCRIPTION"",
""contains"",
""asasasasasgg""
"
GetDataSetWhere result (this is actually ok, but no use as sql statement)
[
"EVENT_DESCRIPTION",
"contains",
"asasasasasgg"
]
Adding up to make it worse, the methods do not manage at all your own filter format which makes an array made of the filter, operator, filter, operator, and throws an error on the operator.
To be more clear, the array of filters is on screenshot "filters.jpg".
When you get to "and" and other operators, it throws exception. It should be care of the library to parse it.
This forces you to parse different parts of the query and glue them togheter.
In addition, which is even worse, what's coming from filters can be user free input, which is inevitable. This filter does not allow parametrization of the query and who writes in the filter can be a malicious actor putting malicious sql, causing sql injection.
So:
It doesn't produce valid sql.
Even if it was, you have to glue it togheter and make if/else to catch operators.
It's sql injection prone and does not allow to parametrize query.
Practically speaking, do it by yourself (which is what I did) and you do a better job.
I frankly don't get the sense of this part of the framework.
Hello Joshua,
I created a separate ticket on your behalf: T1035768: How to use CriteriaToWhereClauseHelper . We placed it in our processing queue and will process it shortly.