1

I have a problem in group by with link to sql
first I have this tables :

  1. client : Id_client , nom , prenom , villeId
  2. ville: id_ville , nom

Code:

 justiceDataContext dbContext = new justiceDataContext();
        GridView1.DataSource = from client in dbContext.Client
                               join ville in dbContext.Commande
                               on client.villeId equals ville.Id_ville
                               group client by ville.nom into g
                               select new { City = g.Key, NumberOfClients = g.Count() };
        GridView1.DataBind();

My objective is to get number of client by city (ville)

thank you

Nadine
  • 25
  • 1
  • 7
  • you want to count how many clients in each city, or you want to show the city id next to the client? It's a little unclear what you want. – Sam W Nov 11 '16 at 14:36
  • Possible duplicate of [I want to count number of occurance of specific group in Generic List](http://stackoverflow.com/questions/40526771/i-want-to-count-number-of-occurance-of-specific-group-in-generic-list) – Gilad Green Nov 11 '16 at 14:36
  • should be something along `select new { ville.nom, s.Distinct().Count() } ` – Innat3 Nov 11 '16 at 14:41
  • @Nadine please check my answer – user449689 Nov 11 '16 at 17:02

1 Answers1

0
dbContext.Client
    .GroupBy(c => c.villeId)
    .Select(g => new {
        CityName = dbContext.Villes.Where(v => v.Id_ville == g.Key),
        NumberOfClient = g.Count()
    }).ToList();

Another approach:

var result = dbContext.Villes
            .Join(dbContextClients, v => v.IdVille, c => c.IdVille, (v, c)  => new { client = c, ville = v })
            .GroupBy(j => j.ville.IdVille)
            .Select(g => new {
                VilleName = g.First().ville.Name,
                NumberOfClients = g.Count()
            }).ToList();
user449689
  • 3,142
  • 4
  • 19
  • 37