EF: One-to-many relationship - querying for a subset of the data

The basic idea is to use the OrderBy and Take methods on the Pictures collection of the user. However, since you want to make sure that you only perform a single EntityFramework SQL query without loading the entire Pictures collection of a user, this needs to be expressed in a slightly more specific way.

Query syntax

var result = (from u in users
              where u.Id == userId
               from p in u.Pictures
               orderby p.Id
               select p).Take(10);

Method syntax

var result = context.Users
    .Where(u => u.Id == 2)
    .SelectMany(u => u.Pictures)
    .OrderBy(p => p.Id)
    .Take(10);

Note the call to SelectMany. It's important. Basically this adds up all the Pictures collections of all the selected users into one list and continues the query on this flattened meta-list. In theory this sounds like a pretty big operation, but in this case there should only be one user with a specific ID, so it really just continues with the selected user's Pictures collection. The generated SQL is a single, fast query:

Resulting SQL query (for both of the above)

SELECT TOP (10)
[Extent1].[User_Id] AS [User_Id],
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name]
FROM [dbo].[Pictures] AS [Extent1]
WHERE ([Extent1].[User_Id] IS NOT NULL) AND (2 = [Extent1].[User_Id])
ORDER BY [Extent1].[Id] ASC