How to use .Include() in EF Core with ViewModel

Question

I have two models

public class PageGroup
{
    public PageGroup()
    {
        Pages = new HashSet<Page>();
    }

    public int GroupID { get; set; }
    public string GroupTitle { get; set; }
    public virtual ICollection<Page> Pages { get; set; }
}

public class Page
{
    public Page()
    {
        
    }

    public int PageID { get; set; }
    public int GroupID { get; set; }
    public string PageTitle { get; set; }
    public string PageText { get; set; }
    public virtual PageGroup PageGroup { get; set; }
}

and a ViewModel

public class ShowGroupsViewModel
{
    public int GroupID { get; set; }
    public string GroupTitle { get; set; }
    public int PageCount { get; set; }
}

I filled this ViewModel with this method

public async Task<IEnumerable<ShowGroupsViewModel>> GetListGroupsServiceAsync()
    {
        return await _context.PageGroups.Include(p => p.Pages.Count).Select(pa => new ShowGroupsViewModel()
        {
            GroupID = pa.GroupID,
            GroupTitle = pa.GroupTitle,
            PageCount = pa.Pages.Count
        }).ToListAsync();
    }

but PageCount is not work. when run the project is has zero value. How can I fill this property? I use .net core 3.1

Answer 1

Since you are using Ef 3.1 Include should not be used because Include("Pages") will bring all Page instances from the SQl server and count them after this.

In EF Net5 it would be done much more simple, using Include, but since you are using EF 3.1 try this:

public async Task<IEnumerable<ShowGroupsViewModel>> GetListGroupsServiceAsync()
    {
    return await ( from pg in  _context.PageGroups
           join p in context.Pages
           on pg.GroupId equals p.GroupId 
           group pg by new { pg.GroupId, pg.GroupTitle}  into g
            select new ShowGroupsViewModel{
             GroupId = g.Key.GroupId,
             GroupTitle =g.Key.GroupTitle
             PagesCount = g.Count()
          }).ToListAsync();
    }