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!
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