CODEWITHSUNDEEP
c#linq
user687554
user687554

Reputation: 11151

Performing a JOIN in LINQ with a strongly-typed object

I have two objects that have been generated with an entity data model. The objects look like the following:

public class Song
{
  public int ID { get; set; }
  public string Title { get; set; }
  public double Duration { get; set; }
}

public class AlbumSongLookup
{
  public int ID { get; set; }
  public int SongID { get; set; }
  public int AlbumID { get; set; }
}

I need to get the Song objects for an Album using LINQ. I have the Album ID. Currently, I'm trying:

int albumID = GetAlbumID();
var results = from lookup in context.AlbumSongLookups
              where lookup.AlbumID=albumID
              select lookup;

I understand that I need to do a join. But what I'm not sure about is, how do I get the results to be Song objects with this LINQ query?

Thanks!

Upvotes: 1

Views: 721

Answers (4)

Chris McAtackney
Chris McAtackney

Reputation: 5242

Does this query return what you're expecting?

var results = from lookup in context.AlbumSongLookups
              join song in context.Songs on lookup.SongID equals song.ID
              where lookup.AlbumID == albumID
              select song;

I'm assuming the existence of context.Songs here.

Upvotes: 1

Chris Snowden
Chris Snowden

Reputation: 5002

Something like this should work:

int albumID = GetAlbumID();
var songIDs = from lookup in contenxt.AlbumSongLookups
              where lookup.AlbumID == albumID
              select lookup.SongID;
var results = from song in context.Songs
              where song.SongID in SongIDs
              select song;

Upvotes: 0

AbdouMoumen
AbdouMoumen

Reputation: 3854

You could add a "Navigation Property" to your "Song" Entity, this way, you'll be able to directly access the corresponding Songs of a particular album. Entity framework will do the heavy lifting for you.

In your case, it returns a collection of lookups, what you need to do is look for the Song objects corresponding to them. this can be done using:

int albumID = GetAlbumID();
var results = from lookup in context.AlbumSongLookups
              where lookup.AlbumID=albumID
              select context.Songs.SingleOrDefault(s=>s.SongID==lookup.SongID);

Upvotes: 0

paulslater19
paulslater19

Reputation: 5917

does this not work?

from lookup in context.AlbumSongLookups
from songs in context.Song
where lookup.SongID == songs.ID && lookup.SongID == albumID
select songs

Upvotes: 0

Related Questions