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

Question

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

entity-framework lazy-loading

Question

I am trying to fetch a collection of entities from a one-to-many relationship as an IQueryable so I can filter the data down before fetching this from the database.

To make it clear, consider the following example:

I currently have an entity "User" which has a collection of Pictures:

public virtual ICollection<Picture> Pictures{ get; set; }

The Picture entity may or may not belong to a User, and hence, doesnt have the User property in its definition.

A user may have thousands of pictures, but I would like to select the first 10 for example, ordered by Picture.Id. Is there a way to do this?

Maybe something along the lines like:

IQueryable<ICollection<Picture>> pictures = context.Users.Where(u=>u.UserId == userId).Select(c => c.Pictures)

Thanks!

Karan

View more on Stack Overflow

Anders Arpi · Accepted Answer · 10/2/2012 8:55:08 AM

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

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related