Navigational properties

music-nav.cs

//LinqPad, C# program style, navigational properties
public class Musician {
    public Musician() {
        Songs = new List<Song>();
    }
    public int MusicianId {get; set;}
    public string LastName {get; set;}
    public List<Song> Songs {get; set;}
}

public class Song {
    public Song() {
        Plays=new List<AirTime>();
    }
    public int SongId {get; set;}
    public Musician Musician {get; set;}
    public string Title {get; set;}
    public List<AirTime> Plays {get; set;}
}

public class AirTime {
    public int Id {get; set;}
    public Song Song {get; set;}
    public DateTime? Date {get; set;}
    public int Duration {get; set;}
}
    
void Main()
{
    //build "Database"
    var Musicians = new List<Musician>();
    var musician1 = new Musician {MusicianId=1, LastName="The Artist"}; Musicians.Add(musician1);
    var musician2 = new Musician {MusicianId=2, LastName="Another Artist"}; Musicians.Add(musician2);
    //no songs
    var musician3 = new Musician {MusicianId=3, LastName="A New Artist"}; Musicians.Add(musician3);
    
    var Songs = new List<Song>();
    var song1=new Song {SongId=1, Musician = musician1, Title = "Song 1"}; Songs.Add(song1); musician1.Songs.Add(song1);
    var song2=new Song {SongId=2, Musician = musician1, Title = "Song Number 2"}; Songs.Add(song2); musician1.Songs.Add(song2);
    var song3=new Song {SongId=3, Musician = musician2, Title = "Another Song"};Songs.Add(song3); musician2.Songs.Add(song3);
    //no airtime
    var song4=new Song {SongId=4, Musician = musician2, Title = "Not popular"};Songs.Add(song4); musician2.Songs.Add(song4);
    
    var AirTimes = new List<AirTime>();
    var at1=new AirTime{Id=1, Song=song1, Date = new DateTime(2012,8,1), Duration=118}; AirTimes.Add(at1); song1.Plays.Add(at1);
    var at2=new AirTime{Id=2, Song=song2, Date = new DateTime(2012,8,1), Duration=125}; AirTimes.Add(at2); song2.Plays.Add(at2);
    var at3=new AirTime{Id=3, Song=song3, Date = new DateTime(2012,8,1), Duration=140}; AirTimes.Add(at3); song3.Plays.Add(at3);
    var at4=new AirTime{Id=4, Song=song2, Date = new DateTime(2012,8,1), Duration=120}; AirTimes.Add(at4); song2.Plays.Add(at4);
    var at5=new AirTime{Id=5, Song=song1, Date = new DateTime(2012,8,2), Duration=118}; AirTimes.Add(at5); song1.Plays.Add(at5);
    var at6=new AirTime{Id=6, Song=song1, Date = new DateTime(2012,8,3), Duration=118}; AirTimes.Add(at6); song1.Plays.Add(at6);
    
    //20120801 - 20120802
    var firstDate = new DateTime(2012,8,1);
    var lastDate = firstDate.AddDays(2);


    //starting from AirTimes
    var dump = (
        from a in AirTimes
        where a.Date >= firstDate && a.Date < lastDate
        select new {
            a.Song.Musician.LastName, a.Song.Title, a.Date, a.Duration
        }
    );
    dump.Dump("AirTimes");
    
    var grpTime = (
        from a in AirTimes
        where a.Date >= firstDate && a.Date < lastDate
        group a by new {a.Song.Musician.LastName, a.Song.Title, a.Date} into grp
        select new {
            grp.Key.LastName,
            grp.Key.Title,
            grp.Key.Date,
            Plays = grp.Count(),
            Seconds = grp.Sum(x => x.Duration)
        }
    );
    grpTime.Dump("AirTimes grouping");
    
    //starting from Musicians
    var dumpMus = (
        from m in Musicians
        from s in m.Songs
        from p in s.Plays
        where p.Date >= firstDate && p.Date < lastDate
        select new {
            m.LastName,
            s.Title,
            p.Date,
            p.Duration
        }
    );
    dumpMus.Dump("Musicians");
    
    var grpMus = (
        from m in Musicians
        from s in m.Songs
        from p in s.Plays
        where p.Date >= firstDate && p.Date < lastDate
        group p by new {m.LastName, s.Title, p.Date} into grp
        select new {
            grp.Key.LastName,
            grp.Key.Title,
            grp.Key.Date,
            Plays = grp.Count(),
            Seconds= grp.Sum(x => x.Duration)
        }
    );
    grpMus.Dump("Musicians grouping");

    //Airtimes for musicians
    var grpAir = (
        from a in AirTimes
        where a.Date >= firstDate && a.Date < lastDate
        group a by new {a.Song.Musician, a.Date} into grp
        select new {
            //Musician instead of his LastName for joining. Id would work too
            grp.Key.Musician,
            //grp.Key.Musician.LastName,
            Date=grp.Key.Date,
            Plays = grp.Count(),
            Secs = grp.Sum(x => x.Duration)
        });
    grpAir.Dump("AirTimes");

    var res = (
        from m in Musicians
        join g in grpAir on m equals g.Musician into g2
        from g in g2.DefaultIfEmpty()
        orderby m.LastName
        select new {
            m.LastName,
            Date = (g==null ? null : g.Date),
            Plays = (g==null ? 0 : g.Plays),
            Secs = (g==null ? 0 : g.Secs)
        }
    );
    res.Dump("All musicians and their airtime");
    
}

