EFCore 5 count child records of a child of a parent

Question

I tried to find first, sorry if it's duplicate.

I have a "Posts" table, and a "Users" table. Each Post is written by an User. My models looks like:

[Table("Post")]
public class Post
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Content { get; set; }
    public User Author { get; set; }
}

[Table("User")]
public class User
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Username { get; set; }

    [NotMapped]
    public int PostCount { get; set; }
}

I can include the Authors by doing this:

return _context.Set<Post>()
    .Include(p => p.Author)
    .ToList();

But I don't know how to fill the "PostCount" property with the number of Posts of each Author.

Is it possible to make efficiently using EF? I'm almost giving up and writing it in plain SQL.

Answer 1

Im not a big fan of [NotMapped] properties because everytime im trying to use them or to find how to use them i just fail so my answer will be a little different.

So, first thing to do

    [Table("Post")]
    public class Post
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        public string Content { get; set; }
        public virtual User Author { get; set; }
    }

    [Table("User")]
    public class User
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        public string Username { get; set; }

        public virtual ICollection<Post> Posts { get; set; }

        [NotMapped]
        public int PostCount { get; set; }
    }

Changes in your models:

1. virtual keyword for User and Posts. They are used for lazy-loading support.

2. public virtual ICollection<Post> Posts { get; set; }.

3. (I'd remove NotMapped prop but it is up to you)

Now about usage:

using (var db = new ApplicationContext())
{
    var postWithAuthor = db.Posts
        .Include(p => p.Author)//.ThenInclude(a => a.Posts)
        .Select(p => new { Post = p, PostsCount = p.Author.Posts.Count })
        .Where(x => x.Post.Content == "content1")
        .FirstOrDefault();

    return postWithAuthor;
}

That query will generate the following SQL query

Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "p"."ID", "p"."AuthorID", "p"."Content", "u"."ID", "u"."Username", (
          SELECT COUNT(*)
          FROM "Post" AS "p0"
          WHERE "u"."ID" = "p0"."AuthorID")
      FROM "Post" AS "p"
      INNER JOIN "User" AS "u" ON "p"."AuthorID" = "u"."ID"
      WHERE "p"."Content" = 'content1'
      LIMIT 1

The resulting object will look like that: { Post = {EFCorePlayground.API.DAL.Post}, PostsCount = 4 }. Author entity will also be loaded AND important part here - loaded Author will have only 1 loaded Post in its Posts collection. If you uncomment .ThenInclude part in my code - all 4 posts will be loaded from database which is not what you need.

I used the following Seed for that:

        static void Seed()
        {
            var authors = new List<User>
            {
                new User() { Username = "user 1" },
                new User() { Username = "user 2" },
            };

            var posts = new List<Post>
            {
                new Post(){ Author = authors[0], Content = "content1" },
                new Post(){ Author = authors[0], Content = "content" },
                new Post(){ Author = authors[0], Content = "content" },
                new Post(){ Author = authors[0], Content = "content" },
                new Post(){ Author = authors[1], Content = "content" },
                new Post(){ Author = authors[1], Content = "content" },
            };
            using (var db = new ApplicationContext())
            {
                db.users.AddRange(authors);
                db.Posts.AddRange(posts);
                db.SaveChanges();
            }
        }

Last part: