c# - LINQtoSQL生成的SQL中的外部联接过多

我对LINQ2SQL查询生成的SQL语句有疑问。我有两个数据库表（VisibleForDepartmentId是外键）：

AssignableObject                 Department
----------------------           ------------
AssignableObjectId        ┌────> DepartmentId
AssignableObjectType      │
VisibleForDepartmentId ───┘

以及以下映射信息（请注意，AssignableObject是抽象的）：

<Database Name="SO_755661" Class="DataClassesDataContext">
  <Table Name="dbo.AssignableObject" Member="AssignableObjects">
    <Type Name="AssignableObject" Modifier="Abstract">
      <Column Name="AssignableObjectId" Type="System.Int32"
              DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true"
              IsDbGenerated="true" CanBeNull="false" />
      <Column Name="AssignableObjectType" Type="System.String"
              DbType="VarChar(50) NOT NULL" CanBeNull="false"
              AccessModifier="Private" IsDiscriminator="true"/>
      <Column Name="VisibleForDepartmentId" Type="System.Int32"
              DbType="Int" CanBeNull="true" />
      <Association Name="Department_AssignableObject" Member="VisibleForDepartment"
                   ThisKey="VisibleForDepartmentId" OtherKey="DepartmentId"
                   Type="Department" IsForeignKey="true" />
      <Type Name="Asset" InheritanceCode="Asset" IsInheritanceDefault="true" />
      <Type Name="Role" InheritanceCode="Role" />
    </Type>
  </Table>
  <Table Name="dbo.Department" Member="Departments">
    <Type Name="Department">
      <Column Name="DepartmentId" Type="System.Int32"
              DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true"
              IsDbGenerated="true" CanBeNull="false" />
      <Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL"
              CanBeNull="false" />
      <Association Name="Department_AssignableObject" Member="AssignableObjects"
                   ThisKey="DepartmentId" OtherKey="VisibleForDepartmentId"
                   Type="AssignableObject" />
    </Type>
  </Table>
</Database>

和以下代码：

var loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Asset>(a => a.VisibleForDepartment);
dataContext.LoadOptions = loadOptions;
var assets = from a in dataContext.Assets
             select a;

这将导致SQL查询具有两个相同的左外部联接：

SELECT t0.AssignableObjectType, t0.AssignableObjectId, t0.VisibleForDepartmentId,
       t2.test, t2.DepartmentId, t2.Name, t4.test AS test2,
       t4.DepartmentId AS DepartmentId2, t4.Name AS Name2
FROM dbo.AssignableObject AS t0
LEFT OUTER JOIN (
    SELECT 1 AS test, t1.DepartmentId, t1.Name
    FROM dbo.Department AS t1
    ) AS t2 ON t2.DepartmentId = t0.VisibleForDepartmentId
LEFT OUTER JOIN (
    SELECT 1 AS test, t3.DepartmentId, t3.Name
    FROM dbo.Department AS t3
    ) AS t4 ON t4.DepartmentId = t0.VisibleForDepartmentId

为什么会有两个外部连接，其中一个连接就足够了？

亲切的问候，

罗纳德

最佳答案

我找出了导致这些重复的外部联接的原因。当一个持久类被两个或更多子类继承时，它们就会发生。如果使用LoadWith，则为每个子类将新的外部联接添加到生成的SQL语句中。

在我的示例中，AssignableObject有两个子类：Asset和Role。这导致与Department表的两个外部联接。如果添加另一个子类，则会添加第三个外部联接。

我不确定SQL Server是否足够聪明以至于意识到外部联接是重复的。我在Microsoft Connect上posted this。

编辑：显然我的问题是another issue的重复，并且不会在LINQ2SQL的下一版本中得到解决。

关于c# - LINQtoSQL生成的SQL中的外部联接过多，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/755661/