music-nonav.cs

//LinqPad, C# program style, IDs only
public class Musician {
	public int MusicianId {get; set;}
	public string LastName {get; set;}
}

public class Song {
	public int SongId {get; set;}
	public int MusicianId {get; set;}
	public string Title {get; set;}
}

public class AirTime {
	public int Id {get; set;}
	public int SongId {get; set;}
	public DateTime Date {get; set;}
	public int Duration {get; set;}
}
	
void Main()
{
	var Musicians = new List<Musician>();
	Musicians.Add(new Musician {MusicianId=1, LastName="The Artist"});
	Musicians.Add(new Musician {MusicianId=2, LastName="Another Artist"});
	
	var Songs = new List<Song>();
	Songs.Add(new Song {SongId=1, MusicianId = 1, Title = "Song 1"});
	Songs.Add(new Song {SongId=2, MusicianId = 1, Title = "Song number 2"});
	Songs.Add(new Song {SongId=3, MusicianId = 2, Title = "A Song"});
	
	var AirTimes = new List<AirTime>();
	AirTimes.Add(new AirTime{Id=1, SongId=1, Date = new DateTime(2012,8,1), Duration=118});
	AirTimes.Add(new AirTime{Id=2, SongId=2, Date = new DateTime(2012,8,1), Duration=125});
	AirTimes.Add(new AirTime{Id=3, SongId=3, Date = new DateTime(2012,8,1), Duration=140});
	AirTimes.Add(new AirTime{Id=4, SongId=2, Date = new DateTime(2012,8,1), Duration=120});
	AirTimes.Add(new AirTime{Id=5, SongId=1, Date = new DateTime(2012,8,2), Duration=118});
	
	var firstDate = new DateTime(2012,8,1);
	var lastDate = firstDate.AddDays(1);

	var dump = (
		from a in AirTimes
		join s in Songs on a.SongId equals s.SongId
		join m in Musicians on s.MusicianId equals m.MusicianId
		where a.Date >= firstDate && a.Date < lastDate
		select new {
			m.LastName, s.Title, a.Date, a.Duration
		}
	);
	dump.Dump();
	
	var tot = (
		from a in AirTimes
		join s in Songs on a.SongId equals s.SongId
		join m in Musicians on s.MusicianId equals m.MusicianId
		where a.Date >= firstDate && a.Date < lastDate
		group a by new {m.LastName, s.Title, a.Date} into grp
		select new {
			grp.Key.LastName,
			grp.Key.Title,
			grp.Key.Date,
			Plays = grp.Count(),
			Seconds = grp.Sum(x => x.Duration)
		}
	);
	tot.Dump();
}