如何强制LINQ to SQL对可为空的外键执行INNER JOIN? [英] How can I force LINQ to SQL to perform an INNER JOIN on a nullable foreign key?

问题描述

我有一个非常简单的设置.表节点"具有可为空的外键"ObjectId".这在我的数据库模型中用一对多关联表示.现在，我想运行一个查询，为我提供具有特定对象ID的所有节点对象.在直接SQL中，这非常简单:

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

但是现在我想在LINQ to SQL中做同样的事情:

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();
...

令人沮丧的是，LINQ 总是会为此查询生成一个LEFT OUTER JOIN，而我尝试过的任何事情都没有作用.

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

从表面上看，这似乎是有道理的.ObjectId外键是可为空的，因此某些节点将没有关联的对象.但是在查询中，我提供了一个对象ID.我对没有关联对象的节点不感兴趣.

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.

在这种情况下，INNER JOIN是正确的选择，但是如何说服LINQ?

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

推荐答案

我最终找到了一个很好的解决方案.答案是简单地使LINQ to SQL成为可能.像这样:

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();

    //...
}

这篇关于如何强制LINQ to SQL对可为空的外键执行INNER JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！