SORTING IN MVC USING MVC

declare @lSortCol VARCHAR(100)='UserName'
declare @SortOrder varchar(100)='asc' 

;with cte_test
as
(

 select 
 ROW_NUMBER() OVER (ORDER BY
        CASE WHEN (@lSortCol = 'UserName' AND @SortOrder='ASC')
                    THEN UserName
        END ASC,
        CASE WHEN (@lSortCol = 'UserName' AND @SortOrder='DESC')
                  THEN UserName
        END DESC,

   CASE WHEN (@lSortCol = 'Last_Name' AND @SortOrder='ASC')
                    THEN Aff_Name
        END ASC,
        CASE WHEN (@lSortCol = 'Last_Name' AND @SortOrder='DESC')
                  THEN Aff_Name
        END DESC

 ) AS ROWNUM 
 
 ,USERNAME from tbl_Users

)
select * from cte_test 
order by ROWNUM
--OFFSET 
--@Skip ROWS 
--FETCH NEXT 
--@Take ROWS ONLY

=====================================

@{
string SortExp= Model.SortExp ;
string SortOrder = Model.SortOrder;
}

@Html.Hidden("hdnSortCol",SortExp,new { @id = "hdnSortCol", @class = "hdnSortCol" })
    @Html.Hidden("hdnSortOrder",SortOrder,new { @id = "hdnSortOrder", @class = "hdnSortOrder" })

  <span prop-name="UserName" sort-type="@(SortExp=="UserName"?SortOrder:"DESC")" class="Sortable sort-asc Searchable " onclick="CustomSorting(this,event);">UserName</span> 

function CustomSorting(elem) {
    var sorttype = $(elem).attr("sort-type");
    var SortExp = $(elem).attr("prop-name");
    var stype = sorttype == "DESC" ? "ASC" : "DESC";
    $(elem).attr("sort-type", stype);
    $('#hdnSortCol').val(SortExp);
    $('#hdnSortOrder').val(stype);

    ShowDetails();
};