CODEWITHSUNDEEP
c#entity-frameworklinq
Mateen -ul-haq
Mateen -ul-haq

Reputation: 229

Linq remove where if the value is null

That My Linq query

var result = db.APPLICATIONS
.Where(a => Statuses.Contains(a.STATUS_ID))
.Where(a => a.TrackingNo == TrackingNo)

Statuses is a int list and TrackingNo is a nullable int (int?).

Problem: If the TrackingNo is null then i dont want to run this clause or just skip this condition.

Upvotes: 4

Views: 3572

Answers (5)

Zsolt Lácza
Zsolt Lácza

Reputation: 1

I think you can just make it return everything if the value is null like:

.Where(q => id != null ? q.Id == id : true)

Upvotes: -1

xanatos
xanatos

Reputation: 111940

LINQ queries can be built in multiple steps:

var result = db.APPLICATIONS
    .Where(a => Statuses.Contains(a.STATUS_ID));

if (TrackingNo != null)
{
    result = result.Where(a => a.TrackingNo == TrackingNo);
}

Note that if you have a Select (a projection), you probably must build the query in multiple steps in multiple variables:

var result2 = result.Select(a => new { a.STATUS_ID });

with the result2 "built" after the if.

Upvotes: 5

Tormod
Tormod

Reputation: 4583

You can check a nullable int by using its "HasValue" property.

var result = db.APPLICATIONS
    .Where(a => Statuses.Contains(a.STATUS_ID))
    .Where(a => a.HasValue && (a.TrackingNo == TrackingNo))

This will cause it to evaluate the "HasValue" prior to checking the value itself. If HasValue return false, then it will never evaluate the rest of the expression (and thus not cause NullReferenceException). If it is of type "int?", then this will work.

Upvotes: 3

Janne Matikainen
Janne Matikainen

Reputation: 5121

You should first check the values of the filtering parameters before trying to add more stuff to the store expression. This would only apply the Statuses and TrackingNo filtering if the nullable TrackingNo has a value. Otherwise it will return all APPLICATIONS as IQueryable.

var result = db.APPLICATIONS.AsQueryable();

if (TrackingNo.HasValue)
{
    result = result.Where(a => Statuses.Contains(a.STATUS_ID) && a.TrackingNo == TrackingNo);
}

return result;

Alternatively, this would check if you have any statuses to apply and the tracking separatedly.

var result = db.APPLICATIONS.AsQueryable();

if (Statuses != null && Statuses.Count() > 0)
{
    result = result.Where(a => Statuses.Contains(a.STATUS_ID)); 
}

if (TrackingNo.HasValue)
{
    result = result.Where(a => a.TrackingNo == TrackingNo);
}

return result;

Or third option, as it is unclear what you really wanted. This would apply the statuses filtering always and tracking only if it is available

var result = db.APPLICATIONS.Where(a => Statuses.Contains(a.STATUS_ID));    

if (TrackingNo.HasValue)
{
    result = result.Where(a => a.TrackingNo == TrackingNo);
}

return result;

Upvotes: 2

Alper Tunga Arslan
Alper Tunga Arslan

Reputation: 579

Just add && condition and check null. And you can use 1 where condiiton here why second where.Pls try this:

    var result = db.APPLICATIONS
                 .Where(a => Statuses.Contains(a.STATUS_ID) 
                 && a.TrackingNo!=null 
                 && a.TrackingNo == TrackingNo)

Upvotes: 2

Related Questions