Linking Multiple Tables in LINQ to SQL

Question

I would like to get the list of albums (Distinct) which was sung by the artistId=1

I am very new to LINQ to SQL and do not know how to join multiple tables. Please see the database diagram below:

SingBy is the middle table between Track and Artist.

How could I achieve this?

Answer 1

var albums = from singer in artist
      from sb in singby
      from t in track
      from a in album
    where singer.artistId == 1 && 
      sb.artistId == 1 && 
      sb.trackId == t.trackId && 
      a.albumId == track.albumId
    select a;

I'm sure there must be a better way. You should look into creating Navigation Properties on your entities. Navigation Properties are like foreign keys.

Edit - corrected to get albums, not artists.

Answer 2

I would like to get the list of albums (Distinct) which was sung by the artistId=1

DBDataContext = new DBDataContext();
album[] = db.artists.Where(a => a.artistId == 1) /* Your artist */
  .SelectMany(a => a.singbies) /* Check if `singby` converted to `singbies` */
  .Select(sb => sb.track) /* The tracks */
  .Select(t => t.album) /* The albums */
  .GroupBy(al => al.albumId) /* Group by id */ /* "Distinct" for objects */
  .Select(alG => alG.First()) /* Select first of each group */
  .ToArray();

Answer 3

If you have all the foreign key relationship defined, you should be able to issue call like below:

dc.GetTable<Album>().Where(a => a.Track.Singby.ArtistId == 1).ToList();

This is relying on Linq to perform lazy load for Track and Singby automatically when required. Obviously this is not optimal to use when you have a large set of data in the db and performance is critical. You can chain the query with GroupBy or Distinct operation to return only the distinct set such as

dc.GetTable<Album>().Where(a => a.Track.Singby.ArtistId == 1).Distinct().ToList();

Answer 4

List<int> Ids = dc.Albums.Where(a => a.Track.Singby.ArtistId == 1).Select(a=> a.albumId).Distinct().ToList();
List<Album> distinctAlbums = dc.Albums.Where(a => distinctAlbumIds.Contains(a.albumId)).ToList();

Hey TTCG, above is the simplest way to do it. This is because doing a Distinct on a List of objects won't do it based on the albumId.

Either you do it in two steps as above, or, you write your own Album Comparer which specifies uniqueness based on AlbumId and pass it to the Distinct call on a List.

NOTE:

The above will only work if you've defined the constraints in your DBML, but better still in your DB.

For best practices, always define your relationships IN THE DATABASE when using Linq to SQL, as Linq to SQL is not like EF, or NHibernate, in that is does not "abstract" your db, it simply reflects it. It's a tool for Data Driven Design, not Domain Driven, so define the relationships in the db.

Answer 5

IEnumerable<Album> query =
  from album in myDC.Albums
  let artists = 
    from track in album.Tracks
    from singBy in track.SingBys
    select singBy.Artist
  where artists.Any(artist => artist.ArtistId == 1)
  select album;

Answer 6

Now, I wrote the codes like the following and it works.

var albums = (from a in db.artists
                          where a.artistId == 1
                          join sb in db.singbies on a equals sb.artist
                          join t in db.tracks on sb.track equals t
                          join al in db.albums on t.album equals al
                          select al).Distinct();

return albums.ToList() as List<album>;

I tested the Chad's version and it works too. I would like to know which way is better and good for query optimization? Thanks all.