ForeignKey references cause LEFT JOIN ON SELECT * #10386

5 Answer(s)

• 

  0

  ismcagdas created 3 years ago

  Support Team

  Hi @BigCDogCrew

  Sorry for the late reply. As I can see, you are not selecting a specific with in your first query but you are selecting Code field in your LinqPad example. Could you alos try selecting specific fields in your first query ?

  Save Cancel
• 

  0

  BigCDogCrew created 3 years ago

  Hi @ismcagdas,

  Thanks for your help.

  Adding a projection to the ANZ query to return only the DisplayName field...

  var parentId = 1;
  var orgUnits = _organizationUnitRepository
      .GetAll()
      .Where(e => e.Parent.Id == parentId)
      .Select(e => e.DisplayName)
      .ToList();
  

  results in the following generated SQL...

  SELECT [a].[DisplayName]
  FROM [AbpOrganizationUnits] AS [a]
  LEFT JOIN (
      SELECT [a0].[Id], [a0].[Code], [a0].[CreationTime], [a0].[CreatorUserId], [a0].[DeleterUserId], [a0].[DeletionTime], [a0].[DisplayName], [a0].[IsDeleted], [a0].[LastModificationTime], [a0].[LastModifierUserId], [a0].[ParentId], [a0].[TenantId]
      FROM [AbpOrganizationUnits] AS [a0]
      WHERE (tenancyFilter2)
  ) AS [t1] ON [a].[ParentId] = [t1].[Id]
  WHERE (tenancyFilter1) AND ([t1].[Id] = @__p_0)
  
  

  Omitting the projection from the LinqPad query, we use this statement....

   AbpOrganizationUnits.Where(e => e.Parent.Id == 1)
  

  And LinqPad generates the following SQL statement...

  SELECT [t0].[Id], [t0].[Code], [t0].[CreationTime], [t0].[CreatorUserId], [t0].[DeleterUserId], [t0].[DeletionTime], [t0].[DisplayName], [t0].[IsDeleted], [t0].[LastModificationTime], [t0].[LastModifierUserId], [t0].[ParentId], [t0].[TenantId]
  FROM [AbpOrganizationUnits] AS [t0]
  LEFT OUTER JOIN [AbpOrganizationUnits] AS [t1] ON [t1].[Id] = [t0].[ParentId]
  WHERE [t1].[Id] = @p0
  

  Save Cancel
• 

  0

  BigCDogCrew created 3 years ago

  I am going to make a guess at what is happening and why.

  The primary difference between the ANZ query and the LinqPad query is that ANZ applies the tenancy filters. That's the "why". Because ANZ needs to apply the tenancy filters, it has opted to use a nested subquery structure which provides the necessary additional WHERE clause.

  However, the nested subquery also requires a SELECT clause...which by default would be SELECT *. And that's the "what". ANZ doesn't know what fields to select in the subquery, so it's easier (and safer) just to select them all. That way, no matter which fields are requested by the outer query, those fields will be available.

  The ideal solution would be for ANZ to scan the outer query and identify the fields projected from the foreign key, and then include only those fields in the subquery SELECT clause. In the event the outer query does not reference any foregin key field, then the default behavior should be to include only the primary key field; as the primary key field will always be required by the JOIN clause.

  So, the original query which did not include any foreign key field (e.g. no Parent fields referenced in the projection) ....

  var parentId = 1;
  var orgUnits = _organizationUnitRepository
      .GetAll()
      .Where(e => e.Parent.Id == parentId)
      .Select(e => e.DisplayName)
      .ToList();
  

  Should have produced the following code (note the Id field is the only subquery output)...

  SELECT [a].[DisplayName]
  FROM [AbpOrganizationUnits] AS [a]
  LEFT JOIN (
      SELECT [a0].[Id]
      FROM [AbpOrganizationUnits] AS [a0]
      WHERE (tenancyFilter2)
  ) AS [t1] ON [a].[ParentId] = [t1].[Id]
  WHERE (tenancyFilter1) AND ([t1].[Id] = @__p_0)
  

  And when the query does specify a foreign key field, such as this....

              var parentId = 1;
              var orgUnits = _organizationUnitRepository
                  .GetAll()
                  .Where(e => e.Parent.Id == parentId)
                  .Select(e => new { e.DisplayName, ParentDisplayName = e.Parent.DisplayName })
                  .ToList();
  

  Then ANZ should see that "Parent.DisplayName" while scanning the outer query and return only that field in addition to the primary key; like the following....

  SELECT [a].[DisplayName], [t1].[DisplayName] AS [ParentDisplayName]
  FROM [AbpOrganizationUnits] AS [a]
  LEFT JOIN (
      SELECT [a0].[Id], [a0].[DisplayName]
      FROM [AbpOrganizationUnits] AS [a0]
      WHERE (tenancyFilter2)
  ) AS [t1] ON [a].[ParentId] = [t1].[Id]
  WHERE (tenancyFilter1) AND ([t1].[Id] = @__p_0)
  
  
  

  Save Cancel
• 

  0

  ismcagdas created 3 years ago

  Support Team

  Hi @BigCDogCrew

  Could you create an issue on GitHub (https://github.com/aspnetboilerplate/aspnetboilerplate) with your last comment ? We can work on this.

  Save Cancel
• 

  0

  BigCDogCrew created 3 years ago

  Issue Added To GitHub https://github.com/aspnetboilerplate/aspnetboilerplate/issues/6186

  Save Cancel