Databases
StackOverflow2013
Postsdbo
POToo many outer joins in LINQtoSQL generated SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POToo many outer joins in LINQtoSQL generated SQL
    text
    copied!<p>I have a question about a SQL statement generated by a LINQ2SQL query. I have two database tables (<code>VisibleForDepartmentId</code> is a foreign key):</p> <pre><code>AssignableObject Department ---------------------- ------------ AssignableObjectId ┌────&gt; DepartmentId AssignableObjectType │ VisibleForDepartmentId ───┘ </code></pre> <p>And the following mapping information (note that <code>AssignableObject</code> is abstract):</p> <pre><code>&lt;Database Name="SO_755661" Class="DataClassesDataContext"&gt; &lt;Table Name="dbo.AssignableObject" Member="AssignableObjects"&gt; &lt;Type Name="AssignableObject" Modifier="Abstract"&gt; &lt;Column Name="AssignableObjectId" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" /&gt; &lt;Column Name="AssignableObjectType" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" AccessModifier="Private" IsDiscriminator="true"/&gt; &lt;Column Name="VisibleForDepartmentId" Type="System.Int32" DbType="Int" CanBeNull="true" /&gt; &lt;Association Name="Department_AssignableObject" Member="VisibleForDepartment" ThisKey="VisibleForDepartmentId" OtherKey="DepartmentId" Type="Department" IsForeignKey="true" /&gt; &lt;Type Name="Asset" InheritanceCode="Asset" IsInheritanceDefault="true" /&gt; &lt;Type Name="Role" InheritanceCode="Role" /&gt; &lt;/Type&gt; &lt;/Table&gt; &lt;Table Name="dbo.Department" Member="Departments"&gt; &lt;Type Name="Department"&gt; &lt;Column Name="DepartmentId" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" /&gt; &lt;Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" /&gt; &lt;Association Name="Department_AssignableObject" Member="AssignableObjects" ThisKey="DepartmentId" OtherKey="VisibleForDepartmentId" Type="AssignableObject" /&gt; &lt;/Type&gt; &lt;/Table&gt; &lt;/Database&gt; </code></pre> <p>And the following code:</p> <pre><code>var loadOptions = new DataLoadOptions(); loadOptions.LoadWith&lt;Asset&gt;(a =&gt; a.VisibleForDepartment); dataContext.LoadOptions = loadOptions; var assets = from a in dataContext.Assets select a; </code></pre> <p>This results in a SQL query with two identical left outer joins:</p> <pre><code>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 </code></pre> <p>Why are there two outer joins where one would have been sufficient?</p> <p>Kind regards,</p> <p>Ronald</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload