InterceptSql - An easy way to change the entire EF Core generated query

[enoch3712]

Problem and motivation:

"Include" related data from multiple relationships is an excellent feature of EF Core. But sometimes custom SQL queries need to be used, where tools like Dapper or methods like "FromSqlRaw" can be used, but mapping relations with this can be a challenge.

The alternative to this can be the use of interceptors, but they are configured on a Context and global level.

The idea is simply to create a new method to run the SQL as the entire query and get the advantage of the include.

Solution with use case

For simplification, the model present in this model will be used.

            var x = _context.Blogs
                            .Include(e => e.Posts)  
                            .InterceptSql(@"SELECT [b].[BlogId], [b].[Rating], [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
                                                       FROM [Blogs] AS[b]
                                                 LEFT JOIN [Posts] AS[p] ON[b].[BlogId] = [p].[BlogId]
                                                ORDER BY [b].[BlogId] desc").ToList();

InterceptSql basically is a function that adds a DbCommandInterceptor to the list of interceptors, that will replace the generated query and take advantage of the Include feature.

State of art

This code is done, the only thing missing from the PR is the ability to access the DBContext in the queryable extension method and unit tests.

Thank you,

[roji]

@enoch3712 it's not completely clear from the above what exactly you're trying to achieve. However, not that you can specify raw SQL and then compose Include operators on it (or any other LINQ operator):

_ = ctx.Blogs.FromSqlRaw("SELECT * FROM Blogs")
    .Include(b => b.Posts)
    .ToList();

If the above doesn't work for you, can you please provide a short description of what exactly it is you're trying to do, and how the above attempts to address it?

[enoch3712]

That does work! But here is the TL:DR of the motivation:

1. Dapper use case
Imagine that you need a custom (imagine some custom SQL with specific engine SQL features) done by someone else. You could use Dapper, but you want to map the data with several relations. Using in this way, as long provide the ability to EF to map the fields, should be ok.

2. TimeScaleDB use case
timescaledb is a "superset" of Postgres. Basically the same but oriented for time series.

        var query = $@"
                        SELECT extract(epoch from time_bucket('{interval} second', createdat)) as time,
                               operationid,
                               COUNT(*),
                               max(responsetype) as responseType,
                               avg(duration)::integer as average, 
                               username,
                               operationlogs.userId as userId
                            FROM operationlogs inner join user on operationlogs.userId = user.id 
                            WHERE operationid in ({string.Join(',', operationIds)}) AND createdat >= :beforeTime AND createdat < :afterTime
                            GROUP BY time, operationid
                            ORDER BY time ASC, operationid;
                    ";

this query was adapted to make the point for the join. As you can see there is a lot of features that are from the timescaledb, not Postgres. You could use the technique that you mentioned but would have a subquery. Using your example above, but as querystring:

SELECT [c].[BlogId], [c].[Rating], [c].[Url], [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
FROM (
    SELECT * FROM Blogs
) AS [c]
LEFT JOIN [Posts] AS [p] ON [c].[BlogId] = [p].[BlogId]
ORDER BY [c].[BlogId]

Advance tunning:

Other things like basic tunning or the need to remove certain templated portions of the generated query like the Order BY that could slow down a query full of joins.

I know that the need can be debated, but what I've seen makes a lot of sense.

[roji]

The scenarios you describe above sound like a good fit for what I pointed to above, i.e. composing over raw SQL. It's true that operator composition means a subquery, and in some cases that subquery may yield non-optimal performance; but I'd carefully benchmark first to see that this is an actual issue. If the subquery really is an issue, you can always write the full query as SQL yourself, and just use EF Core to materialize the results (i.e. not compose LINQ operators).

Apart from that, as you've written interceptors already exist and allow you to do any sort of rewriting of the SQL. You can also use query tags to mark specific queries which you want your interceptor to rewrite (see docs), that allows you to tweak/activate the interceptor on a query-by-query basis.

So I think the needs you describe above are already sufficiently met by EF Core, and the additional proposed mechanism would likely be quite complex if we attempted to generalize it.

[smitpatel]

Duplicate of #14525

#14525 will track the API and implementation. Very unlikely we will use interceptor for the implementation as it is not really per query scoped.