Automatically checking for NULL relationships with LINQ queries

Question

I am using LINQ to SQL to handle the database querying for an application I am working on.

For the purposes of this example, imagine I have some tables like so

- Company
- Product
- Item
- Order

and lets say a Company has 0 or more Products, a Product has 0 or more Items, and an Item has 0 or more Orders.

Now lets so I have obtained a list of Orders, something like:

IQueryable<Order> myOrders = GetMyOrders();

Now lets say I want to query the orders for a particular Company.Name, but have a situation where each table parent ID can be NULL (I know this doesn't seem logical with my example data but it is just an example)

Making the assumption that there are no NULL parent IDs I could do this:

var filteredOrders = myOrders.Where(x => x.Item.Product.Company.Name == "Company1");

This would work fine, however, because there could be NULL parent ID values, I need to check each parent object to ensure it is not null before querying the next parent (otherwise I will get an exception). So I do something like:

var filteredOrders = myOrders.Where(x => 
    x.Item != null &&
    x.Item.Product != null &&
    x.Item.Product.Company != null &&
    x.Item.Product.Company.Name == "Company1");

So my question: Is there a better way (in terms of readability and ease of coding) to do this than having to include all the null checks? Any null parents found along the way should result in exclusion from the resulting list.

Please no suggestions for preventing null parents, the database will not be changing (and is perfectly valid anyway)

Better as in more performant? I don't think so. I can't think of anyway you can rid yourself of all those null checks. Better as to prettier code? I'd go with Lonli-Lokli's suggestion. — InBetween
@InBetween: Sorry, When I say better I meant more for readability. But keeping in mind my comment to Lonli-Lokli's post, where I mentioned the desired to ultimately have it run as an SQL query and not first return all results — musefan
Sounds like a perfect application for inner joins, which will eleminate all elements with null references. Join all tables and filter the result by company name. (But this won't be more readable) — Stephan
Just out of curiousity, what happens if you have; var filteredOrders = myOrders.Where(x => "Company1" == x.Item.Product.Company.Name); — Mr Moose
@MrMoose: Without checking, this will just give the same null exception if the parents don't exist — musefan

Lonli-Lokli Lonli-Lokli · Accepted Answer · 2012-02-27T11:25:27

3

votes

there is known pattren (see Null object pattern). Also you can read this article

Automatically checking for NULL relationships with LINQ queries

2 Answers