Get parent name and number of children from model using LINQ
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 the data group groupBy and get the number 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 request above, the ticket is always zero for everyone, but tickets exist.
source to share
I don't understand why you need to make a group 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()})
};
Agreeing with @Ivan on what he commented above, you don't need to use your own method here, by using it, you will force the projection of your query to execute on the client side, not on the server (your DB)
source to share
Thus, each category has zero or more SubCategories, and each subcategory has zero or more Tickets. Each ticket belongs to one subcategory and each subcategory belongs to one category
And you need a query that leads to groups of subcategories that have the same category. You want some (or all) of the properties of each subcategory, but above all, you want the number of tickets in each subcategory to have.
All items in each subcategory group belong to the same category. You also need some (if not all) of the properties in this category.
The solution is to group all subcategories into groups of the same category (for efficient use of CategoryId). Then use 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,
...
}),
});
Thus, the result is a sequence of objects. Each object has two properties:
- SubCategories: a sequence of some properties of all subcategories belonging to the same category.
- CommonCategory. Multiple category properties to which all subcategories belong.
SubCategories is a sequence. Each element of the sequence is an object with several properties:
- TicketCount: number of tickets in the subcategory
- other properties: several other SubCategory properties
From here it is easy to build code GetChildren
source to share