Linq query returns the same names even though they

Question

I am new to EF6 and I have set up the Chinook database and gotten it working with SqlLite .NET provider on .NET Framework 4.0.

When I execute the following query, it executes without problems but the track names are all the same. They should be different because they have different track IDs and I have looked up those track IDs and they have different names.

var result = context.Playlists.Include(p => p.Tracks)
                .Where(p => p.Name == "Brazilian Music")
                .SelectMany(p => p.Tracks);

foreach(var p in result)
{
  Console.WriteLine(p.Playlist.Name + ", " + p.TrackId + ", " + p.Track.Name);
}

Appreciate any help.

Here is the output of my result:

Console Output:

Answer 1

Your entities are not correct. If Playlist has a TrackId this means that a play list only can have one track. Your output shows that you have many play lists with the same name. Track should have PlaylistId instead but it's hard giving an exact answer without seing the entities.

Answer 2

OK, I tried another approach using joins to the junction table and now it gives the correct result

var result = from p in context.Playlists
             join pt in context.PlaylistTracks on p.PlaylistId equals pt.PlaylistId
             join t in context.Tracks on pt.TrackId equals t.TrackId
             where p.Name == "Brazilian Music"
             select new { PlaylistName = p.Name, TrackId = t.TrackId, TrackName = t.Name };

foreach (var p in result)
{
  Console.WriteLine(p.PlaylistName + ", " + p.TrackId + ", " + p.TrackName);
}

I have checked the junction table POCO and I don't see any problems with it.

I still don't understand why the first approach isn't working.