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.

Answer 2

Does the DefaultIfEmpty extension method do what you're looking for?

Answer 3

Stuart,

This is how I got it to work on my machine. I apologize for it being in c#; it's been too long since I've used VB.

Note the use of the "new" operator in the "select" statement, and the use of the null coalescing operator after the FirstOrDefault().

public class Class1
{

    DataClasses1DataContext dc = new DataClasses1DataContext();

    public decimal MemberPrice(int stockID)
    {

        var q = from s in dc.Stocks
                where s.StockID == stockID
                select new
                {
                    StockID = s.StockID,
                    memberPrice = (from mp in dc.StockPrices
                                   where mp.StockID == s.StockID
                                   select mp.Price).FirstOrDefault() ?? 0
                };

        return q.FirstOrDefault().memberPrice;
    }
}

Answer 4

Stuart,

I changed my Price field in the database to not allow nulls, and I got the same errror you did:

"Operator '??' cannot be applied to operands of type 'decimal' and 'int'".

As you pointed out, when Price is set to not allow nulls in the database, the null coalescing operator no longer works because it is expecting to see a nullable type decimal:

decimal?

If I remove the null coalescing operator and run the test case that doesn't contain a price, I get:

"The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.."

Here is the code that works. I cast the subquery result to decimal? before applying the null coalescing operator.

public class Class1
{

    DataClasses1DataContext dc = new DataClasses1DataContext();

    public decimal test(int stockID)
    {

        var q = from s in dc.Stocks
                where s.StockID == stockID
                select new
                {
                    StockID = s.StockID,
                    memberPrice = ((decimal?)(from mp in dc.StockPrices
                                   where mp.StockID == s.StockID
                                   select mp.Price).FirstOrDefault()) ?? 0
                };

        return q.FirstOrDefault().memberPrice;
    }
}