Retrieve a list containing NULLS with Linq

Question

Is it possible to use LINQ to retrieve a list that may contain nulls.

For example if I have a left outer join like so:

var query=  from c in db.Customers
                join o in db.Orders
                   on c.CustomerID equals o.CustomerID into sr
                from x in sr.DefaultIfEmpty()
                select x.OrderId;

How do I get to a List that could look like {12,13,null,14,null,11,16,17}?

This doesn't work for me:

query.ToList<decimal?>();

Is it possible?

Answer 1

The problem is x.OrderId will throw a NullReferenceException when x is null. You need to check for null first, then return the property if there is an object. For example

select x == null ? (decimal?)null : x.OrderId;

OrderId doesn't quite sound like it ought to be a decimal though...

Answer 2

Try:

var query
    = from c in db.Customers
        join o in db.Orders
        on c.CustomerID equals o.CustomerID into sr
        select (sr != null : sr.OrderId : null);

Answer 3

lc is correct, but it's a bit cleaner to simply cast your select to a nullable type outright.

var query=  from c in db.Customers
                join o in db.Orders
                   on c.CustomerID equals o.CustomerID into sr
                from x in sr.DefaultIfEmpty()
                select (decimal?)x.OrderId;