samedi 30 janvier 2016

EF6 is using the wrong key when eager loading

I have successfully set up EF6 on Chinook database using SqlLite .NET provider.

Now, Playlists and Tracks is a many-many relationship mapped through the junction table PlaylistTracks.

The following query generates the wrong SQL statement where Track is mapped to PlaylistId (It should be TrackId) on the junction table.

var result = context.Playlists
    .Where(p => p.Name == "Brazilian Music")
    .SelectMany(pt => pt.PlaylistTracks)
    .Include(pt => pt.Track).OrderBy(pt => pt.TrackId);

SQL trace:

SELECT 
[Extent1].[PlaylistId] AS [PlaylistId], 
[Extent2].[PlaylistId] AS [PlaylistId1], 
[Extent2].[TrackId] AS [TrackId], 
[Extent3].[TrackId] AS [TrackId1], 
[Extent3].[Name] AS [Name], 
[Extent3].[AlbumId] AS [AlbumId], 
[Extent3].[MediaTypeId] AS [MediaTypeId], 
[Extent3].[GenreId] AS [GenreId], 
[Extent3].[Composer] AS [Composer], 
[Extent3].[Milliseconds] AS [Milliseconds], 
[Extent3].[Bytes] AS [Bytes], 
[Extent3].[UnitPrice] AS [UnitPrice]
FROM   [Playlist] AS [Extent1]
INNER JOIN [PlaylistTrack] AS [Extent2] ON [Extent1].[PlaylistId] = [Extent2].[PlaylistId]
INNER JOIN [Track] AS [Extent3] ON [Extent2].[PlaylistId] = [Extent3].[TrackId]
WHERE 'Brazilian Music' = [Extent1].[Name]
ORDER BY [Extent2].[TrackId] ASC

Somewhere towards the end of the last INNER JOIN I get

[Extent2].[PlaylistId] = [Extent3].[TrackId]

It should be [Extent2].[TrackId] = [Extent3].[TrackId] where [Extent2] is the junction table.

How can I make EF6 map to the correct foreign key?

Appreciate any help.

See my original question Linq query returns the same names even though they should be different

Aucun commentaire:

Enregistrer un commentaire