CODEWITHSUNDEEP
c#linqlinq-to-sql
FreeMars
FreeMars

Reputation: 145

Iterate through LINQ to SQL query containing COUNT and GROUP BY

I have an event registration page with radiobuttonlist populated with event dates, like Jan 1, Feb 1, Mar 1, etc. Once 10 people are registered for a date, I want to disable the option on the radiobuttonlist so that it cannot be selected any longer.

I am new to LINQ to SQL and having some issues translating from straight SQL.

I have a table Events with an EventID and an EventDateID. Data looks like

EventID | EventDateID
1       | 1
2       | 1
3       | 1
4       | 3
5       | 3
6       | 5

I have following SQL that returns exactly what I am looking for, the EventID and the count of occurrences of the eventDateID. 

SELECT eventDateID, COUNT(eventDateID) AS COUNT
FROM EVENTS
GROUP BY eventDateID

Output:
EventID | EventDateID
1       | 3
3       | 2
5       | 1

So far, this is the LINQ I have come up with: 

DataClassesDataContext db = new DataClassesDataContext();

        var query = db.Events
            .GroupBy(ev => new
            {
                ev.EventDateID
            })
            .Select(g => new
            {
                g.Key.EventDateID,
                attendeeCount = g.Count()
            });

The g.Key.EventID gives me the ID I am looking for and the attendeeCount gives me the count, I am just not sure where to go from here to

  1. Iterate through query
  2. Check if the count is >=10
  3. Append "full" to the text and enabled=false for the appropriate radiobuttonlist items.

For now, I am stuck.

I want to do a simple 

foreach (var i in query)
        {
            //check the count
            //do the other stuff
        }

...but I am unsure how to integrate the info from the first LINQ query containing the count with the foreach loop.

(if this is a totally stupid way of doing that, I am 100% open to suggestions).

Thanks.

EDIT to reflect @AjaySingh's answer. 

DataClassesDataContext db = new DataClassesDataContext();

        var query = from ev in db.Events
                    group ev by ev.EventDateID into grp
                    select new
                    {
                        EventDateID = grp.Key,
                        Count = grp.Count(),
                        IsFull = grp.Count() >= 10
                    };

        foreach (var i in query)
        {
            if (i.IsFull)
            {
                rdoDates.Items.FindByValue(i.EventDateID.ToString()).Text += " - Full";
                rdoDates.Items.FindByValue(i.EventDateID.ToString()).Enabled = false;
            }
        }

Upvotes: 0

Views: 822

Answers (1)

AjaySingh
AjaySingh

Reputation: 121

I think you are on right track, but to have more readable query as well as having a bool that can help you decide which radio buttons to display or hide, you can try something like following linq:

var query = from ev in db.Events
        group ev by ev.EventDateID into grp
        select new 
            {
                EventDateID = grp.Key,
                Count = grp.Count(),
                IsFull = grp.Count() >= 10
            };

Upvotes: 1

Related Questions