Too many outer joins in LINQtoSQL generated SQL

Hi,

I have a question about a SQL statement generated by a LINQ2SQL query. I have two database tables (VisibleForDepartmentId is a foreign key):

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

And the following mapping information (note that AssignableObject is abstract):

<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>

And the following code:

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

This results in a SQL query with two identical left outer joins:

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

Why are there two outer joins where one would have been sufficient?

Kind regards,

Ronald

From stackoverflow

• Do you accidentally have 2 foreign key relationships defined on your database between the same 2 columns on the same 2 tables ?

  Ronald Wildenberg : That would almost be too simple :) But it's not the case. I have only one foreign between the two tables.

  Eoin Campbell : can you post your table defnitions for those 2 tables because when I try it with 2 sample tables, where Dept.DeptID is a PK and Asset.DeptID is an FK, I get an Inner Join which is what I'd expect

  Ronald Wildenberg : Hm, I may have oversimplified my example... In the real world these tables have a lot more columns. But I may have left out some other info. One thing that is different is the fact that Asset is a sub class of an abstract persistent base class. I'll try post more info.

  Ronald Wildenberg : I have come up with an example that generates the duplicate outer join. Going to edit my original answer within fifteen minutes.

• You could try doing the left outer join in the query itself. I'm not sure what SQL is generated as I don't have your database here.

  var assets = from a in dataContext.Assets
               join d in dataContext.Departments on 
                    a.VisibleForDepartmentId equals d.DepartmentId
                    into temp
               from t in temp.DefaultIfEmpty()
               select a;
  

• I have created a similar query in LINQPad using its default database

  var loadOptions = new DataLoadOptions();
  loadOptions.LoadWith<Products>(a => a.Category);
  LoadOptions = loadOptions;
  
  var products = from a in Products
                 select a;
  
  products.Dump();
  

  and get

  SELECT [t0].[ProductID], [t0].[ProductName], [t0].[CategoryID], [t2].[test], [t2].[CategoryID] AS [CategoryID2], [t2].[CategoryName]
  FROM [Products] AS [t0]
  LEFT OUTER JOIN (
      SELECT 1 AS [test], [t1].[CategoryID], [t1].[CategoryName]
      FROM [Categories] AS [t1]
      ) AS [t2] ON [t2].[CategoryID] = [t0].[CategoryID]
  

  Only one OUTER JOIN is used as expected.

  Harry : What is that "1 AS [test]" doing in the inner selection?

  Alexander Prokofyev : This query is generated automatically by LINQ to SQL, so I can only guess.

• I found out what causes these duplicated outer joins. They occur when a persistent class is inherited by two or more subclasses. For each subclass a new outer join is added to the generated SQL statement if you use LoadWith.

  In my example, AssignableObject has two subclasses: Asset and Role. This results in two outer joins with the Department table. If I add another subclass, a third outer join is added.

  I'm not sure whether SQL Server is smart enough to realize that the outer joins are duplicated. I've posted this on Microsoft Connect.

  EDIT: Apparently my issue was a duplicate of another issue and it won't be fixed in the next release of LINQ2SQL.