My own function is OrderBy

Question

I am writing a piece of code that is going to order a list of photos based on their rating. Each photo is stored in the database, and each of them has such information as the number of positive and negative votes. I want to order them according to the formula in which I calculate the percentage of positive votes, and the first photo with the highest percentage.

To do this, I used the standard IComparer interface and wrote my own Comparer function, which compares two photos. The problem is that I am doing that I need to download the list of all photos from db first. It seems like a lot of unnecessary effort I would like to avoid. Therefore, I am wondering if it is possible to create my own SQL function, which will compare on the side of the DB and return me only the photos that I want? Is it more efficient than comparing all photos on the server side?

Code for my own comparator:

public class PictureComparer : IComparer<Picture> { public int Compare(Picture p1, Picture p2) { double firstPictureScore = (((double)p1.PositiveVotes/(double)(p1.PositiveVotes+p1.NegativeVotes))*100); double secondPictureScore = (((double)p2.PositiveVotes / (double)(p2.PositiveVotes + p2.NegativeVotes)) * 100); if (firstPictureScore < secondPictureScore) return 1; if (firstPictureScore > secondPictureScore) return -1; return 0; } } 

And the code that the compiler uses:

  var pictures = db.Pictures.Include(q => q.Tags).Include(q => q.User).ToList(); pictures = pictures.OrderBy(q => q, new PictureComparer()).Skip(0 * 10).Take(10).ToList(); 

Answer 1

Remove the first ToList call and use the lambda expression instead of the comparator definition:

 var result = db.Pictures .Include(q => q.Tags) .Include(q => q.User) .OrderByDescending(q => q.PositiveVotes + q.NegativeVotes == 0 ? -1 : q.PositiveVotes / (double)(q.PositiveVotes + q.NegativeVotes)) .Skip(n * 10) .Take(10) .ToList(); 

Answer 2

The calculations in your comparison code are independent (i.e., the comparison simply depends on ordering the value, which can be calculated without reference to the element you are comparing). Therefore, you must first calculate your positive percentage number and just use the calculated value in your compam.

This must be done in the database, if possible (i.e. if you have access to make changes to the database). Databases are suitable for this kind of calculation, and you could do it on the fly without the need to cache the calculated values, by which I mean a view that determines the percentage for you, rather than predicting and saving the value every time there is a positive or negative voice. This saves you from having to upload all the photos for comparison, as you can simply order a positive percentage. The following is an example sql example that will do the job (note that this is just a sample ... you may want to keep the vote as little or something more efficient). The votes table contains a list of all votes for a particular photo and who voted for it.

 declare @votes table( pictureId int, voterId int, vote int) insert into @votes select 1,1,1 insert into @votes select 1,2,-1 insert into @votes select 1,3,1 insert into @votes select 1,4,1 insert into @votes select 2,1,-1 insert into @votes select 2,2,-1 insert into @votes select 2,3,1 insert into @votes select 2,4,1 declare @votesView table( pictureId int, positiveVotes int, NegativeVotes int) insert into @votesView select pictureId, sum(case when vote > 0 then 1 else 0 end) as PositiveVotes, SUM(case when vote < 0 then 1 else 0 end) as NegativeVotes from @votes group by pictureId select pictureId, convert(decimal(6,2),positiveVotes) / convert(decimal(6,2), (positiveVotes + negativeVotes)) as rating from @votesView