Entity Framework, Error while use Groupby

Question

I have 2 tables, Good:

class Good
{
    [Key]
    public int Id { get; set; }

    public int Code { get; set; }
    public string Name { get; set; }
    public int OrderPoint { get; set; }
    public int? GoodGroupId { get; set; }

    public virtual GoodGroup GoodGroup { get; set; }
}

and GoodGroup:

class GoodGroup
{
    [Key]
    public int Id { get; set; }

    public int Code { get; set; }
    public string Name { get; set; }
}

I want to know count of goods in each GoodGroupName with this code:

var res = db.Goods
            .GroupBy(r => r.GoodGroupId)
            .Select(m => new 
                         {
                             m.Name,
                             m.Count(s => s.Id)
                         })
            .ToList();

But the query doesn't find m.Name; anybody know what my mistake is?

Well the result of GroupBy is a sequence of groups. A group doesn't have a name. It has a key, which in this case would be the GoodGroupId. There's nothing in what you've shown to suggest that every GoodGroup with the same ID would have the same name - so what would you expect the "name" of the group to be? — Jon Skeet
– Jon Skeet, Commented Jan 28, 2018 at 17:53
@JonSkeet I want to know Count of goods in each GoodGroupName — Ali
– Ali, Commented Jan 28, 2018 at 18:05
So either group by GoodGroupName as well as GoodGroupId, or look the names up later. — Jon Skeet
– Jon Skeet, Commented Jan 28, 2018 at 18:10
You're going to have to do a join if you want the name from the GoodGroup table. — juharr
– juharr, Commented Jan 28, 2018 at 18:20
@combo_ci Yes, a navigation property will act as a join. So instead of grouping on GoodGrouId you should be able to group on GoodGroup then in you're select m.Key.Name should be what you want. If GoodGroup is properly setup. — juharr
– juharr, Commented Jan 28, 2018 at 18:48

Harald Coppoolse · Accepted Answer · 2018-01-29 08:39:58Z

Apparently you have a true entity-framework one-to-many relation ship: a GoodGroup has zero or more Goods, every Good belongs to exactly one GoodGroup.

If you design your one-to-many according to the entity framework guidelines, your query would be much simpler

class GoodGroup
{
    public int Id {get; set;}  // no need for attribute, automatically primary key

    // a GoodGroup has zero or more Goods:
    public virtual ICollection<Good> Goods {get; set;}

    ... // other properties
}

class Good
{
     public int Id {get; set;}

     // every good belongs to exactly one GoodGroup using foreign key
     public int GoodGroupId {get; set;}
     public virtual GoodGroup GoudGroup {get; set;}

     ... // other properties
}

If you stick to the entity framework code-first conventions, then this is all that entity frame needs to know to detect that you want do configure a one-to-many relationship. If you want to use different class names or properties, you'll have to use attributes or fluent API.

Because I've added the ICollection of Goods to each GoodGroup, you won't need to do a GroupBy:

var result = myDbContext.GoodGroups.Select(goodGroup => new
{
    Name = goodGroup.Name,
    NrOfGroups = goodGroup.Groups.Count(),
});

Entity framework is smart enough to detect that for the Count() a SQL inner join and a Group By and Count are needed.

Thanks, is there exist a problem if I dont put public virtual ICollection<Good> Goods {get; set;} in GoodGroup?
I think the result query must be NrOfGroups = goodGroup.Goods.Count(), instead of NrOfGroups = goodGroup.Groups.Count(),

Artyom Tonoyan · Accepted Answer · 2018-01-28 19:42:12Z

Because the GroupBy method return [IGrouping][1] interface which has "Key" property and it has inherited from IEnumerable interface. You must write the code like this.

//Result Type: List<IEnumerable<GoodGroup>>
var res = db.Goods
                .GroupBy(r => r.GoodGroupId)
                .Select(grp => grp
                    .Select(m => new GoodGroup
                    {
                        Id = m.Id,
                        Name = m.Name,
                        Code = m.Code
                    }))
                .ToList();

Artyom Tonoyan · Accepted Answer · 2018-01-30 08:14:38Z

1

If you want to get "Count" then you can use this part of code:

var res = db.Goods
                .GroupBy(r => r.GoodGroupId)
                .Select(grp => new 
                {
                    Name = grp.First().Name,
                    Count = grp.Count()
                })
                .ToList();

answered Jan 30, 2018 at 8:14

Artyom Tonoyan

945 bronze badges

Collectives™ on Stack Overflow

Entity Framework, Error while use Groupby

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related