We have a Blazor WASM project on .NET 7, using VS 2022, C#, SQL Server, and a repository pattern.
We have a Blazor page that allows the client user to select filters to fill an HTML table of filtered data from the server-side database repository. We anticipate when placed into production there can be a million rows in a SQL Server view for the page.
My question is asking the best way to implement the GET data request on the server side.
I see the request starting at the PAGE calling the client service that invokes the server controller that fetches data from the server repository.
For example, getting data from the SQL Server view that can contain a million rows, and I am wondering the best way to fetch the filtered data.
I see the service function and controller method supporting parameters that can be used in a LINQ statement that can use
somecollection.Where(...field1==value1 && field2==value2...etc.)
from a collection of GetAllRecords() call from the repository.
Is this a good way to return back to the client-side the list of filtered data of 100 rows (for example)?
From my limited knowledge and perspective, I ask...
- should the controller do the filtering by a Linq statement
OR
- should the repository perform the Linq filtering
OR
- have a number of SQL Server stored procedures called by the repository
OR
- some other manner
Based on the helpful comments, I found it easy to create a stored procedure with parameters so that the DB fetches only 100-records back to the controller and to the client service. I am/was very familiar with stored procedures from my VB and ASP experience but did not know I could call the SProc from the repository. A few searches brought me to the C# code I used to remedy my original question. Here is the link...
https://www.c-sharpcorner.com/UploadFile/pchandraker/passing-table-valued-parameter-to-stored-procedu-part-1/