SQL to LINQ expres

Question

I'm trying to convert a SQL expression to Linq but I can't make it work, does anyone help?

SELECT 
COUNT(descricaoFamiliaNovo) as quantidades
FROM VeiculoComSeminovo
group by descricaoFamiliaNovo

I try this:

ViewBag.familiasCount = db.VeiculoComSeminovo.GroupBy(a => a.descricaoFamiliaNovo).Count();

I need to know how many times each value repeats, but this way it shows me how many distinct values there are in the column.

Translating: I am trying to convert a SQL expression to Linq but I can't make it work, does anyone help? — Guilherme Arantes, Jul 31 '19 at 19:44
What have you tried so far. Can you show what LINQ you have written. — Peter Smith, Jul 31 '19 at 19:48
db.VeiculoComSeminovo.GroupBy(a => a.descricaoFamiliaNovo).Count(); This way the program is as long as the "descriptionFamiliaNovo" appears and it is not this information I need — Guilherme Arantes, Jul 31 '19 at 19:51
Please add this into your question rather than as a comment. What output does this produce and what are you expecting? — Peter Smith, Jul 31 '19 at 19:52
Possible duplicate: [LINQ with groupby and count](https://stackoverflow.com/q/7285714/861716). — Gert Arnold, Jul 31 '19 at 20:00

score 1 · Answer 1 · answered Aug 01 '19 at 06:23

You can try:

var list = from a in db.VeiculoComSeminovo
                       group a by a.descricaoFamiliaNovo into g
                       select new ViewBag{
                                  familiasCount=g.Count()
                       };

or

var list = db.VeiculoComSeminovo.GroupBy(a => a.descricaoFamiliaNovo)
                                .Select (g => new ViewBag
                                     {
                                      familiasCount=g.Count()
                                     });

If you need column value:

new ViewBag{
   FieldName=g.Key,
   familiasCount=g.Count()
};

Peter Smith · Answer 2 · 2019-07-31T20:01:06.527

You don't need the GROUP BY unless there are fields other than the one in COUNT. Try

SELECT 
COUNT(descricaoFamiliaNovo) as quantidades
FROM VeiculoComSeminovo

UPDATE, from your comment:

SELECT 
COUNT(descricaoFamiliaNovo) as quantidades,
descricaoFamiliaNovo
FROM VeiculoComSeminovo
GROUP BY descricaoFamiliaNovo

That's it as SQL. In LINQ it is something like:

var reponse = db.VeiculoComSeminovo.GroupBy(a => a.descricaoFamiliaNovo)
                                   .Select ( n => new 
                                     {Name = n.key,
                                      Count = n.Count()
                                     }
                                     )

Not tested.

Peter, I need to know how many incidents there are in each index of the "descricaoFamiliaNovo" column. Example: ONIX = 1 times, PRISMA = 20 times... And that was the only way I could think without having to make countless querrys — Guilherme Arantes, Jul 31 '19 at 19:48

score 0 · Answer 3 · answered Aug 01 '19 at 14:55

Ty all for the help.

I solved the problem using this lines:

// get the objects on db
var list = db.VeiculoComSeminovo.ToList();
// lists to recive data    
List<int> totaisFamilia = new List<int>();
List<int> totaisFamiliaComSN = new List<int>();
// loop to cycle through objects and add the values I need to their lists
foreach (var item in ViewBag.familias)
{
totaisFamilia.Add(list.Count(a => a.descricaoFamiliaNovo == item && a.valorSeminovo == null));
totaisFamiliaComSN.Add(list.Count(a => a.descricaoFamiliaNovo == item && a.valorSeminovo != null));
}

The query was a little slow than I expected, but I got the data

SQL to LINQ expres

3 Answers3