CODEWITHSUNDEEP
c#linqlinq-to-sqljoin
TTCG
TTCG

Reputation: 9113

Linking Multiple Tables in LINQ to SQL

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: alt text http://a.imageshack.us/img155/8572/13690801.jpg

SingBy is the middle table between Track and Artist.

How could I achieve this?

Upvotes: 2

Views: 14214

Answers (6)

TTCG
TTCG

Reputation: 9113

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.

Upvotes: 2

Chad
Chad

Reputation: 3237

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.

Upvotes: 7

Amy B
Amy B

Reputation: 110211

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;

Upvotes: 0

andy
andy

Reputation: 8885

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.

Upvotes: -2

Fadrian Sudaman
Fadrian Sudaman

Reputation: 6465

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();

Upvotes: 1

Matt Mitchell
Matt Mitchell

Reputation: 41861

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();

Upvotes: 0

Related Questions