Clever Geek Handbook

Selecting specific columns from a GroupBy list

Model:

public class Ticket {
    public Ticket();

    public int Id { get; set; }
    public virtual TicketUrgency TicketUrgency { get; set; }
    public int UrgencyId { get; set; }   
}

public class TicketUrgency {
    public TicketUrgency();
    [Key]
    public int Id { get; set; }
    [MaxLength(50)]
    [Required]
    public string Name { get; set; }
    public ICollection<Ticket> Tickets { get; set; }
}

I have the following linq statement:

 var model = Entities
                .Include(x => x.TicketUrgency)
                .GroupBy(x => x.UrgencyId)
                .Select(g => new {
                    id = g.Key,
                    count = g.Count(),
                    name = g.FirstOrDefault(u => u.UrgencyId == g.Key).TicketUrgency.Name
                });

I want to Group Entities

by UrgencyId

and then return the key ( UrgencyId

) and also count the items in one group and show the urgency name.

When I run it, the request just hangs without any exceptions.

+3


source to share


4 answers


This should work by doing it the other way around, first fetching all TicketUrgencies and grouping it.



Entities.Include(e => e.Tickets)
               .GroupBy(t => t.Id)
               .Select(g => new {
                   id = g.Key,
                   name = g.FirstOrDefault().Name,
                   count = g.FirstOrDefault().Tickets.Count()
               });
+1


source


Very simple. Just try this:



 var model = Entities
                .Include(x => x.TicketUrgency)
                .GroupBy(x => new {UrgencyId =  x.UrgencyId ,
                          Name = x.TicketUrgency.Name})
                .Select(x=> new { UrgencyId = x.Key.UrgencyId,
                                  Name = x.Key.Name,
                                  Count = x.Count()});
+1


source


You can group these two properties:

var model = Entities
            .Include(x => x.TicketUrgency)
            .GroupBy(x => new{ x.UrgencyId, x.TicketUrgency.Name })
            .Select(g => new {
                id = g.Key.UrgencyId,
                count = g.Count(),
                name = g.Key.Name 
            });

Another way could be, as @ASpirin suggested, start the request from TickerUrgency

:

var result= TicketUrgencies.Include(t=>t.Tickets)
                           .Where(t=>t.Tickets.Any())
                           .Select(t=> new {id=t.Id,name=t.Name, count= t.Tickets.Count()})
+1


source


Since you are grouping UrgencyId

, you know that all members g

have the same ID as and Key

so to pick up the name, just pull the first one. You also know that g is not empty, because that won't make a group:

var model = Entities
            .Include(x => x.TicketUrgency)
            .GroupBy(x => x.UrgencyId)
            .Select(g => new {
                id = g.Key,
                name = g.First().TicketUrgency.Name
                count = g.Count(),
            });
+1


source






More articles:


All Articles