using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using System.Threading.Tasks;

​

public static class IEnumerableExtensions

{

    public static IEnumerable<T3> Crosswise<T1, T2, T3>(this IEnumerable<T1> first, IEnumerable<T2> second, Func<T1, T2, T3> func)

  {

    return first.SelectMany(f => second.Select(s => func(f, s)));

  }

​

  public static IEnumerable<Tuple<T1, T2>> Crosswise<T1, T2>(this IEnumerable<T1> first, IEnumerable<T2> second)

  {

    return Crosswise(first, second, Tuple.Create);

  }

}

​

public class Coordinate

{

    public Decimal Latitude { get; }

    public Decimal Longitude { get; }

    public double? Accuracy { get; }

    public double? Altitude { get; }

    public double? AltitudeAccuracy { get; }

    public double? Heading { get; }

    public double? Speed { get; }

​

    public Coordinate(decimal lat, decimal lng, double? acc, double? alt, double? altAcc, double? heading, double? speed) {

        Latitude = lat;

        Longitude = lng;

        Accuracy = acc;

        Altitude = alt;

        AltitudeAccuracy = altAcc;

        Heading = heading;

        Speed = speed;

    }

}

​

public class LocationObservation

{

    public Guid Who { get; }

    public Coordinate Coordinate { get; }

    public DateTimeOffset Time { get; }

​

    public decimal Latitude => Coordinate.Latitude;

    public decimal Longitude => Coordinate.Longitude;

    public double? Accuracy => Coordinate.Accuracy;

    public double? Altitude => Coordinate.Altitude;

    public double? AltitudeAccuracy => Coordinate.AltitudeAccuracy;

    public double? Heading => Coordinate.Heading;

    public double? Speed => Coordinate.Speed;

    public LocationObservation(Guid who, Coordinate coordinate, DateTimeOffset time) {

        Who = who;

        Coordinate = coordinate;

        Time = time;

    }

}

​

public class Program

{

  private static readonly TimeSpan Threshold = new TimeSpan(0, 15, 0);

​

    public static void Main()

    {

    Test().Wait();

    }

  public static async Task Test()

  {

    var who = Guid.NewGuid();

​

    Coordinate[] coordinates =

    {

      new Coordinate(43m, -72m, null, null, null, null, null),

      new Coordinate(43.000003m, -72m, null, null, null, null, null),

      new Coordinate(43m, -71m, null, null, null, null, null),

      new Coordinate(43.000002m, -71m, null, null, null, null, null),

      new Coordinate(42m, -72m, null, null, null, null, null),

      new Coordinate(42.000001m, -72m, null, null, null, null, null),

    };

    DateTimeOffset[] times =

    {

      new DateTime(2019, 04, 11, 15, 30, 0, DateTimeKind.Utc),

      new DateTime(2019, 04, 11, 15, 31, 0, DateTimeKind.Utc),

      new DateTime(2019, 04, 11, 15, 29, 0, DateTimeKind.Utc),

      new DateTime(2019, 04, 11, 15, 33, 0, DateTimeKind.Utc),

    };

​

    var instants = coordinates.Crosswise(times, (loc, time) => new LocationObservation(who, loc, time)).ToArray();

    var rand = new Random();

    var allInstants = Enumerable.Range(0, 20)

      .SelectMany(_ => instants)

      .GroupBy(x => rand.Next(0, 20))

      .Select(g => g.ToArray())

      .ToArray();

​

    foreach (var batch in allInstants)

    {

      await Task.WhenAll(batch.Select(async el => await CreateOrUpdateAll(el)));

      await Task.Delay(1000); // Don't _totally_ slam the production server...

    }

  }

​

    public static async Task<bool> CreateOrUpdateAll(LocationObservation observation)

    {

      const string SQL =

@"BEGIN TRAN;

​

  UPDATE Position WITH(SERIALIZABLE)

  SET StartTime = CASE

      WHEN StartTime > @StartTime THEN @StartTime

      ELSE StartTime

    END,

    EndTime = CASE

      WHEN EndTime < @EndTime THEN @EndTime

      ELSE EndTime

    END,

    UpdatedUtc = GETUTCDATE()

  WHERE Who = @Who

    AND (

        StartTime BETWEEN @RangeStart and @RangeEnd

        or

        EndTime BETWEEN @RangeStart and @RangeEnd

    )

    AND cast(Latitude as decimal(8,5)) = cast(@Latitude as decimal(8, 5))

    AND cast(Longitude as decimal(8,5)) = cast(@Longitude as decimal(8, 5))

    AND (Accuracy = @Accuracy or COALESCE(Accuracy, @Accuracy) is NULL)

    AND (Altitude = @Altitude or COALESCE(Altitude, @Altitude) is NULL)

    AND (AltitudeAccuracy = @AltitudeAccuracy or COALESCE(AltitudeAccuracy, @AltitudeAccuracy) is NULL)

    AND (Heading = @Heading or COALESCE(Heading, @Heading) is NULL)

    AND (Speed = @Speed or COALESCE(Speed, @Speed) is NULL);

​

  IF @@ROWCOUNT = 0

  BEGIN

    INSERT Position(UUID, Who, StartTime, EndTime, Latitude, Longitude, Accuracy, Altitude, AltitudeAccuracy, Heading, Speed, CreatedUtc, UpdatedUtc)

            VALUES (NEWID(), @Who, @StartTime, @EndTime, @Latitude, @Longitude, @Accuracy, @Altitude, @AltitudeAccuracy, @Heading, @Speed, GETUTCDATE(), GETUTCDATE())

  END

COMMIT TRAN";

        using (var conn = GetConnection())

        {

            using (var cmd = new SqlCommand(SQL, conn))

            {

                try

                {

                    cmd.Parameters.Add(new SqlParameter("Who", observation.Who));

                    cmd.Parameters.Add(new SqlParameter("Latitude", observation.Latitude));

                    cmd.Parameters.Add(new SqlParameter("Longitude", observation.Longitude));

                    cmd.Parameters.Add(new SqlParameter("StartTime", observation.Time.UtcDateTime));

                    cmd.Parameters.Add(new SqlParameter("EndTime", observation.Time.UtcDateTime));

                    cmd.Parameters.Add(new SqlParameter("Accuracy", observation.Accuracy));

                    cmd.Parameters.Add(new SqlParameter("Altitude", observation.Altitude));

                    cmd.Parameters.Add(new SqlParameter("AltitudeAccuracy", observation.AltitudeAccuracy));

                    cmd.Parameters.Add(new SqlParameter("Heading", observation.Heading));

                    cmd.Parameters.Add(new SqlParameter("Speed", observation.Speed));

                    cmd.Parameters.Add(new SqlParameter("RangeStart", observation.Time.Subtract(Threshold).UtcDateTime));

                    cmd.Parameters.Add(new SqlParameter("RangeEnd", observation.Time.Add(Threshold).UtcDateTime));

​

                    await conn.OpenAsync();

                    await cmd.ExecuteNonQueryAsync();

                    return true;

                }

                catch (SqlException e)

                {

                    Console.Error.WriteLine("Error Updating Access Log:");

                    Console.Error.WriteLine(e.Message);

​

                    return false;

                }

​

                return true;

            }

        }

    }

  private static SqlConnection GetConnection() {

    throw new NotImplementedException("You can hook in your db here");

  }

}

​