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