BuyLog In
Ticket T958499
Visible to All Users

AspNet.Data - Serialize DataSourceLoadOptions options to SQL-compatible strings

created 4 years ago

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

Comments (2)
Andrew Ser (DevExpress Support) 4 years ago

    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:

    C#
    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.

    KP KP
    kumud pandey 4 years ago

      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.

      Sign in to comment on this post

      Answers approved by DevExpress Support

      created 4 years ago

      Hi kumud,

      Thank you for the clarification. Stored Procedures always load all data when an SQL command is executed. If your stored procedure supports certain parameters to obtain data on demand, you can try to adopt the parameters we pass to DataSourceLoadOptions and use them in your stored procedure.

      Please note that conversion between DataSourceLoadOptions and your stored procedure is not straightforward and may require a lot of time and resources. While we don't have a built-in solution for such cases, the best we can offer is general guidance and advice for those who choose to implement this scenario.

      Thanks,
      Artem

        Comments (1)
        GM GM
        Gregory Meier 3 years ago

          Dear All,

          Is there any solution to this problem? I have this same situation.
          In my case, I actually only want to do paging, sorting in the Web API.

          I use dxDataGrid in an Angular mobile app. This mobile will call an ASP.Net REST API. Problem is, this API will return tons of records such as 100.000 records is normal.

          dxDataGrid paging works perfectly on the client-side but it must wait for data returned by the API which is very slow on the server. That is why I need to think of a way to do paging and sorting (which are the 2 parameters that affect the paging?) on the server.

          So it looks like we must send DataSourceLoadOptions to the Rest API then get the page parameter and sort parameter from this option to use in the Store procedure using the OFFSET / FETCH method.

          Is this a correct way to implement this function?

          does DevExpress has an example about using Stored Procedure with paging or something that can work in a big database?

          P/S: I really like to use Stored Procedure in SQL Server.

          Thanks

          Sign in to comment on this post

          Other Answers

          created 3 years ago

          Hi Gregory,

          with the help of some R&D and efforts, i made a work around for this. i created a parser function , which converts all parameter of DataSourceLoadOptions to DevExpressOptionModel (including filters, it converts DataSourceLoadOptions filter parameter to sql string).

          we can use DevExpressOptionModel model properties to pass parameter to our Sql server stored procedures.

          DevExpressOptionModel devExpressOption = DevExpressParser.ParseFilterOption(loadOptions);

          I am attaching some screen shots and code snippet about how to use this.
          Please have a look and let us know if that helps.

            Show previous comments (2)
            KP KP
            kumud pandey 3 years ago

              @Gregory Yes, i use dynamic query on stored procedure

              GM GM
              Gregory Meier 3 years ago

                @kumud pandey thanks

                KP KP
                kumud pandey 3 years ago

                  @Gregory Your most welcome. i am glad i could help.

                  Sign in to comment on this post

                  Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

                  Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.