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 => 
  .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.


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?


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


One solution is this:

SELECT TOP 1 item1, item2 from item i, item i2 
AND (SELECT COUNT(*) FROM Rating WHERE userId=@userId AND AND = 0
AND (SELECT COUNT(*) FROM Rating WHERE userId=@userId AND AND = 0

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

Related Articles