Proposal: Add overload to .Include for custom SQL query

[LeroyK]

We currently use DbSet<>.FromSql to execute temporal queries on System-Versioned Temporal Tables, this works fine, but we can't use .Include in these queries, since you can't specify a SQL query for included entities. There is a workaround, which is to select all of the included entities beforehand using .FromSql so they are already in the EF Core model. However, this is quite cumbersome to do and involves a lot of roundtrips to the DB.

I would like to propose an enhancement that adds an overload on .Include that allows the SQL query to be provided, similar to .FromSql on DbSet<>.

Example:

var temporalQueryClause = " FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011'";
var users =
    db.Users
    .FromSql("SELECT * FROM [dbo].[User]" + temporalQueryClause)
    .Include(x => x.Login, "SELECT * FROM [dbo].[Login]" + temporalQueryClause);

[ajcvickers]

@LeroyK This is an interesting idea. We discussed it in triage and we're going to put it on the backlog for now. However, it could be that the same functionality might be better implemented in a different place, such as in a defining query although this would likely require the defining query to be parameterized. It might also be covered by global query hints. Then, of course, we hope to have better support for temporal tables without needing these things.