sqlite_dto_test.cs

[Test]
public async void Sqlite_Orders_And_Returns_DateTimeOffsets_Correctly()
{
    using (var db = DatabaseBuilder.BuildDatabase())
    {
        db.ExecuteSql("CREATE TABLE \"Tests\" (\"Time\" TEXT NOT NULL)");

        var later = DateTimeOffset.Parse("10/23/2014 11:35:18 AM -04:00").ToLocalTime();
        var earlier = later.AddMinutes(-10).ToUniversalTime();
        var ordered = new[] {later, earlier}.OrderBy(x => x);

        foreach (var dto in ordered)
            await db.InsertAsync(new {Time = dto}, "Tests");

        // demonstrate improper ordering
        var badResults = db.QuerySql("SELECT * FROM \"Tests\" ORDER BY \"Time\"")
            .AsEnumerable()
            .Select(x => x["Time"].ConvertTo<DateTimeOffset>())
            .ToArray();

        CollectionAssert.AreEqual(ordered.OrderByDescending(x=>x),badResults);

        // must use sqlite datetime() function for ordering
        // because sqlite stores the values as strings
        // and the ordering of strings is lexicographical
        var results = db.QuerySql("SELECT * FROM \"Tests\" ORDER BY datetime(\"Time\")")
            .AsEnumerable()
            .Select(x => x["Time"].ConvertTo<DateTimeOffset>())
            .ToArray();

        CollectionAssert.AreEqual(ordered,results);

    }
}