How to handle Null in LINQ subquery?

Question

I have 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 MemberPrice type is supposed to be decimal and not NULL.

Simplified request:

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 will contain Top (1) and return Null if empty. How can I handle this in LINQ? Is there a way to make MemberPrice null, or the default value is null if not found (or a more elegant solution)?

Thanks a lot, Stuart

Answer 1

Stuart

Price , null, , :

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

, Price , NULL, , , :

decimal?

, , :

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

, . ? .

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;
    }
}

Answer 2

, :

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)

MemberPrice.

Answer 3

DefaultIfEmpty , ?

Answer 4

,

. , #; , VB.

Note the use of the “new” operator in the select statement and the use of the zero coalescence operator after 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;
    }
}