Question

I m trying to simulate:

WHERE x.IsActive = true OR x.Id = 5

The following causes AND to be used... how do I simulate an OR condition with IQueryable (qry) and my nullable int, given that other filtering might be involved as with the IsActive filter here?

            if (onlyActiveItems) //bool
            {
                qry = qry.Where(x => x.IsActive == true);
            }

            if (whenSpecifiedMustIncludeRecordWithThisId.HasValue) //int?
            {
                qry = qry.Where(x => x.Id == whenSpecifiedMustIncludeRecordWithThisId.Value);
            }

I have considered union but its seems the answer to this should be much simpler.

This is one solution which gets around the problem I get with "Nullable object must have a value" when trying the combined all in one answer. What causes the nullable to be evaluated when it is null otherwise?

            if (whenSpecifiedMustIncludeRecordWithThisId.HasValue)
            {
                qry = qry.Where(x => (!onlyActiveItems || (onlyActiveItems && x.IsActive)) || x.Id == whenSpecifiedMustIncludeRecordWithThisId.Value);
            }
            else
            {
                qry = qry.Where(x => (!onlyActiveItems || (onlyActiveItems && x.IsActive)));
            }

It seems also in some cases the use of the nullable s .Value property makes a difference as seen in another question of mine here Linq to SQL Int16 Gets Converted as Int32 In SQL Command

Answer 1

Try this:

qry = qry.Where(x => (onlyActiveItems
                      ? x.IsActive
                      : false) ||
                     (whenSpecifiedMustIncludeRecordWithThisId.HasValue
                      ? x.Id == whenSpecifiedMustIncludeRecordWithThisId
                      : false) ||
                     (!onlyActiveItems && !whenSpecifiedMustIncludeRecordWithThisId.HasValue));

Note that we re comparing an int? to an int, not two ints.

I am assuming here that the point of the query is to filter out if certain conditions are met.

If onlyActiveItems is true, it verifies whether the IsActive field is true
If whenSpecifiedMustIncludeRecordWithThisId.HasValue is true it verifies whether the value matches the Id field
If both are true it will logically OR the conditions
If both are false all records are displayed (if this is not the intent, you can remove the last condition)

Answer 2

When working with "int?" I usually compare them using object.Equals(i1, i2), for example

from r in cxt.table
where object.Equals(r.column, nullableInt)
select r

This avoids all nullable issues.

友情链接