Sortable MVC Tables with Stored Procedure

Question

Hello everyone and thanks for the help in advance. I am developing a MVC application that requires a sortable table. The issue is that the tables utilize queries that are best suited for stored procedures. While researching, I cam across older articles such as: https://stackoverflow.com/questions/15621609/t-sql-conditional-order-by, using either dynamic SQL or some type of CASE structure, but wondered if there were better ways to approach this problem. New example seem to rely on creating conditional LINQ queries within the controller, but I don't think that would be optimal. Any help would be appreciated.

Answer 1

using either dynamic SQL or some type of CASE structure

Yes, these are typically the solution. The solution with CASE breaks down quite quickly if you want to sort by more than one column, since you need keep different data types in different CASE expressions.

The other alternative is to sort in the client.

Answer 2

T-SQL has OFFEST and FETCH NEXT

SELECT SomeColumns 
FROM MyTable
WHERE aColumn = 'aValue'
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Answer 3

I suggest that you consider use of Linq to entities which will be much easier and more practical for the ASP.NET Core MVC app if you do not have concrete reason to use stored procedure. Please see the following Microsoft tutorial:

Tutorial: Add sorting, filtering, and paging - ASP.NET MVC with EF Core