Omitting duplicate row sets from Linq-to-SQL subquery

Question

I'm writing a service that tracks game stats for an online shooter. Clients report events to the server which are then recorded in a SQL Server database. The server is expected to generate a report of game events aggregated from client reports - the authenticity of an event claim is based on the number of clients that reported it.

I have a Linq-to-SQL query that groups event reports from different clients based on the time they occurred (within 7 seconds).

Events
    .Select(e => 
        Events.Where(ev =>
            // Same event type
            ev.Discriminator == e.Discriminator &&
            // Same match
            ev.ServerIpAddress == e.ServerIpAddress &&
            SqlMethods.DateDiffSecond(ev.MatchStartTime, e.MatchStartTime) < 30 &&
            // Find nearby events
            Math.Abs(ev.MatchTime.TotalSeconds - e.MatchTime.TotalSeconds) < 7 &&
            // That are duplicate
            ev.VictimTribesGuid == e.VictimTribesGuid &&
            ev.KillerTribesGuid == e.KillerTribesGuid &&
            ev.KillType == e.KillType &&
            ev.Weapon == e.Weapon
        )
    )

This returns the following:

Events are grouped as expected, but as the outer query is "per-event", groups are duplicated (see the first two rows).

Is there a way to strip these duplicate groups?

First, all events select themselves. But because of `Math.Abs`, if match A is within 7s of Match B, both match A and B will contain A and B. — Gert Arnold, Mar 12 '17 at 22:04
Yes - I'd like to omit the extraneous "A+B" results (and A+B+C+... in the case of more rows) — Hayden McAfee, Mar 12 '17 at 23:24
If I remove `Math.Abs` (and specify between 0 and 7 seconds), I can get a list of /only/ the events with more than one corresponding nearby event, but I also want to include 'lone' events in the query. — Hayden McAfee, Mar 12 '17 at 23:37

score 1 · Answer 1 · edited May 23 '17 at 11:53

GroupBy seems to be the appropriate method here. But this not as easy as it looks. It seems a reasonable operation: cluster events by the time they occurred. You only have to define some sort of "equality" so you can group more or less identical events, isn't it? Your chosen definition of equality within the same game is: being less than 7s apart.

But there's a problem similar to what I described here. When 3 events a, b, and c occur at 0s, 5s and 10s, the "7s rule" defines the following "equalities":

a ≈ b
b ≈ c

But a and b are 10s apart, so

a !≈ c

In math terms this means that the equality isn't transitive. Which means that you can't simply group events by the fact that they are less than 7s apart.

I think you need a different approach. You should start counting off of the first-occurring event in a game. All events (in the same game) occurring within 7s after this event should be counted. The first event starting more than 7s later starts a new count. This can be done in a simple foreach.

A complication is that everything I said above also applies to the MatchStartTime condition by which you decide that games are "equal". I think you should try to find a less ambiguous way to establish that fact, for example by assigning a guid to a game instance. Otherwise you should take the same approach as for event "equality".

score 0 · Answer 2 · answered Mar 13 '17 at 01:34

Try to use .Distinct();

Events
    .Select(e => 
        Events.Where(ev =>
            // Same event type
            ev.Discriminator == e.Discriminator &&
            // Same match
            ev.ServerIpAddress == e.ServerIpAddress &&
            SqlMethods.DateDiffSecond(ev.MatchStartTime, e.MatchStartTime) < 30 &&
            // Find nearby events
            Math.Abs(ev.MatchTime.TotalSeconds - e.MatchTime.TotalSeconds) < 7 &&
            // That are duplicate
            ev.VictimTribesGuid == e.VictimTribesGuid &&
            ev.KillerTribesGuid == e.KillerTribesGuid &&
            ev.KillType == e.KillType &&
            ev.Weapon == e.Weapon
        ).Distinct();
    )

I think much better if you could work this at your database Query before doing it in Linq. — Vijunav Vastivch, Mar 13 '17 at 03:36

Omitting duplicate row sets from Linq-to-SQL subquery

2 Answers2