public void TestQueryDirect()
{
var result = _db.Query <TestClass>("SELECT 3 AS TestLong, 'Hey!' AS TestString, 5.6 AS TestReal").Single();
Assert.Equal(3, result.TestLong);
Assert.Equal("Hey!", result.TestString);
Assert.Equal(5.6, result.TestReal);
}
public void TestGroupBy()
{
var expectedResult = _db.Query <int?>("SELECT AlbumId FROM Track GROUP BY AlbumId");
var actualResult = _db.Tracks.GroupBy(t => t.AlbumId);
foreach (var(expected, item) in expectedResult.Zip(actualResult, (e, a) => (e, a)))
{
Assert.Equal(expected, item.Key);
var expectedSubResult = _db.Query <int>("SELECT TrackId FROM Track WHERE AlbumId IS ?", item.Key);
foreach (var(subExpected, actual) in expectedSubResult.Zip(item, (e, a) => (e, a)))
{
Assert.Equal(subExpected, actual.TrackId);
}
}
}
public void TestSimpleSubQuery()
{
const string querySQL =
"SELECT p.Name, (SELECT COUNT(*) FROM PlaylistTrack WHERE PlaylistId == p.PlaylistId) AS _count " +
"FROM Playlist p";
var expected = _db.Query(querySQL, row => new
{
Playlist = row.Get <string>("Name"),
TrackCount = row.Get <int>("_count")
});
var actual = _db.Playlists.Select(p => new
{
Playlist = p.Name,
TrackCount = _db.PlaylistTracks.Count(pt => pt.PlaylistId == p.PlaylistId)
});
Assert.Equal(expected, actual);
}
public void TestJoin()
{
const string querySQL = "SELECT Album.AlbumId, Track.TrackId " +
"FROM Track JOIN Album ON Album.AlbumId IS Track.AlbumId";
var expected = _db.Query(querySQL, row => new
{
AlbumId = row.Get <int>("AlbumId"),
TrackId = row.Get <int>("TrackId")
});
var actual = _db.Albums.Join(_db.Tracks, a => a.AlbumId, t => t.AlbumId, (album, track) => new
{
album.AlbumId,
track.TrackId
});
Assert.Equal(expected, actual);
}