Linq-to-SQL select many columns

Question

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.

It looks like you are missing some JOINs. A good resource I've found is http://msdn.microsoft.com/en-us/vstudio/bb688085 There you will find a few options under JOIN — ToddBFisher
– ToddBFisher, Commented Nov 28, 2011 at 19:06
SongAlbumDBDataContext sa = new SongAlbumDBDataContext(connStr); — Mimosa
– Mimosa, Commented Nov 28, 2011 at 19:11

Anders Abel · Accepted Answer · 2011-11-28 19:32:45Z

2

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

edited Nov 28, 2011 at 19:32

answered Nov 28, 2011 at 19:09

Anders Abel

69.6k18 gold badges154 silver badges221 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mimosa Over a year ago

can't implicitly convert type 'System.Collections.Genric.List<int>' to int[]

Anders Abel Over a year ago

If this answer is the one that solved your problem, you should mark it as accepted by clicking the check mark.

Hanlet Escaño · Accepted Answer · 2011-11-28 19:05:16Z

0

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!

answered Nov 28, 2011 at 19:05

Hanlet Escaño

17.4k10 gold badges54 silver badges76 bronze badges

Comments

Daniel Moses · Accepted Answer · 2011-11-28 19:06:30Z

0

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

answered Nov 28, 2011 at 19:06

Daniel Moses

5,86828 silver badges39 bronze badges

Collectives™ on Stack Overflow

Linq-to-SQL select many columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related