I have such tables created through EF ERD

The first and last tables have classes.

public class Work { public Work() { Tags = new HashSet<WorkTag>(); } [Key] public int WorkID { get; set; } [Required] [StringLength(50, MinimumLength = 5, ErrorMessage = "The length of the string must be 5 to 50 characters")] public string Title { get; set; } [Required(ErrorMessage = "Please add the image!")] [Display(Name = "Main image")] public byte[] MainImage { get; set; } public List<Image> Images { get; set; } [Required] public string Content { get; set; } [Required] [Display(Name = "Install instruction")] public string InstallInstruction { get; set; } public virtual ICollection<WorkTag> Tags { get; set; } } public class WorkTag { public WorkTag() { Works = new HashSet<Work>(); } [Key] public int TagID { get; set; } [Required] public string Name { get; set; } public virtual ICollection<Work> Works { get; set; } }

The link table is created automatically, but the problem is that I need to work with it.

The task is to group WorkTagWorks by TagId and display only 5 of the most frequent entries.

  • And what specific records to display? If the five TagId with the largest count in their group, then the WorkTags collection can be considered a grouped selection from WorkTagsWords. Then the desired result is selected like this: context.WorkTags.Select(t => new { TagId = t.TagId, TagName = t.Name, WorksCount = t.Works.Count() }).OrderByDescending(t => t.WorksCount).Take(5) - Uranus
  • @Uranus Thank you very much, this is what you need - Viktor Kozenko
  • What is the problem to prescribe the table, which will link? - Misha
  • not looking for easy ways - Viktor Kozenko
  • @ViktorKozenko, great. Then the answer will issue. - Uranus

1 answer 1

Since in the WorkTags table all the values ​​in the TagID column are unique, the WorkTags collection can be considered a grouped selection by WorkTagWorks. Thus, it remains only to calculate how many Works are connected with a specific WorkTag and select those which have the most Works.

 context.WorkTags.Select(t => new { TagId = t.TagId, TagName = t.Name, WorksCount = t.Works.Count() }).OrderByDescending(t => t.WorksCount) .Take(5);