Skip to content

Instantly share code, notes, and snippets.

@mythz

mythz/data.cs

Forked from gistlyn/data.cs
Last active November 18, 2016 15:17
Show Gist options
  • Save mythz/ed29fb09e39b7dde423f18f1471eadf7 to your computer and use it in GitHub Desktop.
Save mythz/ed29fb09e39b7dde423f18f1471eadf7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mythz revised this gist Nov 18, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -7,9 +7,9 @@
    using ServiceStack.OrmLite;
    using ServiceStack.OrmLite.Sqlite;

    LogManager.LogFactory = new ConsoleLogFactory();
    var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
    var db = CreateArtistAndTrackTablesWithData(dbFactory.OpenDbConnection());
    LogManager.LogFactory = new ConsoleLogFactory();

    var oldestTracks = db.Select(db.From<Track>()
    .Where(x => Sql.In(x.Year, db.From<Track>().Select(y => Sql.Min(y.Year)))));
  2. mythz revised this gist Nov 18, 2016. 1 changed file with 2 additions and 29 deletions.
    31 changes: 2 additions & 29 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -2,27 +2,15 @@
    using System.Linq;
    using System.Collections.Generic;
    using ServiceStack;
    using ServiceStack.Logging;
    using ServiceStack.Text;
    using ServiceStack.OrmLite;
    using ServiceStack.OrmLite.Sqlite;

    LogManager.LogFactory = new ConsoleLogFactory();
    var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
    var db = CreateArtistAndTrackTablesWithData(dbFactory.OpenDbConnection());

    var q = db.From<Track>()
    .Join<Artist>() //Uses implict reference convention
    .Where<Artist>(x => x.Name == "Nirvana");
    var implicitJoin = db.Select(q);
    "Nirvana Tracks (implicit join): {0}".Print(implicitJoin.Dump());

    var explicitJoin = db.Select(db.From<Track>()
    .Join<Artist>((track,artist) => track.ArtistId == artist.Id)
    .Where<Artist>(x => x.Name == "Nirvana"));
    "Nirvana Tracks (explicit join): {0}".Print(explicitJoin.Dump());

    var nirvanaWithRefs = db.LoadSingleById<Artist>(explicitJoin[0].ArtistId);
    "Nirvana with References: {0}".Print(nirvanaWithRefs.Dump());

    var oldestTracks = db.Select(db.From<Track>()
    .Where(x => Sql.In(x.Year, db.From<Track>().Select(y => Sql.Min(y.Year)))));
    "Oldest Tracks: {0}".Print(oldestTracks.Dump());
    @@ -32,18 +20,3 @@
    .Where(a => oldestTracks.Map(t => t.ArtistId).Contains(a.Id)));
    "Earliest Artists: {0}".Print(earliestArtistsWithRefs.Dump());

    var oldestTracksAndArtistNames = db.Dictionary<string, string>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id))
    .Select<Track,Artist>((t,a) => new { t.Name, Artist = a.Name }));
    "Oldest Track and Artist Names: {0}".Print(oldestTracksAndArtistNames.Dump());

    var oldestTrackAndArtists = db.SelectMulti<Track,Artist>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id)));
    foreach (var tuple in oldestTrackAndArtists)
    {
    Track track = tuple.Item1;
    Artist artist = tuple.Item2;
    "Oldest Track + Artist: {0}".Print(new { track, artist }.Dump());
    }
  3. @gistlyn gistlyn revised this gist Aug 25, 2016. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions packages.config
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,8 @@
    <?xml version="1.0" encoding="utf-8"?>
    <packages>
    <package id="ServiceStack.Text" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.Interfaces" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.Common" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.OrmLite" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.OrmLite.Sqlite.Mono" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.Text" version="4.5.0" targetFramework="net45" />
    <package id="ServiceStack.Interfaces" version="4.5.0" targetFramework="net45" />
    <package id="ServiceStack.Common" version="4.5.0" targetFramework="net45" />
    <package id="ServiceStack.OrmLite" version="4.5.0" targetFramework="net45" />
    <package id="ServiceStack.OrmLite.Sqlite.Mono" version="4.5.0" targetFramework="net45" />
    </packages>
  4. @gistlyn gistlyn revised this gist Aug 9, 2016. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions packages.config
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,8 @@
    <?xml version="1.0" encoding="utf-8"?>
    <packages>
    <package id="ServiceStack.Common" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.Interfaces" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.OrmLite" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.Text" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.OrmLite.Sqlite.Mono" version="4.0.60" targetFramework="net45" />
    </packages>
    <package id="ServiceStack.Text" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.Interfaces" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.Common" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.OrmLite" version="4.0.62" targetFramework="net45" />
    <package id="ServiceStack.OrmLite.Sqlite.Mono" version="4.0.62" targetFramework="net45" />
    </packages>
  5. @gistlyn gistlyn revised this gist Jul 27, 2016. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -41,10 +41,9 @@
    var oldestTrackAndArtists = db.SelectMulti<Track,Artist>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id)));
    "Oldest Track and Artists:".Print();
    foreach (var tuple in oldestTrackAndArtists)
    {
    Track track = tuple.Item1;
    Artist artist = tuple.Item2;
    "Track/Artist: {0}".Print(new { track, artist }.Dump());
    "Oldest Track + Artist: {0}".Print(new { track, artist }.Dump());
    }
  6. @gistlyn gistlyn revised this gist Jul 27, 2016. 1 changed file with 7 additions and 3 deletions.
    10 changes: 7 additions & 3 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -12,7 +12,6 @@
    var q = db.From<Track>()
    .Join<Artist>() //Uses implict reference convention
    .Where<Artist>(x => x.Name == "Nirvana");

    var implicitJoin = db.Select(q);
    "Nirvana Tracks (implicit join): {0}".Print(implicitJoin.Dump());

    @@ -37,10 +36,15 @@
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id))
    .Select<Track,Artist>((t,a) => new { t.Name, Artist = a.Name }));

    "Oldest Track and Artist Names: {0}".Print(oldestTracksAndArtistNames.Dump());

    var oldestTrackAndArtists = db.SelectMulti<Track,Artist>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id)));
    "Oldest Track and Artist: {0}".Print(oldestTrackAndArtists.Dump());
    "Oldest Track and Artists:".Print();
    foreach (var tuple in oldestTrackAndArtists)
    {
    Track track = tuple.Item1;
    Artist artist = tuple.Item2;
    "Track/Artist: {0}".Print(new { track, artist }.Dump());
    }
  7. @gistlyn gistlyn revised this gist Jul 27, 2016. 1 changed file with 6 additions and 10 deletions.
    16 changes: 6 additions & 10 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -33,18 +33,14 @@
    .Where(a => oldestTracks.Map(t => t.ArtistId).Contains(a.Id)));
    "Earliest Artists: {0}".Print(earliestArtistsWithRefs.Dump());

    var oldestTracksAndArtists = db.Dictionary<string, string>(db.From<Track>()
    var oldestTracksAndArtistNames = db.Dictionary<string, string>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id))
    .Select<Track,Artist>((t,a) => new { t.Name, Artist = a.Name }));

    "Oldest Track and Artist Names: {0}".Print(oldestTracksAndArtists.Dump());

    var tracksByYear = db.Dictionary<int, int>(db.From<Track>()
    .Join<Artist>()
    .GroupBy(x => x.Year)
    .OrderBy(x => x.Year)
    .Select(x => new { x.Year, Count = Sql.Count("*") }));

    "No of Tracks in each Year: {0}".Print(tracksByYear.Dump());
    "Oldest Track and Artist Names: {0}".Print(oldestTracksAndArtistNames.Dump());

    var oldestTrackAndArtists = db.SelectMulti<Track,Artist>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id)));
    "Oldest Track and Artist: {0}".Print(oldestTrackAndArtists.Dump());
  8. @gistlyn gistlyn revised this gist Jul 27, 2016. 1 changed file with 7 additions and 5 deletions.
    12 changes: 7 additions & 5 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -14,22 +14,24 @@
    .Where<Artist>(x => x.Name == "Nirvana");

    var implicitJoin = db.Select(q);
    "Nirvana Tracks: {0}".Print(implicitJoin.Dump());
    "Nirvana Tracks (implicit join): {0}".Print(implicitJoin.Dump());

    var explicitJoin = db.Select(db.From<Track>()
    .Join<Artist>((track,artist) => track.ArtistId == artist.Id)
    .Where<Artist>(x => x.Name == "Nirvana"));
    "Nirvana Tracks: {0}".Print(explicitJoin.Dump());
    "Nirvana Tracks (explicit join): {0}".Print(explicitJoin.Dump());

    var nirvanaWithReferences = db.LoadSingleById<Artist>(explicitJoin[0].ArtistId);
    "Nirvana with References: {0}".Print(nirvanaWithReferences.Dump());
    var nirvanaWithRefs = db.LoadSingleById<Artist>(explicitJoin[0].ArtistId);
    "Nirvana with References: {0}".Print(nirvanaWithRefs.Dump());

    var oldestTracks = db.Select(db.From<Track>()
    .Where(x => Sql.In(x.Year, db.From<Track>().Select(y => Sql.Min(y.Year)))));
    "Oldest Tracks: {0}".Print(oldestTracks.Dump());

    var oldestTrackIds = oldestTracks.Map(x => x.Id);
    //var oldestTracksWithReferences =
    var earliestArtistsWithRefs = db.LoadSelect(db.From<Artist>()
    .Where(a => oldestTracks.Map(t => t.ArtistId).Contains(a.Id)));
    "Earliest Artists: {0}".Print(earliestArtistsWithRefs.Dump());

    var oldestTracksAndArtists = db.Dictionary<string, string>(db.From<Track>()
    .Join<Artist>()
  9. @gistlyn gistlyn revised this gist Jul 27, 2016. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -21,12 +21,16 @@
    .Where<Artist>(x => x.Name == "Nirvana"));
    "Nirvana Tracks: {0}".Print(explicitJoin.Dump());

    var nirvanaWithReferences = db.LoadSingleById<Artist>(explicitJoin[0].ArtistId);
    "Nirvana with References: {0}".Print(nirvanaWithReferences.Dump());

    var oldestTracks = db.Select(db.From<Track>()
    .Where(x => Sql.In(x.Year, db.From<Track>().Select(y => Sql.Min(y.Year)))));

    "Oldest Tracks: {0}".Print(oldestTracks.Dump());

    var oldestTrackIds = oldestTracks.Map(x => x.Id);
    //var oldestTracksWithReferences =

    var oldestTracksAndArtists = db.Dictionary<string, string>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id))
  10. @gistlyn gistlyn revised this gist Jul 27, 2016. 1 changed file with 33 additions and 0 deletions.
    33 changes: 33 additions & 0 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -9,3 +9,36 @@
    var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
    var db = CreateArtistAndTrackTablesWithData(dbFactory.OpenDbConnection());

    var q = db.From<Track>()
    .Join<Artist>() //Uses implict reference convention
    .Where<Artist>(x => x.Name == "Nirvana");

    var implicitJoin = db.Select(q);
    "Nirvana Tracks: {0}".Print(implicitJoin.Dump());

    var explicitJoin = db.Select(db.From<Track>()
    .Join<Artist>((track,artist) => track.ArtistId == artist.Id)
    .Where<Artist>(x => x.Name == "Nirvana"));
    "Nirvana Tracks: {0}".Print(explicitJoin.Dump());

    var oldestTracks = db.Select(db.From<Track>()
    .Where(x => Sql.In(x.Year, db.From<Track>().Select(y => Sql.Min(y.Year)))));

    "Oldest Tracks: {0}".Print(oldestTracks.Dump());

    var oldestTrackIds = oldestTracks.Map(x => x.Id);
    var oldestTracksAndArtists = db.Dictionary<string, string>(db.From<Track>()
    .Join<Artist>()
    .Where(x => oldestTrackIds.Contains(x.Id))
    .Select<Track,Artist>((t,a) => new { t.Name, Artist = a.Name }));

    "Oldest Track and Artist Names: {0}".Print(oldestTracksAndArtists.Dump());

    var tracksByYear = db.Dictionary<int, int>(db.From<Track>()
    .Join<Artist>()
    .GroupBy(x => x.Year)
    .OrderBy(x => x.Year)
    .Select(x => new { x.Year, Count = Sql.Count("*") }));

    "No of Tracks in each Year: {0}".Print(tracksByYear.Dump());

  11. @gistlyn gistlyn revised this gist Jul 26, 2016. 1 changed file with 64 additions and 2 deletions.
    66 changes: 64 additions & 2 deletions data.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,65 @@
    // data.cs
    // Created by gistlyn on 2016/07/26
    using System.Collections.Generic;
    using System.Data;
    using ServiceStack;
    using ServiceStack.OrmLite;
    using ServiceStack.DataAnnotations;

    public class Artist
    {
    public int Id { get; set; }
    public string Name { get; set; }

    [Reference]
    public List<Track> Tracks { get; set; }
    public override string ToString() => Name;
    }

    public class Track
    {
    [AutoIncrement]
    public int Id { get; set; }
    public string Name { get; set; }
    public int ArtistId { get; set; }
    public string Album { get; set; }
    public int Year { get; set; }
    public override string ToString() => Name;
    }

    var Artists = new [] {
    new Artist {
    Id = 1, Name = "Faith No More",
    Tracks = new List<Track> {
    new Track { Name = "Everythings Ruined", Album = "Angel Dust", Year = 1992 },
    new Track { Name = "Ashes to Ashes", Album = "Album of the Year", Year = 1997 },
    }
    },
    new Artist {
    Id = 2, Name = "Live",
    Tracks = new List<Track> {
    new Track { Name = "Lightning Crashes", Album = "Throwing Copper", Year = 1994 },
    new Track { Name = "Lakini's Juice", Album = "Secret Samadhi", Year = 1997 },
    }
    },
    new Artist {
    Id = 3, Name = "Nirvana",
    Tracks = new List<Track> {
    new Track { Name = "Smells Like Teen Spirit", Album = "Nevermind", Year = 1991 },
    new Track { Name = "Heart-Shaped Box", Album = "In Utero", Year = 1993 },
    }
    },
    new Artist {
    Id = 4, Name = "Pearl Jam",
    Tracks = new List<Track> {
    new Track { Name = "Alive", Album = "Ten", Year = 1991 },
    new Track { Name = "Daughter", Album = "Vs", Year = 1993 },
    }
    },
    };

    IDbConnection CreateArtistAndTrackTablesWithData(IDbConnection db)
    {
    db.DropAndCreateTable<Artist>();
    db.DropAndCreateTable<Track>();
    Artists.Each(x => db.Save(x, references:true));
    return db;
    }
  12. @gistlyn gistlyn revised this gist Jul 26, 2016. 2 changed files with 4 additions and 1 deletion.
    3 changes: 3 additions & 0 deletions data.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    // data.cs
    // Created by gistlyn on 2016/07/26

    2 changes: 1 addition & 1 deletion main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -7,5 +7,5 @@
    using ServiceStack.OrmLite.Sqlite;

    var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
    var db = dbFactory.OpenDbConnection();
    var db = CreateArtistAndTrackTablesWithData(dbFactory.OpenDbConnection());

  13. @gistlyn gistlyn created this gist Jul 26, 2016.
    11 changes: 11 additions & 0 deletions main.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    using System;
    using System.Linq;
    using System.Collections.Generic;
    using ServiceStack;
    using ServiceStack.Text;
    using ServiceStack.OrmLite;
    using ServiceStack.OrmLite.Sqlite;

    var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
    var db = dbFactory.OpenDbConnection();

    8 changes: 8 additions & 0 deletions packages.config
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,8 @@
    <?xml version="1.0" encoding="utf-8"?>
    <packages>
    <package id="ServiceStack.Common" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.Interfaces" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.OrmLite" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.Text" version="4.0.60" targetFramework="net45" />
    <package id="ServiceStack.OrmLite.Sqlite.Mono" version="4.0.60" targetFramework="net45" />
    </packages>