CODEWITHSUNDEEP
c#linq
Jesse Liberty
Jesse Liberty

Reputation: 1414

LINQ Count multiple values from a collection

In SQL what I'm trying to accomplish is

SELECT
            SUM(CASE WHEN Kendo=1 THEN 1 ELSE 0 END) as KendoCount,
            SUM(CASE WHEN Icenium=1 THEN 1 ELSE 0 END) as IceniumCount
FROM
            Contacts

I'd like to do this in a C# program using LINQ.

Contacts is a List where Contact has many Booleans such as Kendo and Icenium and I need to know how many are true for each of the Booleans.

Upvotes: 2

Views: 1651

Answers (4)

iOhcidnal
iOhcidnal

Reputation: 11

var result = Contacts
.GroupBy(c => new
{
    ID = "",
})
.Select(c => new
{
    KendoCount = c.Sum(k => k.Kendo ? 1 : 0),
    IceniumCount = c.Sum(k => k.Icenium ? 1: 0),
})
.ToArray()

Upvotes: 1

Reed Copsey
Reed Copsey

Reputation: 564631

I would do this as two separate queries:

int kendoCount = db.Contacts.Count(c => c.Kendo);
int iceniumCount = db.Contacts.Count(c => c.Icenium);

Given that these queries will automatically translate into optimized SQL, this will likely be similar in speed or even potentially faster than any query option, and is far simpler to understand.

Note that, if this is for Entity Framework, you'll need to write this as:

int kendoCount = db.Contacts.Where(c => c.Kendo).Count();
int iceniumCount = db.Contacts.Where(c => c.Icenium).Count();

Upvotes: 3

Jim Wooley
Jim Wooley

Reputation: 10398

At least with LINQ to SQL, the downside of the count functions is that it requires separate SQL requests for each .count method. I suspect Jessie is trying to run a single scan over the table rather than multiple scans for each predicate. Depending on the logic and number of columns you are creating, this may not perform as well. Closer to the original request, try using sum with a ternary if clause as such (from Northwind):

from e in Employees
group e by ""  into g
select new { 
    isUs = g.Sum (x =>  x.Country == "USA" ? 1 : 0),
    NotUs = g.Sum (x =>  x.Country != "USA" ? 0 : 1)
}

LINQ to SQL generates the following (YMMV with other ORM's):

 SELECT SUM(
    (CASE 
        WHEN [t1].[Country] = @p1 THEN @p2
        ELSE @p3
     END)) AS [isUs], SUM(
    (CASE 
        WHEN [t1].[Country] <> @p4 THEN @p5
        ELSE @p6
     END)) AS [NotUs]
FROM (
    SELECT @p0 AS [value], [t0].[Country]
    FROM [Employees] AS [t0]
    ) AS [t1]
GROUP BY [t1].[value]

Upvotes: 5

Fede
Fede

Reputation: 44048

var KendoCount = db.Contacts.Where(x => x.Kendo).Count();
var IceniumCount = db.Contacts.Where(x => x.Icenium).Count();

Upvotes: 3

Related Questions