Eager Loading a SimpleRecord - Different results depending on query command used

[hmobius]

I've been experimenting with eager-loading joins and noticed some difference in what happens when eager loading to a SimpleRecord. I'm using two tables. The Artists table has a 1:n foreign key relationship with the Albums table. Both use the artistId column. it is PK in the Artist table.

If I call db.Artists.FindAllByArtistId(22).WithAlbums().FirstOrDefault(); all works as expected. SimpleRecord returned with 14 items in Albums SimpleList.

If I call db.Artists.Get(22).WithAlbums(); I get an Exception.

If I call db.Artists.WithAlbums().Get(22); A SimpleRecord is returned but with only 1 item in the Albums SimpleList.

The SQL sent over is markedly different.

db.Artists.FindAllByArtistId(22).WithAlbums().FirstOrDefault(); sends the following

select [dbo].[Artists].[ArtistId],[dbo].[Artists].[Name], [dbo].[Albums].[AlbumId] AS [withnAlbumsAlbumId], [dbo].[Albums].[ArtistId] AS [withnAlbumsArtistId], [dbo].[Albums].[Title] AS [withnAlbums__Title] from [dbo].[Artists] LEFT JOIN [dbo].[Albums] ON ([dbo].[Artists].[ArtistId] = [dbo].[Albums].[ArtistId]) WHERE [dbo].[Artists].[ArtistId] = @p1 @p1 (Int32) = 22

db.Artists.WithAlbums().Get(22) sends the following

WITH Data AS ( SELECT [dbo].[Artists].[ArtistId],[dbo].[Artists].[Name], [dbo].[Albums].[AlbumId] AS [withnAlbumsAlbumId], [dbo].[Albums].[ArtistId] AS [withnAlbumsArtistId], [dbo].[Albums].[Title] AS [withnAlbumsTitle], ROWNUMBER() OVER(ORDER BY [dbo].[Artists].[ArtistId]) AS [#] from [dbo].[Artists] LEFT JOIN [dbo].[Albums] ON ([dbo].[Artists].[ArtistId] = [dbo].[Albums].[ArtistId]) WHERE [dbo].[Artists].[ArtistId] = @p1 ) SELECT [ArtistId],[Name], [withnAlbumsAlbumId], [withnAlbumsArtistId], [withnAlbumsTitle] FROM Data WHERE [#_] BETWEEN 1 AND 1 @p1 (Int32) = 22

I know that the recommended way to get a SimpleRecord is to FindAllBy().FirstOrDefault() , but Get and Find aren't deprecated and they both produce this wrong result.

[markrendle]

This is fixed now. The code that fixed it made my brain cry on the inside of my head. I hope you're happy.

[andagr]

The documentation (under Eager Evaluation) still mentions this as a bug.