Linq to return records that don't have a follow up record

Question

I have this query that counts the total number of +1's a user has made on our website:

return db.tblGPlusOneClicks
    .Where(c => 
        c.UserID == UserID
        && c.IsOn
        )
    .Select(c=>c.URLID)
    .Distinct()
    .Count();

Data originates from this table:

A simple count of distinct URLs where IsOn = true will show the count of pages they have +1'd. However, the table also stores when they un-plus1 something, by storing the value in IsOn as false.

If I:

• Plus one my homepage
• Unplus one my homepage

It shouldn't count this as a plus for that user in our query as the last action for this URL for this user was to un-plus 1 it. Similarly, if I:

• Plus one my homepage
• Unplus one my homepage
• Plus one my homepage
• Unplus one my homepage
• Plus one my homepage

It should count this in the original query as the last action for that URL was to plus 1 it.

How can I modify my query to count the instances where IsOn is true and that was the last known action for that user for that URL? I'm struggling to write a query that does this.

Answer 1

Try this:

return db.tblGPlusOneClicks
    .Where(c => c.UserID == UserID)
    .GroupBy(c => c.URLID)
    .Count(g => g.OrderByDescending(c => c.Date).First().IsOn);

Answer 2

Sounds like you could do something like this:

return (from c in db.tblGPlusOneClicks
        where c.UserID == UserID
        group c by c.URLID into g
        where g.OrderByDescending(x => x.Date).First().IsOn
        select g.Key).Distinct().Count();

Answer 3

Making no assumptions about what the balance for up/downvotes could be:

return db.tblGPlusOneClicks
    .Where(c => c.UserID == UserID)
    .GroupBy(c=>c.URLID)
    .Select(g => new {
          URLID = g.Key,
          VoteBalance = g.Aggregate(0, (a,i) => a+(i.IsOn?1:-1))
    })
    .Sum(u => u.VoteBalance);

This takes all previous votes into account, rather than looking just at the latest record. It is, of course, up to you, what you prefer.