This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 13%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
I am trying to add sorting to a Razor Page by simply passing sort parameters to a class which should ideally use them properly. The problem is that the directions from Microsoft for how to implement sorting in a Razor page do not tell how to do this. The instructions at https://learn.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-6.0 come up short by telling people to hard code sorting parameters and does not provide a dynamic solution.
I want to be able to pass any sort parameter from a Razor page to a service class that will then take that parameter and plug it into the ORDER BY part of the query.
My view code is quite simple. It is like this:
string sortBy = "post.Postid";
string sortDirection = "descending";
Posts = await PostService.GetPaginatedResultSorted(CurrentPage, PageSize, sortBy, sortDirection);
My class is far more complicated, so here is a redacted version. As you can see it plugs the sortBy parameter in where I used to simply type by hand post.Postid. However, passing "post.Postid" as a string from the view does not work. I didn't expect it would, but I thought it would crash the page and produce an error message due to that string not being the same type. I don't know what type it really is, but in my experience Microsoft loves to uses types as an excuse to stop processing the query. In this case it simply returns the results unsorted.
public async Task<List<Posts>> GetPaginatedResultSorted(int currentPage, int pageSize, string sortBy, string sortDirection)
{
return await (from post in _context.Posts
where REDACTED
orderby sortBy descending
select post into pts
select new Posts
{
REDACTED }).Skip((currentPage - 1) * pageSize).Take(pageSize).ToListAsync();
}
I tried changing the orderby part to say "sortBy sortDirection" but that results in a runtime error. I would appreciate it if a Microsoft employee would be nice enough to respond by explaining why their example does not say how to dynamically sort anything and how to do so.
Finally, I could not a find a category for this post titled "ASP.Net" or anything like that even though this is allegedly where the ASP.Net forum was moved according to Microsoft.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 90%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EZL%3C/text%3E%3C/svg%3E)
Hi @PostAlmostAnything ,
You could try to install the System.Linq.Dynamic.Core package and then use it to achieve the dynamic LINQ Query. then query statement looks like this:
var queryresult2 = _context.Products.Include(c => c.Photos).OrderBy("Id DESC").ToList();
More detail information, see Dynamic LINQ Simple Query.
Best regards,
Dillion
Thank you, that looks quite promising indeed.
Unfortunately, I discovered another problem because I calculate average rating per post in the loop and I can't think of a way to sort all posts by average rating. I have a posts table, ratings table, and comments table. Each rating must have one corresponding comment and one corresponding post, but comments and posts don't need to have any ratings.
The code used to calculate average rating runs in the redacted part of the code already posted and looks like this:
Ratingavg = ratingavg((from rac in pts.Ratings
where REDACTED
select rac into ra
select ra.Rating).Average())
To be continued due to Microsoft putting a character limit on my reply.
Previous reply continued:
If I try adding an OrderBy clause for Ratingavg it always fails. The end of the query ends up looking like ".OrderByDescending(o => o.Ratingavg).Skip((currentPage - 1) * pageSize).Take(pageSize).ToListAsync();" then when I try to run the site in debug mode I get a runtime error saying the LINQ expression could not be "translated" which must be the case since everything was still in English, but I never tried to translate anything. The error is further explained as "If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."
TO BE CONTINUED...
Second continuation"
"Those links don't appear very helpful since now that I think of it I don't know how to compute the average rating of all posts so that they can be sorted without having to get all posts first. This was not a problem when the site was first built because I followed Scott Hanselman's demo video in which he told people to get all data first and then work with it to avoid repeating yourself. He did not warn viewers that his example did not work once a site reaches a certain size. I hadn't worked with .Net for years and figured they'd probably wised up and programmed EF to do the necessary pagination to make using it in that way feasible, but it turns out you still need to do more work yourself. How can I sort all posts by average rating BEFORE taking the pages needed for the view?"
Your example does seem to work for basic dynamic sorting and for that I thank you. I tried passing "Postid DESC" as the sortBy parameter and it appears to have worked just fine. Now I guess I just need to program my views to select fields like Postid before passing them as a string formatted just like that. For instance, if I write something like var posttitle = Posts.Title; then how would I make the view convert Posts.Title to "Title DESC" without having to manually write a switch clause?
Long term I would like to have something like a pair of dropdown lists. The value of the first list would be the column to sort by and the second list would only let users choose between the values ASC and DESC. Then when the list is selected the values will be passed using something like "var sortBy = DDL1.SelectedValue + ' ' + DDL2.SelectedValue" I am still thinking in terms of .Net page controls in terms of syntax. I am also unsure as to how to populate the value of a select list using column names instead of values (ex: DDL1.SelectedValue is usually something like a number not a string containing the column name only).
The standard approach for dealing with complex queries is to create a View. It does not matter if you are using Entity Framework or stored procedures or a script. Create a View when the base query is complex. It is much easier to tune a View in SQL than say a LINQ query. Once you have the View working then your LINQ query will be much easier to deal with.
Agree with AgaveJoe, for the complex queries, you could try to directly execute the SQL query or use view/ stored procedure. Refer the following tutorials:
Also, it seems EF is incapable of sorting by average rating even when passing the parameter is not an issue. If the end of the query says ".OrderBy(p => p.Ratingavg).Take(pageSize).ToListAsync();" Then it results in an error too long to paste which I have never seen.
On top of this, it seems EF is not able to both calculate an average rating for each post and sort by that rating without first getting all post data which is not feasible due to the number of posts involved. How can someone calculate the average of all ratings and use them to rank posts WITHOUT having to start the process by getting all posts?
This is a problem because if I were to use the rating from the paginated results then only the 10 latest posts have their ratings compared.
The only think I can think of is writing a new task that starts with the ratings table instead of the posts table before figuring out the average rating using the post id in the ratings table and then using navigation if I need specific post data.