linq count/groupby not working

Question

Hello all,

I want to group by the categoryid and then do a count on this. But I don't know how to do this. I have tried a couple of ways without success. Here is my latest:

public class Count
{
    public int TradersCount { get; set; }
    public int Id { get; set; }
    public string Description { get; set; }       
}


    public IQueryable<Count> CountTradersAttachedToCategories()
    {
        var data = from tc in _db.tblTradersCategories
                   select new Count
                  {

                        Description = tc.tblCategory.description,
                        Id = tc.tblCategory.categoryId,
                        TradersCount = tc.Select(x => x.categoryid).GroupBy().Count()

                   };

        return data;
    }

• tblTradersCategories joins both tblTraders/tblCategories
• A single trader can have many categories
• A single category can have many traders

Thanks in advance for any help.

Clare

Answer 1

Try this:

var data = from tc in _db.tblTradersCategories
           group tc by new { tc.tblCategory.categoryId,
                             tc.tblCategory.description } into g
           select new { Count = g.Count(),
                        Id = g.Key.categoryId,
                        Description = g.Key.description };

If you want that in your Count class you may need to use AsEnumerable() to perform the conversion in process:

var converted = data.AsEnumerable()
                    .Select(c => new Count { TradersCount = c.Count,
                                             Id = c.Id,
                                             Description = c.Description });

You can try doing them all in one go:

var data = from tc in _db.tblTradersCategories
           group tc by new { tc.tblCategory.categoryId,
                             tc.tblCategory.description } into g
           select new Count { TradersCount = g.Count,()
                              Id = g.Key.categoryId,
                              Description = g.Key.description };

But I don't know if that will work. It depends on how the LINQ provider handles it.