CODEWITHSUNDEEP
c#asp.netlinq-to-sql
Mimosa
Mimosa

Reputation: 35

Linq-to-SQL select many columns

I'm using Linq-to-SQL and I just started to learn some of the basics. I have problem with select command many columns in many tables. I give songs which selected into session (contain songid) and display songname, artistname, genrename in datagrid.

But it's not working.

ArrayList SelectedSongs = (ArrayList)Session["SelectedSongs"];

string songIds = "";

foreach (int id in SelectedSongs)
            songIds += id + ", ";

var query = from s in sa.Songs
            from ar in sa.Artists
            from g in sa.Genres
            where s.SongID in (songIds)
            select new { s.SongID, s.SongName, ar.ArtistName, g.GenreName };

dgSongs.DataSource = query;

Can anyone help me solve this problem. Thanks.

Upvotes: 2

Views: 1874

Answers (3)

Anders Abel
Anders Abel

Reputation: 69260

This syntax is not correct Linq:

where s.SongID in (songIds)

The Linq equivalent of SQL's WHERE IN is to use Contains(). You have to turn the statement around and start with the list:

where songIds.Contains(s.SongID)

When using Linq-to-SQL you should always use navigation properties instead of explicit joins. If you have proper foreign keys between your tables those properties will be automatically created. With navigation properties and songIDs changed into an int[] your query should be something like this:

int[] songIDs = ((ArrayList)Session["SelectedSongs"]).OfType<int>().ToArray();

var query = from s in sa.Songs
            where songIDs.Contains(s.SongID)
            select new 
            { 
              s.SongID, 
              s.SongName, 
              s.Artist.ArtistName, 
              s.Genre.GenreName 
            };

Upvotes: 2

Daniel Moses
Daniel Moses

Reputation: 5858

I believe you want songIds to be an int[] instead of a csv of ids.

Upvotes: 0

Hanlet Esca&#241;o
Hanlet Esca&#241;o

Reputation: 17370

Seems like you're trying to Join multiple tables. I would recommend to take a look at the Join section of this page. Good luck!

Upvotes: 0

Related Questions