[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]
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:
C#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,
Hi Henry,
DevExtreme components have their own implementation unrelated to our ASP.NET MVC Extensions. That is why DevExpress.Data.Filtering.CriteriaToWhereClauseHelper is not present in your project. The DataSourceLoader.Load method returns data according to the load options received from the client. If your dxDataGrid has a filter expression, you will get it as the loadOptions.Filter parameter of the Get controller action.
Would you please clarify why and where you need to use DevExpress.Data.Filtering.CriteriaToWhereClauseHelper? It would be great if you also describe your final goal in greater detail. We will do our best to help you.
Hi Nikolai,
Thank you for responding,
I have some business page and its reach up to hundreds thousand records.
#1 I make a flexible query string to included security filter and something else
#2 I call Dapper to execute query (Dapper.Query return IEnumerable<T>)
#3 I call DataSourceLoader.Load(list, loadOptions);
I would like to have WHERE clause from loadoptions.Filter to build-in it into queryString before execute it by Dapper to reduce data query/return from database.
Current code:
try { if (super == false) { queryCmd = Util.ApplyDataFilter(queryCmd); } // return data var list = Util.DapperExecuteQuery<object>(Util.GetConnectionString(), queryCmd, null); // on custom get onExecuteGet(ref list); return DataSourceLoader.Load(list, loadOptions); } catch (Exception e) { throw new CustomException($"Execute [{eqdQuery.Name}] was failed. {e.Message}", e); } public static IEnumerable<T> DapperExecuteQuery<T>(string conn, string sqlString, object param) { using (var dbConn = new SqlConnection(conn)) { return dbConn.Query<T>(sqlString, param); } }Regards,
Hello Henry,
As Nikolai mentioned, you can add extra filtering criteria directly to the loadOptions.Filter property. Is it a possible solution for you? I have created a small test sample that illustrates this approach? I hope to hear from you soon.