LINQ Join to Top(1) - expected SQL?

[agileorange]

Using EF Core RC2.

Issue as described in stackoverflow http://stackoverflow.com/questions/37810721/linq-join-to-top1?noredirect=1#comment63087615_37810721.

Two Entities: User and Photo. Photo has nullable UserId. So a User can have 0-n Photos.

To select all Users with the first Photo URI I have the following code.

var users = _context.Users.Select( x => new
       {
            Name= x.Name,
            PhotoUri= x.Photos.Select( c => c.Uri ).FirstOrDefault()
       } );

The SQL I would expect is this:

SELECT   User.Name, Photo.Uri
FROM     User
LEFT OUTER JOIN     Photo
ON       Photo.Id =
         (
         SELECT  TOP 1 Id 
         FROM    Photo
         WHERE   UserId = User.Id
         )

However EF is generating one SELECT of Users followed by multiple SELECTs on Photos, one for each User record.

The same occurs with the following code variation:

var users = _context.Users.Select( x => new
       {
            Name= x.Name,
            PhotoUri= x.Photos.FirstOrDefault().Uri
       } );

Is this what I should expect?

[anpete]

Looks like #4007

[agileorange]

@anpete yes quite possibly.

[rowanmiller]

Closing as dupe