Querying virtual list property on EF

user_7879389 0
Viewed 183

I'm trying to get a paginated result on a query that currently returns a virtual list resulting from an N:M relationship:

var selectedProfiles = await (from a in _context.USER_PROFILES
    where a.Id == id
    select a.Followers).FirstOrDefaultAsync();

The thing is: I've tried diferent methods but none works, what i want to archive is:

        var selectedProfiles = await (from a in _context.USER_PROFILES
                                      where a.Id == id
                                      select a.Followers.Skip((pageNum - 1) * pageSize).Take(pageSize))
                                     .FirstOrDefaultAsync();

Obviously the code above doesn't work, but my objective should be quite clear. Is there any way to archive this result without querying the N:M table? The DB is the latest build of mariaDB

entity-framework-core entity-framework linq c#
1 Answers

It sounds like you want a paginated set of Followers for a given Profile?

var followers = _context.USER_PROFILES
   .Where(x => x.Id == id)
   .SelectMany(x => x.Followers)
   .OrderBy(x => /* Order your followers.. */)
   .Skip((pageNum-1)*pageSize)
   .Take(pageSize)
   .ToList();

When using pagination it is important to always provide an OrderBy/OrderByDescending condition.

However, if you want the user profile containing just the first page of followers, you need to use projection to populate what your view wants rather than attempting to pass entities. The entity graph returned for a user profile should always be complete. Related entities cannot be "filtered" otherwise how would EF differentiate a partial entity loaded this way from a complete one?

To get the user profile /w a page of followers:

var profile = _context.USER_PROFILES
    .Where(x => x.Id == id)
   .Select(x => new UserProfileViewModel
   {
       Id = x.Id,
       Name = x.Name,
       // Add the fields to be displayed.
       Followers = x.Followers
          .Select(f => new FollowerViewModel
          {
              Id = f.Id,
              Name = f.Name,
              // Add the fields to be displayed.
          }).OrderBy(f => /* Order your followers.. */)
          .Skip((pageNum-1)*pageSize)
          .Take(pageSize)
          .ToList()
    }).Single();

Using a view model means there is no confusion between the partial model your view will interact with and the complete data state that the entities are tracking. It also allows EF to generate more efficient queries rather than selecting/serializing entire entity graphs.

user_4234970
Related