public List <SongClass> SearchResult(SongClass songclass, bool artistname, bool songname, bool albumname, bool bandname, bool studioname, bool genrename, bool composername, bool producername) { MyanmarMusicDataContext db = new MyanmarMusicDataContext(); List <SongClass> mylist = new List <SongClass>(); Song tblsong = new Song(); IQueryable <Song> temptable = db.Songs; if (artistname == true) { // temptable=from s in temptable // join ar in db.Artist_Songs // on s.SongID equals ar.SongID // join a in db.Artists // on ar.ArtistID equals a.ArtistID // where a.ArtistMyanmarName.Contains(songclass.ArtistName) || a.ArtistEnglishName.Contains(songclass.ArtistName) || a.ArtistEnglishName.StartsWith(songclass.ArtistName) || a.ArtistEnglishName.StartsWith(songclass.ArtistName) // select s; temptable = from s in temptable join a in db.Albums on s.AlbumID equals a.AlbumID join studio in db.Studios on s.StudioID equals studio.StudioID join band in db.Bands on s.BandID equals band.BandID join p in db.Producers on s.ProducerID equals p.ProducerID join artists in db.Artist_Songs on s.SongID equals artists.SongID join artist in db.Artists on artists.ArtistID equals artist.ArtistID join cpos in db.Composer_Songs on s.SongID equals cpos.SongID join composer in db.Composers on cpos.ComposerID equals composer.ComposerID where artist.ArtistMyanmarName.Contains(songclass.ArtistName) || artist.ArtistEnglishName.Contains(songclass.ArtistName) || artist.ArtistEnglishName.StartsWith(songclass.ArtistName) || artist.ArtistEnglishName.StartsWith(songclass.ArtistName) select s; } if (songname == true) { temptable = from s in temptable where s.SongTitle.Contains(songclass.SongTitle) || s.SongTitle.StartsWith(songclass.SongTitle) select s; } if (albumname == true) { temptable = from s in temptable join l in db.Albums on s.AlbumID equals l.AlbumID where l.AlbumTitle.Contains(songclass.AlbumName) || l.AlbumTitle.StartsWith(songclass.AlbumName) select s; } if (bandname == true) { temptable = from s in temptable join l in db.Bands on s.BandID equals l.BandID where l.BandName.Contains(songclass.BandName) || l.BandName.StartsWith(songclass.BandName) select s; } if (studioname == true) { temptable = from s in temptable join l in db.Studios on s.StudioID equals l.StudioID where l.StudioName.Contains(songclass.StudioName) || l.StudioName.StartsWith(songclass.StudioName) select s; } if (genrename == true) { temptable = from s in temptable join l in db.Genres on s.GenresID equals l.GenresID where l.GenresName.Contains(songclass.GenresName) || l.GenresName.StartsWith(songclass.GenresName) select s; } if (composername == true) { temptable = from s in temptable join ar in db.Composer_Songs on s.SongID equals ar.SongID join a in db.Composers on ar.ComposerID equals a.ComposerID where a.ComposerName.Contains(songclass.ComposerName) || a.ComposerName.StartsWith(songclass.ComposerName) select s; } if (producername == true) { temptable = from s in temptable join p in db.Producers on s.ProducerID equals p.ProducerID where p.ProducerName.Contains(songclass.ProducerName) || p.ProducerName.StartsWith(songclass.ProducerName) select s; } var total = from t in temptable group t by new { t.SongTitle, t.SongID, t.AlbumID, t.BandID, t.GenresID, t.ProducerID, t.StudioID, t.MusicFile, t.DemoFile } into g select new { g.Key.AlbumID, g.Key.BandID, g.Key.GenresID, g.Key.ProducerID, g.Key.SongID, g.Key.SongTitle, g.Key.StudioID, g.Key.MusicFile, g.Key.DemoFile }; foreach (var t in total) { SongClass sclass = new SongClass(); SongRepository songrep = new SongRepository(); sclass.StudioName = songrep.GetStudioBySongID(t.SongID); sclass.SongID = t.SongID; sclass.SongTitle = t.SongTitle; sclass.AlbumID = t.AlbumID; sclass.SongFile = t.MusicFile; sclass.SongDemoFile = t.DemoFile; sclass.BandID = t.BandID; sclass.StudioID = t.StudioID; sclass.AlbumName = songrep.GetAlbumBySongID(t.SongID); sclass.ArtistNameMyanmar = songrep.GetArtistNameMyanmarBySongID(t.SongID); sclass.BandName = songrep.GetBandBySongID(t.SongID); sclass.ComposerName = songrep.GetComposerBySongID(t.SongID); sclass.ProducerName = songrep.GetProducerBySongID(t.SongID); mylist.Add(sclass); } return(mylist); }