CODEWITHSUNDEEP
c#linq
Matthew Flynn
Matthew Flynn

Reputation: 3931

Nullable Int comparison in Linq Expression Query

I have a query that is returning 0 results when the database has valid entries. I think this is down to a nullable foreign key on the comparison.

I have;

return context.Where<Job>(
       x =>
       x.StatusId != completeStatus.Id &&
       x.StatusId != cancelledStatus.Id).ToList();

where StatusId is;

[ForeignKey("Status")]
public int? StatusId { get; set; }

I want to include all jobs where the StatusId is either null or not the value of the 2 Ids (arbitary integers).

How can I correct the above statement to return results please?

Upvotes: 0

Views: 1758

Answers (3)

CDove
CDove

Reputation: 1950

From your description, I think you want something like this:

return context.Where<Job>(
       x =>
       !x.HasValue ||
       (x.StatusId.Value != completeStatus.Id &&
       x.StatusId.Value != cancelledStatus.Id)).ToList();

It's important to remember that an int? isn't a base type and does not in any way inherit from int/Int32. It's just syntactic sugar for Nullable<int>. It's an object with a Value property that may be null. This is true of any Nullable<T>.

It's also important to implement parentheses to separate your comparisons to prevent unpredictable results and make code maintenance easier. Here, it makes it clear that we want all results that are either without a value for x, or those whose value exists and both doesn't match completeStatus.Id and cancelledStatus.Id.

Upvotes: 0

Richard
Richard

Reputation: 108995

This should do it (if it is LINQ to Entities because it handles comparison to nullables in a somewhat SQL like way)

!x.StatusId.HasValue
|| (x.StatusId!= completeStatus.Id
  && x.StatusId != cancelledStatus.Id)

With other LINQ providers you may need to use x.StatusId.Value in the value comparisons.

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

You can add the OR for the StatusId is null

return context.Where<Job>(
   x =>
   (x.StatusId != completeStatus.Id &&
   x.StatusId != cancelledStatus.Id) ||
   x.StatusId == null).ToList();

Upvotes: 0

Related Questions