CODEWITHSUNDEEP
c#.netsqllinqentity-framework
domderen
domderen

Reputation: 643

My own OrderBy function

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

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

The code for my own comparer:

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 which uses the comaprer:

 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();

Upvotes: 4

Views: 196

Answers (2)

Mark Byers
Mark Byers

Reputation: 838716

Remove the first call to ToList and use a lambda expression instead of defining a comparer:

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();

Upvotes: 6

Mike Hanrahan
Mike Hanrahan

Reputation: 1192

The calculations in your comparer code are independent (i.e. the comparison just depends on ordering a value that can be calculated without reference to the item you are comparing to). Therefore you should calculate your positive percentage number first and just use the calculated value in your comparer.

This should certainly be done in the database if possible (i.e. if you have access to make changes to the database). Databases are suited to this kind of calculation and you could probably do it on the fly without having to cache the calculated values, by which I mean have a view that works out the percentage for you rather than precalculating and storing the value everytime there is a positive or negative vote. This will obviate the need to download all the photos to compare, as you can just order by the positive percentage. Below is some sample sql that will do the job (note it is only a sample...you might want to store the vote as a bit or something more efficient). The votes table contains a list of all votes for a particular picture 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

Upvotes: 1

Related Questions