Skip to content

Instantly share code, notes, and snippets.

@chrismcv
Forked from gistlyn/data.cs
Last active May 31, 2017 16:10
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save chrismcv/49b46c5dca699bb1e69f6aed294a832a to your computer and use it in GitHub Desktop.
Save chrismcv/49b46c5dca699bb1e69f6aed294a832a to your computer and use it in GitHub Desktop.
Join with Alias
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;
}
using System;
using System.Linq;
using System.Collections.Generic;
using ServiceStack;
using ServiceStack.Text;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.Sqlite;
using ServiceStack.DataAnnotations;
var dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
var db = CreateArtistAndTrackTablesWithData(dbFactory.OpenDbConnection());
var q = db.From<Track>()
.Join<Artist>((x, y) => x.ArtistId == y.Id)
.OrderByDescending(x => x.Year)
.Take(3);
public class TrackArtist {
public string Name { get; set; }
[Alias("Artist.Name")]
public string ArtistIsCalled { get; set; }
public string Album { get; set; }
}
public class TrackArtistWorks {
public string Name { get; set; }
public string ArtistName{ get; set; }
public string Album { get; set; }
}
var captured = new CaptureSqlFilter();
var doesntWork = db.Select<TrackArtist>(q);
var doesWork = db.Select<TrackArtistWorks>(q);
<?xml version="1.0" encoding="utf-8"?>
<packages>
<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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment