//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; }