I have the following LINQ query
from p in dc.Purchases
where invoiceNumber == null || p.InvNumber == invoiceNumber.Value
select p;
The 'invoiceNumber' is a nullable int - when it's null, the program throws a 'Nullable object must have a value error'. Why is this when it's explicitly checking if it's null first? Is there a way around this?
Thanks,
I think there is something wrong with your query. From the code you've supplied I assume that invoiceNumber is a local variable or a parameter. If this is the case, then why are you even checking if invoiceNumber == null in your query? Such check should be done separately from the query:
if(invoiceNumber == null)
{
return dc.Purchases;
// the query would evaluate to this because invoiceNumber == null will allways return true.
}
else
{
return
from p in dc.Purchases
where p.InvNumber == invoiceNumber.Value
select p;
}
try this:
from p in dc.Purchases
where invoiceNumber == null ||(invoiceNumber!=null && p.InvNumber == invoiceNumber.Value)
select p;
where p.InvNumber == invoiceNumber. Alternatively, add a separate .Where() condition when invoiceNumber is not null.Check this one
from p in dc.Purchases
where (invoiceNumber == null ? p.InvNumber == null : p.InvNumber == invoiceNumber.Value)
select p;
what about
from p in dc.Purchases
where (invoiceNumber ?? p.InvNumber) == p.InvNumber
select p;
but, I would pose the question, if invoiceNumber is null why run this statement?
Use the HasValue property to check if a Nullable has value.
from p in dc.Purchases
where !invoiceNumber.HasValue || p.InvNumber == invoiceNumber.Value
select p;
You don't tell, but I suspect that p.InvNumber is nullable as well. In that case, don't use the .Value:
from p in dc.Purchases
where invoiceNumber == null || p.InvNumber == invoiceNumber
select p;
you don't need to check for null explicitly. there might two cases with the nullable values either it will be null or has some value.
e.g.
CASE 1-
int ? invoiceNumber = null;
var prods = from p in dc.Purchases
where p.InvNumber == invoiceNumber
select p;
CASE 2-
int ? invoiceNumber = 100;
var prods = from p in dc.Purchases
where p.InvNumber == invoiceNumber
select p;
same linq statement will work in both the cases.
The problem can be in Linq-to-Sql - it's trying to convert where statement to SQL so invoiceNumber.Value can be called even it's null.
Please just try where invoiceNumber == null || p.InvNumber == invoiceNumber.
Hope it'll help.
The basic concept of nullable type is... the underlying type will have all the values specified in its range + a null value, to increase the flexibility with the database programming.
nullable types has two readonly properties
1) HasValue 2) Value
HasValue is boolean type and it is set to true automatically if Value has some value in it.
So, for comparisions, you have to use HasValue first and then Value. If u use Value directly, and if it is null, then the above exception will be thrown.
keeping these in mind, I doubt this fragment too,. where invoiceNumber == null
you should try invoiceNumber.Value==null (not invoiceNumber)
or
where invoiceNumber.HasValue && p.InvNumber == invoiceNumber.Value
thanks
invoiceNumber.HasValue?