CODEWITHSUNDEEP
c#linq
Elena Maximova
Elena Maximova

Reputation: 946

Selecting specific columns from 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 of the items in a single group and show the name of the Urgency.

When I run it, the query just gets hung up without any exceptions.

Upvotes: 3

Views: 2176

Answers (4)

ocuenca
ocuenca

Reputation: 39326

You could group by those 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,starting the query 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()})

Upvotes: 1

capiono
capiono

Reputation: 2997

This should work, doing it the other way around, by retrieving all TicketUrgencies first 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()
               });

Upvotes: 1

NetMage
NetMage

Reputation: 26907

Since you are grouping by UrgencyId, you know all members of g have the same id as the Key, so to pick up the name just pull the first one. You also know g isn't empty because that wouldn'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(),
            });

Upvotes: 1

Ali Adlavaran
Ali Adlavaran

Reputation: 3735

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()});

Upvotes: 1

Related Questions