Hi i am using Devextreme angular grid and ASP.NET MVC core APIs as backend.
I am using SQL Server stored procedure to get data from database and send response through API.
I want to pass DataSourceLoadOptions filter parameters to my stored procedures as a parameter, so that i can put filter on server side and fetch only the necessary records, rather than fetching all records and then apply the DataSourceLoadOptions filter in C#.
I think that can only be possible if i can get the filter option serialized in a string format, which can be read by SQL server .
for example "where ID=10".
Please advice, if there is any way to achieve this.
Thanks
Hello,
The DataSourceLoadOptions.filter property contains arrays of filter operators. This syntax is different than SQL syntax. We don't have tools or utilities that convert our filters into SQL. You will need to manually implement such converters if you wish to pass our filter to your data base.
However, it is unclear why it is necessary to pass filters in a custom way. Our DataSourceLoader operates with IEnumerable or IQueryable objects. It creates a LINQ expression that will be transformed into SQL by the Data Layer you are using in your back-end (for example, Entity Framework). Entity Framework doesn't query the data base if you just create an expression:
var result = MyEntities.Where(e => e.qty > 100); // it doesn't query the data base
So, if you don't explicitly call ToList or similar methods, the data base is not used. Our DataSourceLoader first applies all DataSourceLoadOptions and then queries the data base. So, no extra data is loaded. You can check it by profiling queries to your data base.
If your situation is different, please send us your server side code that uses DataSourceLoader.
Hi Andrew,
Thanks for your reply.
I see what you are suggesting, but i am not using EntityFramework but dapper.
I am directly calling the stored procedure and get the dataset, so Linq is not involved in here.