Using owned types in inheritance hierarchies generates unnecessary joins

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
laurensb
Posts: 6
Joined: Thu 06 Jun 2019 07:44

Using owned types in inheritance hierarchies generates unnecessary joins

Post by laurensb » Thu 17 Oct 2019 12:49

Using Owned Entity Types as a property in an inheritance hierarchy generates unnecessary joins, reducing query performance.

Given this hierarchy:

Code: Select all

public abstract class Rider
{
	public long Id { get; private set; }
}

public class BeastRider : Rider
{
	public Beast Beast { get; private set; }

	public BeastRider(Beast beast) : base()
	{
		Beast = beast;
	}
}

public class Beast : ValueObject
{
	public string Name { get; private set; }

	public EquineBeast Type { get; private set; }

	public Beast(string name, EquineBeast type)
	{
		Name = name;
		Type = type;
	}

	protected override IEnumerable<object> GetEqualityComponents()
	{
		yield return Name;
		yield return Type;
	}
}
Querying beast riders generates the following SQL:

Code: Select all

SELECT
	 "r".ID,
	 "r".DISCRIMINATOR,
	 "t".ID AS ID1,
	 "t".BEAST_NAME,
	 "t".BEAST_TYPE

FROM RIDER "r"
LEFT JOIN (
	
    SELECT
		 "b.Beast".*
    
	FROM RIDER "b.Beast"
    
	WHERE "b.Beast".DISCRIMINATOR = 'BeastRider'
)
 "t" ON "r".ID = "t".ID

WHERE "r".DISCRIMINATOR = 'BeastRider'
FETCH FIRST 1 ROWS ONLY
As BEAST_NAME and BEAST_TYPE are both part of the BEAST table, this join is not necessary. The behaviour is the same when Beast is a property of Rider instead of BeastRider.

When using an owned entity type on a regular entity (not in an inheritance hierarchy), this join is not generated. This is demonstrated in the sample repository below with the "OtherBeastRider" entity. Querying this entity generates the following SQL:

Code: Select all

SELECT
	 "o".ID,
	 "o".ID AS ID1,
	 "o".BEAST_NAME,
	 "o".BEAST_TYPE

FROM OTHER_RIDER "o"
FETCH FIRST 1 ROWS ONLY

A repository reproducing the issue can be found here: https://github.com/LBRitsSES/devart-efc ... -bug-repro

Shalex
Site Admin
Posts: 8862
Joined: Thu 14 Aug 2008 12:44

Re: Using owned types in inheritance hierarchies generates unnecessary joins

Post by Shalex » Sat 19 Oct 2019 19:01

Thank you for your report. We will investigate the issue and notify you about the result.

Shalex
Site Admin
Posts: 8862
Joined: Thu 14 Aug 2008 12:44

Re: Using owned types in inheritance hierarchies generates unnecessary joins

Post by Shalex » Mon 28 Oct 2019 17:17

The same unnecessary join is generated by EF Core 2.2 with System.Data.SqlClient as well.

Please use EF Core 3 instead of EF Core 2.2.

Entity Framework Core 3.0 is supported by dotConnect for Oracle v9.9.867: viewtopic.php?f=1&t=39475.

laurensb
Posts: 6
Joined: Thu 06 Jun 2019 07:44

Re: Using owned types in inheritance hierarchies generates unnecessary joins

Post by laurensb » Tue 29 Oct 2019 08:55

Thank you for the update. Does it mean that this issue will not be present using EF Core 3.0 with dotConnect v9.9.867?

Shalex
Site Admin
Posts: 8862
Joined: Thu 14 Aug 2008 12:44

Re: Using owned types in inheritance hierarchies generates unnecessary joins

Post by Shalex » Wed 30 Oct 2019 20:28

We cannot tell for sure because migrating your project to EF Core 3 leads to the mapping problem (''Rider.Id' and 'BeastRider.Beast#Beast.Id' are both mapped to column 'ID' in 'RIDER' but are configured with different nullability.') with both Devart.Data.Oracle and System.Data.SqlClient.

Post Reply