How can I force LINQ to SQL to perform an INNER JOIN on a nullable foreign key?

Question

I have a very simple set-up. Table "Node" has a nullable foreign key "ObjectId." This is represented in my database model with a one-to-many association. Now, I want to run a query that gives me all Node-Objects with a particular object id. In straight SQL, this is very easy:

SELECT Node.*, Object.*
FROM Node INNER JOIN Object
    ON Node.ObjectId = Object.ObjectId
WHERE Node.ObjectId = @objectId

But now I want to do the same thing in LINQ to SQL:

private static Func<MyDataContext, string, IQueryable<DataNode>> _queryGet =
        CompiledQuery.Compile(
            (MyDataContext context, string objectId) =>
                (from node in context.DataNodes
                 where node.ObjectId == objectId
                 select node));

var loadOptions = new DataLoadOptions();
loadOptions.LoadWith<DataNode>(node => node.DataObject);
context.LoadOptions = loadOptions;

DataNode node = _queryGet.Invoke(context, objectId).FirstOrDefault();
...

The frustrating thing is that LINQ always generates a LEFT OUTER JOIN for this query and nothing I've tried makes difference.

On the face of it, this seems to make sense. The ObjectId foreign key is nullable, so some nodes won't have an associated object. But in my query, I'm supplying an object id. I'm not interested in nodes without an associated object.

In this case, an INNER JOIN is the right thing to do, but how do I convince LINQ?

Answer 1

I think you are just going to have to let it be a left outer join. I imagine when the expression tree is being transformed to SQL, the join and the equality predicate are considered seperate parts of the resultant query. In other words, the LEFT OUTER JOIN is just there because of the fact that you are joining on a nullable foreign key, and the equality part is written in afterwards (so to speak).

Does it really matter that it's not translating how you want it? The fact that you don't always get the most efficient query possible is kind of an accepted tradeoff when you use LINQ to SQL. Most of the time the queries are pretty efficient if you aren't doing anything crazy, and if you really think it's going to impact performance or something, you can always write a stored procedure LINQ to SQL can use.

Answer 2

loadOptions.LoadWith<DataNode>(node => node.DataObject); 

You misunderstand the purpose of this statement. It does not filter the result in any way. It does not get translated into sql that can filter the result in any way. INNER JOIN will filter the result set, and LEFT JOIN won't, so LEFT JOIN is the correct choice.

If you want to filter the nodes, you should use a query that includes your filter criteria:

from node in context.DataNodes  
where node.ObjectId == objectId  
where node.DataObject != null
select node

---

Consider the difference between our queries when objectId is null (the query translator does not inspect objectId's value).