The future of optional dependents sharing table with principal

[smitpatel]

The optional dependents which are sharing table with principal has few technicalities around how they are retrieved and materialized.
There are mainly 2 stages where such determination needs to be made.

• Sql generation so we do get data from server
• Materialization of entity to create actual instances

The basic set of conditions for optional dependents right now is

• Find non-pk required properties, if all of them have value then we get data from server and materialize instance. We need value for all required properties else creating instance would throw.
• If first condition fails. we look for non-pk optional properties. if any of them have value we get data from server and materialize instance. At least one non-null value would indicate that instance exists.
• If second condition fails then we join with other dependents of this table and find if nested dependents exist. If they exist then the current entity does exist. We get this data from server, but currently materialization does not have any info about other related entities so it ends up materializing them.

Issues:

• Complicated SQL because use of Union for nested dependent
• Incorrect results - Cannot determine nested dependent to materialize

Examples

• Materialization condition for optional dependents with all null properties but having own optional dependent #21488 When we get data from server but we don't materialize according to previous version of 3rd condition. This may be materializing now.
• Empty instance instead of null returned for owned navigation property #23198 According to 3rd condition we materializes this but the nested dependents are null so it should not materialize.
• Add model validation for nested dependents #23229 is same as above bucket.

Now SQL side,
#23230 - we need to integrate conditions for existence of dependent when accessing any property which is sharing table with any principal.

So major issue right now in SQL/correct SQL we need for conditional property access & materialization w.r.t nested dependent when dependent does not have at least one non-PK, optional/required property.

Proposals:

• We stop checking nested dependents. We utilize first 2 conditions only else we consider it always true.

  • What this means is that optional dependents act like required dependents if there is no differentiating property.
  • This matches what we have in current behavior so no breaking change apart from people who don't want this behavior and already broken.
  • This simplify the SQL.
  • This solves all the materialization issues we have without involving bigger design challenge.
  • We can verify further than behavior is consistent and generate a warning to let users know.

• We guide people have at least one non-nullable property as a discriminator like for existence of dependent.

  • In this kind of scenario first condition will always be hit.
  • The behavior is very easy to understand.
  • We can simplify the conditions to use just one property rather than all properties.
  • This would be breaking change as it requires schema change for people. This would be only needed if they want the dependent to be null and it does not have any property in it. They are already broken right now since we materialize it.

SQL property access would be written as "existence check ? property access : null" (potentially expanded in check AND property == value")
In first proposal, the existence check will be possible (since no longer joins needed).
In second proposal, the check would be much simpler.

[AndriySvyryd]

I am in favor of the first proposal as it's less breaking. We should throw in model validation for any nested types on optional dependents that don't have required non-shared properties and gather feedback.

[ajcvickers]

Agreed. First proposal seems good. Thanks for writing all this down, Smit.