LINQ count in same table

Question

I have this class in my code-first entity framework context as follows-

class User
{
   int UserId;
   string Name;;
   int ParentId;
}

Database table:

  UserId    Name  ParentId
   1         abc     0
   3         pqr     1
   4         xyz     1

I would like to select from above data like as -

 UserId     Name   ChildQuantity
   1         abc        2
   3         pqr        0
   4         xyz        0

Can I do it using LINQ or Lambda?

Answer 1

You can join table to itself (you need a group join here):

from u in db.Users
join c in db.Users on u.UserId equals c.ParentId into children
select new
{
    u.UserId,
    u.Name,
    ChildQuantity = children.Count()
};

---

Or you can setup a navigation property for the collection of children in the user class. With this navigation property query will look like:

   from u in db.Users
   select new {
      u.UserId,
      u.Name,
      ChildQuantity = u.Children.Count()
   };

I believe it's a little bit cleaner. All you need to do - add an association property for children:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }        
    public int? ParentId { get; set; } // Note: this property is not required
    public IList<User> Children { get; set; }
}

And provide mapping:

modelBuilder.Entity<User>().HasMany(u => u.Children)
    .WithOptional().HasForeignKey(u => u.ParentId).WillCascadeOnDelete(false);

And generated query will look like:

SELECT
    [Extent1].[UserId] AS [UserId],
    [Extent1].[Name] AS [Name],
    (SELECT
        COUNT(1) AS [A1]
        FROM [dbo].[Users] AS [Extent2]
        WHERE [Extent1].[UserId] = [Extent2].[ParentId]) AS [C1]
    FROM [dbo].[Users] AS [Extent1]