2

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?

Peter Ruderman
  • 12,241
  • 1
  • 36
  • 58

4 Answers4

2

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.

Ocelot20
  • 10,510
  • 11
  • 55
  • 96
  • This is my first forray into linq. I guess the real problem is that the people who sold this to me did not emphasize the trade-offs. I can't say that the LEFT OUTER JOIN matters at this point (but I suspect that it will), and there are always ways to work around performance problems. Here's a follow-up question for you: Suppose I go the route of creating a stored procedure. Is there any way to leverage the node-object association in this case? – Peter Ruderman Oct 25 '10 at 13:07
  • You can create a stored procedure that returns both entities, but you'll have to map up the association yourself in the code. Here's a good reference for some stored procedure limitations: http://oakleafblog.blogspot.com/2007/09/problems-using-stored-procedures-for_08.html – Ocelot20 Oct 25 '10 at 17:28
0
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).

Amy B
  • 108,202
  • 21
  • 135
  • 185
  • Actually, the load options are necessary to force linq to perform a join in the first place. Otherwise, it will fall back to lazy loading and perform two queries: one for the node and one for the object (when I first access it). – Peter Ruderman Oct 25 '10 at 12:59
0

I did eventually find a good solution to this. The answer is to simply get LINQ to SQL out of the way. Like so:

using (MyDataContext context = CreateDataContext())
{
    // Set the load options for the query (these tell LINQ that the
    // DataNode object will have an associated DataObject object just
    // as before).
    context.LoadOptions = StaticLoadOptions;

    // Run a plain old SQL query on our context.  LINQ will use the
    // results to populate the node object (including its DataObject
    // property, thanks to the load options).
    DataNode node = context.ExecuteQuery<DataNode>(
        "SELECT * FROM Node INNER JOIN Object " +
        "ON Node.ObjectId = Object.ObjectId " +
        "WHERE ObjectId = @p0",
        objectId).FirstOrDefault();

    //...
}
Peter Ruderman
  • 12,241
  • 1
  • 36
  • 58
  • If the question was how to make LINQ do an inner join, how can "sidestep LINQ" be the accepted answer? Look, all the joins in LINQ are basically left joins, but all you have to do is add a condition like `.Where(parent => parent.Child != null)` and you have the equivalent of an inner join. – Mel Nov 26 '19 at 16:26
  • To clarify: All joins using method chaining syntax are inherently outer joins, but joins made through query comprehension syntax using the "join" keyword will be inner joins. – Mel Nov 26 '19 at 17:30
  • Well, it's been years since I thought about this (obviously), but my primary concern was the generated SQL, not the final results of the query. When I wrote the original question, we were rewriting an old OLE DB C++ application for the web. We had spent a lot of time optimizing the queries in the old application, and the LINQ-to-SQL-generated queries were disappointing in many cases (to say the least). – Peter Ruderman Nov 28 '19 at 03:43
0

Seems very complicated for what I think you want.

I would force the joins like this:

from n in context.Nodes join o in context.Objects on n.ObjectId 
    equals o.Object_id select n
SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Matt Berry
  • 1