In Linq, how do you conduct a basic count?

ado.net entity-framework linq linq-to-entities

Question

I wanted to do a paging style table, but NeerDinner example fetches the entire data into a PaggingList type, and I have more than 10 000 rows to be fetched, so I skipped that part.

so I come up with this query

var r = (from p in db.Prizes
            join c in db.Calendars on p.calendar_id equals c.calendar_id
            join ch in db.Challenges on c.calendar_id equals ch.calendar_id
            join ca in db.ChallengeAnswers on ch.challenge_id equals ca.challenge_id
            join cr in db.ChallengeResponses on ca.challenge_answer_id equals cr.challenge_answer_id

            where
                p.prize_id.Equals(prizeId)
                && ch.day >= p.from_day && ch.day <= p.to_day
                && ca.correct.Equals(true)
                && ch.day.Equals(day)

            orderby cr.Subscribers.name

            select new PossibleWinner()
            {
                Name = cr.Subscribers.name,
                Email = cr.Subscribers.email,
                SubscriberId = cr.subscriber_id,
                ChallengeDay = ch.day,
                Question = ch.question,
                Answer = ca.answer
            })
        .Skip(size * page)
        .Take(size);

Problem is, how can I get the total number of results before the Take part?

I was thinking of:

var t = (from p in db.JK_Prizes
            join c in db.JK_Calendars on p.calendar_id equals c.calendar_id
            join ch in db.JK_Challenges on c.calendar_id equals ch.calendar_id
            join ca in db.JK_ChallengeAnswers on ch.challenge_id equals ca.challenge_id
            join cr in db.JK_ChallengeResponses on ca.challenge_answer_id equals cr.challenge_answer_id

            where
                p.prize_id.Equals(prizeId)
                && ch.day >= p.from_day && ch.day <= p.to_day
                && ca.correct.Equals(true)
                && ch.day.Equals(day)

            select cr.subscriber_id)
        .Count();

but that will do the query all over again...

anyone has suggestions on how can I do this effectively ?

1
6
12/2/2010 9:27:48 AM

Accepted Answer

If you take a query as such:

var qry = (from x in y
           select x).Count();

...LINQ to SQL will be clever enough to make this a SELECT COUNT query, which is potentially rather efficient (efficiency will depend more on the conditions in the query). Bottom line is that the count operation happens in the database, not in LINQ code.

17
12/2/2010 9:35:02 AM

Popular Answer

You can put count for query logic as well as, see the sample as below:

 public int GetTotalCountForAllEmployeesByReportsTo(int? reportsTo, string orderBy = default(string), int startRowIndex = default(int), int maximumRows = default(int))
        {
            //Validate Input
            if (reportsTo.IsEmpty())
                return GetTotalCountForAllEmployees(orderBy, startRowIndex, maximumRows);

            return _DatabaseContext.Employees.Count(employee => reportsTo == null ? employee.ReportsTo == null : employee.ReportsTo == reportsTo);
        }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow