How to get how many rows that has a column with the same record

Question

Example table:

Name          Work        City

Bob           Work1       City2
Frank         Work2       City1
Will          Work3       City1
Lisa          Work4       City3
Alex          Work5       City2

How do I with entity framework find out how many names lives in a city?

So the output I get is "2,2,1" (2 City2, 2 city1, 1 city3)

I'd do this in two stages. First, get a list of unique city names. Second, count how many rows have that city name. — Robin Bennett, Sep 19 '18 at 12:42
Possible duplicate of [SQL to Entity Framework Count Group-By](https://stackoverflow.com/questions/11564311/sql-to-entity-framework-count-group-by) — PaulF, Sep 19 '18 at 12:47
`context.TableName.GroupBy(x => x.City).Select(x => new { name = x.Key, count = x.Count() });` — CodingYoshi, Sep 19 '18 at 12:48
@RobinBennett I was thinking that that is what i need to do but i can't figure out how. Could you please explain how to me? — user10384441, Sep 19 '18 at 12:58

score 0 · Answer 1 · answered Sep 19 '18 at 13:05

0

If all are in the same table then the comment given by @CodingYoshi is the correct.

But if All are different table then its required for us to understand the Table structure.

You can do it multiple ways.

Join and Group by the table based on conditions.
Get each record and count up using for loop (not recommended)

answered Sep 19 '18 at 13:05

Mohsin Tolat

125
2
15

It's all in the same table. When i try CodingYoshi's method i get `Cannot implicitly convert type 'System.Collections.Generic.List<>'` – user10384441 Sep 19 '18 at 13:10
`Severity Code Description Project File Line Suppression State Error CS0029 Cannot implicitly convert type 'System.Linq.IQueryable<>' to 'int' DynamicBetting C:\Annevo\DynamicBetting\DynamicBetting\DynamicBetting\DynamicBetting\Pages\Betting\PlaceBet.cshtml.cs 65 Active` – user10384441 Sep 19 '18 at 13:13
I think you are converting the list into integer. can you please post what have you done? – Mohsin Tolat Sep 19 '18 at 13:27
Yeah that is wrong, i just can't figure out what datatype to use. `public int ScoreCount { get; set; }` `var scoreCount = _context.Bet.Where(i => i.MatchId == id).GroupBy(x => x.ScoreBetted).Select(a => new { User = a.Key, count = a.Count() }); ScoreCount = scoreCount;` – user10384441 Sep 19 '18 at 13:39
_context.Bet.Where(i => i.MatchId == id).GroupBy(x => x.ScoreBetted).Select(a => new { User = a.Key, count = a.Count() }) will return the IEnumerable of anonymous type. You can Create the list of dictionary. or you can assign into your custom model. as answered into "https://stackoverflow.com/a/6370057/10309661" – Mohsin Tolat Sep 19 '18 at 13:44
@user10384441 My answer will return a `List` where `a` is an anonymous type with two properties: `name` and `count`. You cannot assign that to an integer. It makes no sense. – CodingYoshi Sep 19 '18 at 18:04
Yeah i know that, it was just a left over from old testing. So is it `IList` or how should that property look like? – user10384441 Sep 20 '18 at 07:49
Its Not `IList` its will be the `List` where `a` is anonymous type, you can access you can assign the value of the query shared into some `var` variable, Or you can create your custom model with property name and count, and access that `var` or custom model variable for fulfill your requirements. – Mohsin Tolat Sep 20 '18 at 12:28

Ahmed Sherien · Answer 2 · 2018-09-20T08:34:26.223

0

assuming that the data is as noted in your question you can do as follows

set.GroupBy(x => x.City)
   .Select(x => new { NamesCount = x.Distinct().Count(), City = x.Key })

if you are using linq-to-sql, you'll need to add a ToList before Select like this

set.GroupBy(x => x.City)
   .ToList()
   .Select(x => new { NamesCount = x.Distinct().Count(), City = x.Key })

edited Sep 20 '18 at 08:34

answered Sep 19 '18 at 13:13

Ahmed Sherien

313
1
2
15

1

I assume you should not take into account duplicates: `NamesCount = x.Select(x => x.Name).Distinct().Count()` – Slava Utesinov Sep 20 '18 at 05:44

How to get how many rows that has a column with the same record

2 Answers2