[DevExpress Support Team: CLONED FROM K18434: How to convert the CriteriaOperator to the corresponding filter string in order to use it for building SQL queries or for filtering the DataView]
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.
Hi Joshua,
As far as I understand, you need to parse a filter written in the syntax our DevExtreme widgets use (Filtering). This syntax is not correct for CriteriaOperators. They have their own syntax described in the following topic: Criteria Language Syntax. That's why you are getting an incorrect filter. The correct syntax to search for a substring in CriteriaOperators will be "Contains([ProductName], 'dairy')". If you pass this criteria to the
CriteriaToWhereClauseHelper.GetMsSqlWheremethod, you will get the following SQL query:To give you an accurate information on how to achieve the required functionality, would you please clarify how you wish to use a SQL query generated from a DevExtreme filter? For example, we have the DataSourceLoader class that works with DevExtreme filters and can apply them to collections that implement the IEnumerable or IQueryable interfaces.
None of the solutions proposed worked well and all the getSql methods where useless in producing useful sql since they all produced either broken or empty results.
I had to parse every bit of filter array produced by DataSourceLoader manually in the end.
The DataSourceLoader is ineffective with something that does not use IQueryable, since it would require some kind of framework (like Entity Framework or any other ORM)
IEnumerable it's not really an option too, since it would mean to not use deferred loading and load every single record the query produces, the filter it locally.
Imagine a datagrid whose source table has 2 million records.
Hi Joshua,
You are right. The Get…Sql methods described in the original article are not useful for DevExtreme filters. Those methods are not compatible with DevExtreme. They are designed for the Criteria Operator language that we implemented before we started developing the DevExtreme product line. DevExtreme has its own filtering engine and so there is no way to use the Get…Sql methods to convert DevExtreme filter expressions.
Our goal is to find the best solution for your usage scenario. However, we need to better understand it first. From what I gather, you wish to convert a DevExtreme filter expression to SQL. Please clarify the following:
I look forward to your response.
Hello Andrew,
My architecture is the following:
Mvc .NET 5 application. The queries are passed to a Unit Of Work + Repository pattern.
The pattern expose a service which directly fires queries on an Oracle database through ODP.NET (Official Oracle library to use oracle in .NET software).
The queries are hardcoded into the code with parameters passed to the methods that fire them.
In my particular use case and in the case of DataGrid remote operations, the WHERE expression generated by DataSourceLoader should be converted in an usable where condition that can be appended to the query directly (or with minor modifications like manually adding the aliases and so on)
WEB -> Application: through DataSourceLoader
Application -> Database: Application appends manually converted to sql filter to the query and filters it.
Hope this answers your questions. If you need further details let me know.
Thanks