Linq to SQL - return record details from two tables

Question

I'm having some trouble with a linq query. It's a simple problem, but I'm not sure what the best way to approach it would be. I have two tables (I'm only showing relevant fields) called Artist and Song:

ARTIST - int ArtistID (pk)
         varchar Name

SONG - int SongID (pk)
       uniqueidentifier UserID (To lookup songs the user has added to their account)
       int ArtistID (foreign key to ArtistID in Artist table)
       varchar songName

What I'd like to do, is create a method in my DAL which retrieves all of the user's songs (given the UserID) and display it in a repeater which combines the Artist name with the song (so the final output would be ArtistName - songName). I've created this query:

            var query = from p2 in db.SONG
                        where p2.UserID == givenUserID
                        join p in db.ARTIST
                        on p2.ArtistID equals p.ArtistID
                        select new ArtistSongStruct
                        {
                            ArtistName = p.Name,
                            songName = p2.songName

                        };   

            return query;

This works to the degree that I can debug in my business layer and read the correct values. However, ArtistSongStruct is a custom struct I created in the DAL just for this method. I'm not sure if that is a good way of doing things. Secondly, even if this is returned to the business layer, I can't get the repeater to show the actual values. It displays an error claiming there is no property with the name ArtistName/songName.

What would be the best way to return the artist and their song based on the ArtistID? Thanks for any suggestions. I'm quite new to L2S, so this is slightly confusing.

Answer 1

I can use IList as return type of the method and on the calling side I use dynamic type, if can for loop the result and it just works like magic.

    public static IList GetHistory(DateTime time, string contact = "")
    {
        using (Entities entities = new Entities())
        {
            //....your code
            return convs.ToList();
        }
    }

Now you can call it like:

        dynamic sa = DataStore.GetHistory(DateTime.Now, "satish");
        foreach (var a in sa)
        {
            Console.WriteLine(a.ConvText);
        }

Answer 2

You shouldn't return the query, you should close the connection to the database as soon as possible and return a ToList() result of your query.

using(DataContext db = new DataContext())
{
        var query = from p2 in db.SONG
                    where p2.UserID == givenUserID
                    join p in db.ARTIST
                    on p2.ArtistID equals p.ArtistID
                    select new ArtistSongStruct
                    {
                        ArtistName = p.Name,
                        songName = p2.songName

                    };   

        return query.ToList();
}

EDIT: I am not sure why you are using a struct consider using a class, but you real problem is because you have public fields, not public properties. Reader only binds to public properties, hence the error you're getting. Change your struct to.

    public struct ArtistSongStruct 
    {  
       public string ArtistName {get; set;}
       public string songName { get; set; } 
    }

Answer 3

Any reason you can't return an IEnumerable of Strings as opposed to a custom struct?

var query = from p2 in db.SONG
                    where p2.UserID == givenUserID
                    join p in db.ARTIST
                    on p2.ArtistID equals p.ArtistID
                    select String.Format("{0} - {1}", p.Name, p2.songName)  

        return query.ToList();