//public MusicianExtendedDto GetMusicianOnAlbum(int albumId, int musicianId)
//{
// var result = from song in DbContext.media_product
// join recording in DbContext.recording_party on song.recordingid equals recording.recordingid
// join person in DbContext.party_real on recording.partyrealid equals person.id
// join instrument in DbContext.party_instrumenttype on recording.instrumentcode equals instrument.code
// where song.packageid == albumId && person.id == musicianId
// group new { instrument.description_is } by new { song.title, song.id, person.fullname };
// var musician = new MusicianExtendedDto();
// foreach (var group in result)
// {
// var instrumentList = (from instrument in @group
// where instrument.description_is != null
// select instrument.description_is).Distinct().ToList();
// musician.Fullname = group.Key.fullname;
// musician.Songs.Add(group.Key.title, instrumentList);
// }
// return musician;
//}
public ICollection <MusiciansOnSongDto> GetMusiciansOnSong(int albumId, int songId)
{
var result = from song in DbContext.media_product
join recording in DbContext.recording_party on song.recordingid equals recording.recordingid
join person in DbContext.party_real on recording.partyrealid equals person.id
join instrument in DbContext.party_instrumenttype on recording.instrumentcode equals instrument.code into instrumentCheck
from subinstruments in instrumentCheck.DefaultIfEmpty()
join role in DbContext.party_partyroletype on recording.rolecode equals role.rolecode
where song.packageid == albumId && song.id == songId && !person.isdeleted
group new { song, recording, person, subinstruments, role } by new { person.id, recordingId = recording.id, person.fullname };
var allSongs = new List <MusiciansOnSongDto>();
foreach (var group in result)
{
var credits = new List <MusicianCreditsDto>();
foreach (var e in group)
{
var musicianCredits = new MusicianCreditsDto();
var registration = new RegistrationDto();
SetAllRegistrationFields(registration, e.recording.comment, e.recording.updatedon, e.recording.updatedby, e.recording.createdon, e.recording.createdby);
SetAllMusicianFields(musicianCredits, registration, e.recording.id, albumId, null, e.person.fullname, e.person.id, e.subinstruments == null ? "" : e.subinstruments.description_is, e.subinstruments == null ? "" : e.subinstruments.code, e.role.rolename_is, e.recording.rolecode);
credits.Add(musicianCredits);
}
var musician = new MusiciansOnSongDto(group.Key.id, group.Key.recordingId, group.Key.fullname, null, null, credits);
allSongs.Add(musician);
}
return(allSongs);
}
private static void SetAllMusicianFields(MusicianCreditsDto musicianCredits, RegistrationDto registration, int creditId, int albumId, string nickName,
string fullName, int personId, string instrument, string instrumentCode, string roleName, string roleCode)
{
musicianCredits.Registration = registration;
musicianCredits.CreditId = creditId;
musicianCredits.AlbumId = albumId;
musicianCredits.ArtistNickName = nickName;
musicianCredits.FullName = fullName;
musicianCredits.PersonId = personId;
musicianCredits.InstrumentName = instrument;
musicianCredits.RoleNameIs = roleName;
musicianCredits.RoleCode = roleCode;
musicianCredits.InstrumentCode = instrumentCode;
}
