score:1

Accepted answer 
Table Item: ItemId
Table Rating: UserId, ItemId1, ItemId2, WinnerId

If you require that ItemId1 < ItemId2 in the Rating table, you only have to check the Rating table once.

var pair = db.Items.Join(db.Items,
  i1 => i1.ItemId,
  i2 => i2.ItemId,
  (i1, i2) => new {i1, i2}
)  //produce all pairs
.Where(x => x.i1.ItemId < x.i2.ItemId) //filter diagonal to unique pairs
.Where(x => 
  !db.Ratings
  .Where(r => r.UserId == userId
    && r.ItemId1 == x.i1.ItemId
    && r.ItemId2 == x.i2.ItemId)
  .Any() //not any ratings for this user and pair
)
.OrderBy(x => db.GetNewId()) //in-database random ordering
.First();  // just give me the first one

return new List<Item>() {pair.i1, pair.i2 };

Here's a blog about getting "random" translated into the database.

score:0

Assuming that the list of available items is in the database, I would handle this problem entirely in the database. You are hitting the database already, no matter what, so why not get it done there?

score:0

What about putting all the objects in a queue or a stack, and then pop 2 and 2 off until they are empty?

score:1

One solution is this: 

SELECT TOP 1 i.id item1, i2.id item2 from item i, item i2 
WHERE i.id <> i2.id 
AND (SELECT COUNT(*) FROM Rating WHERE userId=@userId AND FK_ItemBetter=i.id AND FK_ItemWorse=i2.id) = 0
AND (SELECT COUNT(*) FROM Rating WHERE userId=@userId AND FK_ItemBetter=i2.id AND FK_ItemWorse=i.id) = 0
ORDER BY NEWID()

I wasn't aware of the cross join method of just listing multiple FROM tables before.


Related Articles