How to make count with linq ASP.NET C#

Question

I´m trying to make this sql request

SELECT number, COUNT(number)
FROM Bet GROUP BY number ORDER BY COUNT(number) DESC;

Code

public static List<Apuesta> GetAllNumbers(ApplicationDbContext db)
{
    List<Bet> bets = (from b in db.Bet select b.Number).Count();

    return bets;
}

and i want to use it in one function using linq.

You assign an int (from the Count) to a List<Bet>. Do you want a list that contains the number of Number in each Bet? (so a List<int>) — Alexander Derck
– Alexander Derck, Commented Feb 21, 2016 at 17:30
Yes, im trying to do that! But it says that you cant convert int - Bet list — Johan Sánchez
– Johan Sánchez, Commented Feb 21, 2016 at 17:52
List<int> bets = (from b in db.Bet select b.Number.Count()).ToList(); should work — Alexander Derck
– Alexander Derck, Commented Feb 21, 2016 at 18:43

ocuenca · Accepted Answer · 2016-02-21 18:17:41Z

1

To get the result you are trying to achieve you can project your query to an anonymous type:

var bets =db.Bet.GroupBy(b=>b.Number)
                .Select(g=>new {Number=g.Key, Count=g.Count()})
                .OrderByDescending(e=>e.Number);

Or a DTO:

public class BetDTO
{
 public int Number{get;set;}
 public int Count{get;set;}
}

Then project your result using that custom class:

 var bets =db.Bet.GroupBy(b=>b.Number)
             .Select(g=>new BetDTO{Number=g.Key, Count=g.Count()})
             .OrderByDescending(e=>e.Number)
             .ToList();

edited Feb 21, 2016 at 18:17

answered Feb 21, 2016 at 18:03

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johan Sánchez Over a year ago

Thanks a lot, but now I need to plus the amount of the numbers, for example, I have the number 50 two times, but each one with a different amount. So, I dont have the idea how to do that. Thanks a lot again.

Johan Sánchez Over a year ago

I add amount in BetDTO and in the cusmtom class i use Amount = ap.Sum() and it works, thanks a lot octavioccl!

feeeper · Accepted Answer · 2016-02-21 18:14:51Z

0

In addition to @octavioccl. If you like SQL-like LINQ-expressions you can use that snippet:

var bets = from b in bets group b by b.Number into g orderby -g.Key  select new { Number = g.Key, Count = g.Count() });

answered Feb 21, 2016 at 18:14

feeeper

3,0474 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to make count with linq ASP.NET C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related