How to handle Null in LINQ Subquery?

Question

I've got a subquery that returns the most recent value from a child table. In some cases the subquery returns nothing. The query below fails at runtime because the inferred type of MemberPrice is decimal and is not nullable.

Simplified query:

Dim q = From s In dc.STOCKs _
        Select s.ID, MemberPrice = _
          (From mp In dc.STOCKPRICEs Where mp.NUMBER = s.NUMBER _
          Order By dc.date Descending _
          Select mp.PRICE).FirstOrDefault

In SQL, the subquery would contain Top (1) and would return Null when empty. How can I handle this in LINQ? Is there a way to make MemberPrice nullable or default the value to zero if not found (or a more elegant solution)?

Many thanks, Stuart

Answer 1

Stuart, try this:

Dim q = From s In dc.STOCKs _
    Select s.ID, MemberPrice = _
      if((From mp In dc.STOCKPRICEs Where mp.NUMBER = s.NUMBER _
      Order By dc.date Descending _
      Select mp.PRICE).FirstOrDefault),0)

The null coalescing operator will coerce the null value to zero for MemberPrice.