CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sql
Ben_G
Ben_G

Reputation: 826

Trouble with a "simple" linq query

I have data in two tables - an Albums table and a Tracks table. There is a one-to-many relationship between the two and the database structure mirrors my classes below. I'm trying to get an album and it's associated tracks based on the artist name (and I know that the data isn't normalized correctly - there should be an Artist entity, but my source data doesn't support that!)

public class Album
{
    public string ArtistName { get; set;}  
    public string AlbumId { get; set; }
    public string AlbumName { get; set; }
    public int TrackCount { get; set; }
    public string Year { get; set; }
    public string Genre { get; set; }
    public string CoverArt { get; set; }
    public string Biography { get; set; }

    // Navigation properties
    public ICollection<Track> Tracks { get; set; }
}
public class Track
{
    public string TrackId { get; set; }
    public int TrackNumber { get; set; }
    public string TrackName { get; set; }
    public string AlbumId { get; set; }
}

My query to get a list of matching albums works fine:

var albums = DataContext.Albums.Where(a => a.ArtistName == artistName  && a.AlbumName == album).ToList();

but the following statement doesn't work:

var tracks = DataContext.Tracks.Where(a => a.AlbumId == albums.Select(al => al.AlbumId).SingleOrDefault());

After it runs the tracks variable contains:

{System.Data.Entity.Infrastructure.DbQuery<MusicApp.Model.Track>}   System.Linq.IQueryable<MusicApp.Model.Track> {System.Data.Entity.Infrastructure.DbQuery<MusicApp.Model.Track>}

I've tried appending .ToList() to my Linq statement and that just returns an error:

Additional information: Unable to create a constant value of type 'MusicApp.Model.Album'. Only primitive types or enumeration types are supported in this context.

I'm sure that the solution is really simple, but I'm new to Linq and just can't figure this one out. Any help would be much appreciated!

Upvotes: 1

Views: 680

Answers (5)

Aron
Aron

Reputation: 15772

Unnecessary joins that everyone else are making, are a personal gripe of mine, as they make code hard to follow.

var query = from t in DataContext.Tracks
            where t.Artists.Any(a => a.ArtistName == "artistName" 
                         && a.AlbumName == "album")
            select t;

Upvotes: 0

Girish Sakhare
Girish Sakhare

Reputation: 763

var albums = DataContext.Albums.Where(a => a.ArtistName == artistName  && a.AlbumName == album).ToList();
List<string> albumIds = albums.Select(s => s.AlbumId).Distinct().ToList();

var tracks = DataContext.Tracks.Where(w => albumIds.Contains(w.AlbumId)).ToList();

Upvotes: 1

Neil Smith
Neil Smith

Reputation: 2565

You can include the navigation properties you want:

var albumsWithTheirTracks = DataContext.Albums
                .Where(a => a.ArtistName == artistName)
                .Include("Tracks")
                .ToList();

Doing this will populate each album's tracks collection with the related entities.

Your other option is to mark your Tracks navigation property as virtual in your Album model:

public virtual ICollection<Track> ...

Marking a nav property as virtual enables lazy loading and will bring along the related entities with your original query for album. If you don't choose to lazy load, you can use the Include method to specify which navigation properties you want to get along with the original album you're asking for.

Upvotes: 1

Dharmesh Tailor
Dharmesh Tailor

Reputation: 372

try this:

var query = from t in DataContext.Tracks
            join a in DataContext.Albums on t.AlbumId equals a.AlbumId
            where a.ArtistName == "artistName" && a.AlbumName == "album"
            select t;

Upvotes: 0

eKek0
eKek0

Reputation: 23289

If you already have the album's data, why don't you try:

var tracks = from t in DataContext.Tracks
             joins a in albums on a.AlbumId equals t.AlbumId
             select t;

Upvotes: 0

Related Questions