get parent name and child count from model using LINQ

asp.net-core c# entity-framework entity-framework-core linq

Question

I have the following model:

public Class Category{
   public int Id {get;set;}
   public string Name {get;set;}
   public ICollection<SubCategory> SubCategories {get;set;}
}

public Class SubCategory{
   public int Id {get;set;}
   public string Name {get;set;}
   public int CategoryId { get; set; }
   public virtual Category Category{ get; set; }
   public ICollection<Ticket> Tickets { get; set; }
}

public class Ticket {
    public Ticket();

    public int Id { get; set; }
    public virtual SubCategory SubCategory{ get; set; }
    public int SubCategoryId{ get; set; }   
}

I want to get data groupBy Category and get the count of tickets in each subcategory using this query:

Entities
                .Include(h => h.SubCategories )
                .ThenInclude(s => s.Tickets)
                .GroupBy(s => s.Id)
                .Select(t => new Cata {
                    Name = t.FirstOrDefault().Name,
                    Children = GetChildern(t.FirstOrDefault().SubCategories )
                });



public List<SubCat> GetChildern(IEnumerable<SubCategories> subs)
        {
            var output = new List<SubCat>();
            foreach (var sub in subs) {
                var subcat = new SubCat();
                subcat.Name = sub.Name;
                if (sub.Tickets != null) {
                    subcat.Size = sub.Tickets.Count;
                }
                output.Add(subcat);
            }
            return output;
        }

With the Query above the ticket is always zero for all, but tickets exists.

1
4
8/8/2017 3:41:36 PM

Accepted Answer

I don't see why you need to do a group by if you start your query in Categories

var result=  Entities
            .Include(h => h.TicketSubCategories)
            .ThenInclude(s => s.Tickets)
            .Select(t => new Cata {
                            Name = t.Name,
                            Children= t.TicketSubCategories
                                       .Select(ts=>new SubCat{
                                                     Name=ts.Name,
                                                     Count=ts.Tickets.Count()})
                         };

Agreed with @Ivan about he commented above, here you don't need to use a custom method, using it you will force the projection of your query to be executed on the client side and not on the server (your DB)

3
8/8/2017 4:02:45 PM

Popular Answer

So each Category has zero or more SubCategories, and each SubCategory has zero or more Tickets. Each Ticket belongs to exactly one SubCategory and each SubCategory belongs to exactly one Category

And you want a query, that results in groups of SubCategories that have the same Category. You want some (or all) properties of each SubCategory, but above all, you want the number of Tickets each SubCategory has.

All elements in every group of SubCategories belong to the same Category. You also want some (if not all) properties of this Category.

The solution is to group all SubCategories into groups of same Category (for efficiency use CategoryId). Then use a Select to get the properties you want.

var result = SubCategories 
    // group them into groups with same CategoryId
    .GroupBy(subCategory => subCategory.CategoryId
    // from every group take the properties you want: 
    .Select(group => new
    {
        // All SubCategories in one group belong to the same Category.
        // For efficiency, take only the Category properties you plan to use,
        CommonCategory = group.Key.Select(category => new
        {
            // take the category properties you want to use
        }

        // The group has a lot of SubCategories.
        // For each subcategory select only the properties you want to use
        SubCategories = group.Select(subCategory => new
        {
            // one of the properties you want is the number of Tickets of this SubCategory:
            TicketCount = subCategory.Tickets.Count(),

            // for efficiency: select only SubCategory properties you plan to use:
            Property1 = subCategory.Property1,
            Property2 = subCategory.Property2,
            ...
        }),
    });

So the result is a sequence of objects. Each object has two Properties:

  • SubCategories: a sequence of some properties of all SubCategories that belong to the same Category.
  • CommonCategory. Several properties of the Category that all SubCategories belong to.

The SubCategories is a sequence. Each element of the sequence is an object with several properties:

  • TicketCount: the number of tickets in the SubCategory
  • other properties: several other properties of the SubCategory

From this it is easy to construct the code to GetChildren



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow