Feat: IncludeInner extension method to force Parent/Children inner joins

[tsanton]

This is an ask for an explicit inner join extension to force inner joins between parent and child/children entities in a FK relationship. This feature request is a shameless plagiarization of this (closed) issue from EF-core. Here is a summary of the issue and arguments for from the linked issue:

The relational database model doesn't have a concept of mandatory children since ownership is defined by a foreign key in a child relation. As a consequence, in SQL, Include always has to translate to OUTER JOIN.

There are three reasons why this OUTER JOIN may not always be desired.

1) It's not uncommon for parents without children to be invalid or meaningless. Fetching them from the database by plain SQL would always involve an INNER JOIN because there is foreknowledge of the database content. (Which in such cases will be enforced by client-side validation).
2) Irrespective of database content, developers may want to Include children and at the same time filter only parents with children. Here, INNER JOIN is better than OUTER JOIN + a predicate. Again, in plain SQL that would be a no-brainer.
3) INNER JOINs may perform significantly better than OUTER JOINs, esp. in more complex queries, it would be useful for developers to have control over the type of join EF will execute.

As of now I haven't found anything indicating that the EFcore-team will do anything to implement this feature for EF9. Further they seem very set on not explicitly declaring expected behaviour, but rather having it inferred from configuration. That to me means that I should not be holding my breath for this kind of "performance optimisation easily gained from data model understanding" features from the EFcore-team.

This feature request might be more natural implemented in the EF Plus codebase as I see that's where all the IncludeX extension methods are located, but to be fair this is an optimisation that I:

1. can't find any implementation for (and I have searched extensibly)
2. can imagine being highly sought after
3. would gladly pay for

I am therefore hoping this is an implementation/override of the query pipeline that you guys can easily implement, as it will massively benefit the community of "optimisation focused" query writer, and hopefully bring ZZZ Projects more customers :)

[JonathanMagnan]

Hello @tsanton ,

Thank you for reporting.

With your text, I can definitely see the advantage of being able from time to time using the INNER JOIN instead of OUTER JOIN clause.

I will discuss it with my team this week to see if that's something we might want to implement next month or not.

Best Regards,

Jon

[JonathanMagnan]

Hello @tsanton ,

We started to take some time to think about this proposed feature.

Is replacing ALL LEFT JOIN with INNER JOIN could be a possible solution, or do you need to be able to replace depending on what you want to include, so having a method such as IncludeWithInnerJoin?

We believe the replacement of all join could be very easily done on our side, while the IncldueWithInnerJoin will take us way more research and time to make it happen (Doing the first one doesn't exclude we might eventually try to do the others).

Let me know,

Best Regards,

Jon

[tsanton]

Oh @JonathanMagnan , why do you never ask easy questions?

For now ReplaceAll would solve my issues because I create data models that will always have a group by InnerJoin compatibility (i.e. dummy row if nothing else). With time (and for everybody else inheriting old data models) individual overrides is definitely the way to go.

Before I answer (and sorry to try to pin you down) can you give estimates? I.e. if it's 2 weeks for the first (with minimal coding) and you can create a very concise api (i.e. as a final method OverrideAllJoinToBecomeInnerJoins()) then sure: a no brainer (but please deprecate it with time as it's ugly). If it's 0-4 months for the ideal solution and you're willing to prioritise it I'll become a missionary running around the frozen wastelands of Norway and the rest of Scandinavia* (*Finland and Iceland not included) preaching the holiness of ZZZ EntityFramework-Extensions if that's what it takes :)

Looking forward to hearing from you!

/T

[JonathanMagnan]

My developer will start to check the first part this week

It will be easier to give you a good estimate next Monday.

Best Regards,

Jon

[JonathanMagnan]

Hello @tsanton ,

A new version has been released. It's not fully completed, but if you use the latest version, you can add this 2 extension methods to your project:

public static class Extensions
{
	public static IQueryable<T> ReplaceAllLeftJoinByInnerJoin<T>(this IQueryable<T> query)
	{
		return Z.EntityFramework.Plus.PublicMethodForEFPlus.ReplaceAllLeftJoinByInnerJoin(query);
	}
	
	public static IQueryable<T> InterceptorCommandExecuting<T>(this IQueryable<T> query, Action<DbCommand> action)
	{
		return Z.EntityFramework.Plus.PublicMethodForEFPlus.InterceptorCommandExecuting(query, action);
	}
}

And use it to replace all LEFT JOIN by INNER JOIN with the following syntax:

context.Invoices.Include(x => x.Items).ReplaceAllLeftJoinByInnerJoin().ToList()`

You can try it with the following online example

A version of EF Plus should be available that already includes these 2 methods at the end of next month, but as I have shown, you can easily use it by adding those extensions to your project with the latest version.

Best Regards,

Jon