CODEWITHSUNDEEP
c#linqlinq-to-sql
frenchie
frenchie

Reputation: 51937

grouping and counting in linq-to-sql

I have the following query that receives a list of IDs and I want to do a count. There's also an object model CountModel that holds the counts with each property defined as an int.

public class GetCountByStatus(List<int> TheIDs)
{
 ...using MyDC...
 var CountData = (from d in MyDC.Data
                  where TheIDs.Contains(d.ID)
                  group d by d.Status into statusgroup
                  select new CountModel()
                  { 
                     CountStatus1 = (from g in statusgroup
                                     where g.Status == 1
                                     select g).Count(),

                     CountStatus2 = (from g in statusgroup
                                     where g.Status == 2
                                     select g).Count(),

                     CountStatusN = ....

                   }).Single();

If for instance there are no elements with status N, will this code crash or will the count be 0 for CountStatusN ? Is this the best way to do what I want?

Thanks.

Upvotes: 0

Views: 172

Answers (2)

Tomas Jansson
Tomas Jansson

Reputation: 23472

I would go for a dictionary instead, try something like this:

var countData = MyDC.Data.Where(y => TheIDs.Contains(y.ID))
    .GroupBy(y => y.Status).ToDictionary(y => y.Key, y => y.Count());

I haven't tried it my self and not written the code in VS, but I think that is almost how you do can do it. That will give you a dictionary where the key is the status and the value is the count of that status.

Defining a model with properties named SomethingX is not very flexible. That means you have to go in an change the model when there is a new status. Keeping the data in the dictionary instead will save you from that.

Upvotes: 2

Dennis Traub
Dennis Traub

Reputation: 51634

Count() will always return an integer, which is 0 if there are no elements with the given status. Therefore CountStatusN will always be an integer as well.

Upvotes: 1

Related Questions