LINQ Count null values

Question

I'm doing a simple Pie chart that counts current employee, I'm still new to LINQ so I just want to ask is there a way to total count null values?

Here's my code so far:

public ActionResult PieCount()
{    
    int undefined = db.EMPs.Where(x => x.JS_REF == 1).Count();
    int regular = db.EMPs.Where(x => x.JS_REF ==2 ).Count();
    int contractual = db.EMPs.Where(x => x.JS_REF == 3).Count();
    int probationary = db.EMPs.Where(x => x.JS_REF ==4 ).Count();
    int notdefined = db.EMPs.Where(x => x.JS_REF == null ).Count();

    Ratio obj = new Ratio();

    obj.Undefined = undefined;
    obj.Contractual = contractual;
    obj.Regular = regular;
    obj.Probationary = probationary;
    obj.Notdefined = notdefined;

    return Json(new { result = obj }, JsonRequestBehavior.AllowGet);
}

It's working so far but when I tried to count null value "x.JS_REF == null" I'm getting an error

here's the error: enter image description here

My database: enter image description here

the null value count is valid, so the issue you're having is likely something else. what's the error? — user3468711, Mar 04 '20 at 02:52

Nguyễn Văn Phong · Accepted Answer · 2020-03-04T04:07:29.340

Just add a predicate to your Count() expression

int notdefined = db.EMPs.Count(x => x.JS_REF == 0);

or

int notdefined = db.EMPs.Count(x => String.IsNullOrEmpty(x.JS_REF.ToString()) == null);

Note that: int cannot be null. If a value is not set to it, then the default value I believe is zero. So you should check your type of JS_REF is int or int?

Refactor code

You should get all data at one time then count from them instead of calling multiple times.

var data = db.EMPs.Where(x => 0 <= x.JS_REF && x.JS_REF <= 4).Select(p => p.JS_REF ).ToList();

int undefined = data.Count(x => x == 1);
int regular = ddata.Count(x => x == 2);
int contractual = data.Count(x => x == 3);
int probationary = data.Count(x => x == 4);
int notdefined = data.Count(x => x == 0);

LINQ Count null values

1 Answers1