Lambda with SUM+Conditional

[pod]

I am trying to convert an SQL statement into a LAMBDA expression. I have a basic understanding, I know how to write simple statements c/w filter portion(WHERE) but could not find anything on how to add conditionals into the SELECT portion.

Right now I query without the conditional part but the web server must loop through millions of returned records to do the calculation where I would prefer the SQL server to perform this on the first call.

var EDsAndCounts = db.Electors.
                Where(x => x.VotedStrikeList == true
                    || x.VotedPollBook == true).
                Select(x => x).ToList();

Anyway I am not asking for the complete solution, I am looking for a examples that have aggregate functions with a conditional expressions, ... if that is even possible.


Here is one of the sql statement I need to convert.

Thank you in advance for any input /info on the matter.

P:oD

SELECT elector.ed_code, 
       Sum(CASE 
             WHEN votedstrikelist = 1 THEN 1 
             ELSE 0 
           END) AS VotedStrikeListCount, 
       Sum(CASE 
             WHEN votedpollbook = 1 THEN 1 
             ELSE 0 
           END) AS VotedPollBookCount, 
       Sum(CASE 
             WHEN votedpollbook = 1 
                   OR votedstrikelist = 1 THEN 1 
             ELSE 0 
           END) AS TotalCount 
FROM   elector 
WHERE  ( votedstrikelist = 1 ) 
        OR ( votedpollbook = 1 )
GROUP  BY elector.ed_code

[PsychoCoder]

Might now be exactly what you're looking for but try using the Contains conditional expression, similar to this

var EDsAndCounts = db.Electors.
                Where(x => x.VotedStrikeList == true
                    || x.VotedPollBook == true || x.Contains("Criteria").
                Select(x => x).ToList();

[pod]

my solution

Thank you PsychoCoder for your reply... but that is not what exactly what I needed, I wanted the SUM of individual cases, and I figured out a way to do it ... I decided to do it outside the query

            var myList = db.Electors.
                            Where(x => x.VotedStrikeList == true
                                || x.VotedPollBook == true
                                || x.Flagged == true
                                || x.Comment.Length > 0).
                            Select(x => x).ToList();
            ViewBag.VSL = myList.Sum(c => (c.VotedStrikeList == true ? 1 : 0));
            ViewBag.VPB = myList.Sum(c => (c.VotedPollBook == true ? 1 : 0));
            ViewBag.FLG = myList.Sum(c => (c.Flagged == true ? 1 : 0));
            ViewBag.COM = myList.Sum(c => (c.Comment.Length > 0 ? 1 : 0));
            ViewBag.PRC = myList.Sum(b => (b.VotedStrikeList == true ? 1 :
                                            (b.VotedPollBook == true ? 1 :
                                                  (b.Flagged == true ? 1 :
                                               (b.Comment.Length > 0 ? 1 : 0)))));