SfGrid Makes 9 Calls to Database on Each Page Load, Uses Synchronous Calls in Asynchronous Context

I have created a very simple project outlining my issue here:

https://github.com/Mike-E-angelo/Stash/tree/master/Syncfusion.EfCore

It seems that when the SfGrid is on a page, 9 hits to the database are registered.  To reproduce, load the solution, F5, click on the Counter link, clear your debug output, and then hit the back button.  This is what I see:

```
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(@__p_0) [i].[Id], [i].[Name]
FROM [Items] AS [i]

```
Additionally, doing some debugging and loading of symbols, it appears that some if not all of these calls are from calling synchronous calls within an asynchronous context.  This is a poor practice which of other things leads to thread starvation.

Am I doing something completely wrong here?  I hope that is the case. :)  I did search for specific EfCore data adaptors, but did not see anything obvious.

Thank you for any assistance you can provide,
Michael

7 Replies 1 reply marked as answer

VN Vignesh Natarajan Syncfusion Team January 18, 2021 12:29 PM UTC

Hi Mike, 
 
Thanks for contacting us and we are glad to help to resolve your issue. 
 
From your query we understand that the problem might have occurred because of binding Queryable information as DataSource for Grid. As we will be processing this Queryable data in our source level to converted to List for binding this data to CurrentViewData of Grid and also for other internal actions, we suspect that, this internal actions might have caused the multiple times execute/database call problem.  
 
So we have prepared a sample using CustomAdaptor feature by fetching the DB data and converting it to List before bind that data to Grid as like the below code. Please download the sample from the link below, 
  
 
Code example: 
 
public override object Read(DataManagerRequest dm, string key = null) 
        { 
 
            IEnumerable<Order> DataSource = db.GetAllOrders(); 
                . . . . 
            if (dm.Take != 0) 
            { 
                DataSource = DataOperations.PerformTake(DataSource, dm.Take); 
            } 
            IEnumerable<Order> ResponseDataSource = DataSource.ToList(); 
            return dm.RequiresCounts ? new DataResult() { Result = ResponseDataSource, Count = count } : (object)DataSource; 
        } 
 
Refer our UG documentation for your reference 
 
 
We request you to download the above sample and check from your side. Kindly check with the above sample for the reproduce of issue and get back to us with the details, if you are still facing the reported problem.  
 
Regards, 
Vignesh Natarajan 
 


Marked as answer

MI Mike-E January 18, 2021 12:41 PM UTC

Looks like there was a reply to this thread... on Twitter. :)

https://twitter.com/ThisIsDinu/status/1351134856839835648

I will reply here as it's easier to follow, especially when code is involved.

Would it be possible to post your code to GitHub somewhere w/o the use of a Zip file?  I am weary of downloading zip files due to possible vulnerabilities, whether known or unknown.

Secondly, I did try using the CustomAdaptor as recommended. I actually mentioned this in the thread here:
https://twitter.com/Mike_E_angelo/status/1350831974710206464

However, I did realize now that I still had the DataSource set.  Removing this brings down the total SQL execution count to 4 per page load.  This is what I see now:

```
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*)
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
```
That still seems excessive.  I would expect one call per page load (or 1 call per subsequent filter modification).

Finally, the other remaining issue with using CustomAdaptor is that my Excel Filters no longer work.  I see that the DataManagerRequest has a bunch of settings.  If there is a resource/article on how to tie in those settings to compose a query it would be greatly appreciated.  I will be tying something together in the meantime.

Thank you for your assistance!
Michael


MI Mike-E January 18, 2021 12:46 PM UTC

OK!  I see the `DataOperations` class.  I see how that works now.  Thank you!

So really the only issue right now is the excessive SQL calls.  There appears to be four per page load, and this seems excessive.

Additionally, I've noticed that `CustomAdaptor.ReadAsync` gets called 2 times when the filter is opened, and then a 3rd time when the filter is applied.  This is 3 calls to the database for each filter adjustment.  The expectation here would be 1.

Thank you for any continued assistance.  It is much appreciated. :)


MI Mike-E January 18, 2021 01:05 PM UTC

Alright, got it.

I forgot to call ToListAsync on the IQueryable. :)

When I do this, I now see:

```
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*)
FROM [Items] AS [i]

```

Which seems like what I would expect.

Now the only issue is that Displaying the Excel Filter calls `CustomAdaptor.ReadAsync` twice.


VN Vignesh Natarajan Syncfusion Team January 19, 2021 01:08 PM UTC

Hi Michael, 

Thanks for the update.  

Query: “Now the only issue is that Displaying the Excel Filter calls `CustomAdaptor.ReadAsync` twice. 
 
We have analyzed the reported query and we are also able to reproduce the reported issue at our end also. We have confirmed it as a bug and logged the defect report “CustomAdaptor Read/ReadAsync method is triggered twice when opening a Excel Filter” for the same. Thank you for taking the time to report this issue and helping us improve our product. At Syncfusion, we are committed to fixing all validated defects (subject to technological feasibility and Product Development Life Cycle ) and including the defect fix in our weekly release which is expected to be rolled out by end of February, 2021. We will update you once the release is rolled out.    
       
You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.       
   
  
Till then we appreciate your patience.  

Regards, 
Vignesh Natarajan 





MI Mike-E January 19, 2021 01:22 PM UTC

Awesome!  Thank you Vignesh and team!


VN Vignesh Natarajan Syncfusion Team January 20, 2021 03:48 AM UTC

Hi Michael, 

Thanks for the update. 

Kindly follow our Syncfusion site for release related information. We will get back to you once the reported issue is fixed and included in our release as promised.   

Regards, 
Vignesh Natarajan  




Loader.
Up arrow icon