ToQueryString() and ToDbCommand() variants that accept the entire query as an argument

[roji]

ToQueryString() and ToDbCommand() work on IQueryable, and so cannot be used when e.g. a reducing terminating operator is used (Max(), Min()); it's possible to chop it off, but that changes the SQL. The same limitation applies to ExecuteUpdate/Delete, which behave like other reducing terminating operator.

A solution to this could be ToQueryString()/ToDbCommand() overloads on DbContext, which would accept the entire query as an argument:

_ = context.ToQueryString(ctx => ctx.Blogs.Max());

Note the similarity with the Query() API we've discussed several times in the past, which also accepts a full query as an argument and executes it (i.e. to get all the top-level operators as a quotation rather than execution them).

Note that this would add API noise to the DbContext surface which likely only few people will actually need. Maybe there's some way to combine ToQueryString() with the aforementioned Query() API, to help out with this.

Raised by @clement911 in #33143 (comment) and #33167.

[clement911]

It would be also useful if there was a ToDbBatchCommand() so that we can include the command as part of a DbBatch

[clement911]

In the meantime if anyone is looking for a workaround, I managed to make it work with a small amount of code.
Basically an interceptor records commands and suppress the execution. The collected commands are then returned to the user.

See https://gist.github.com/clement911/695d7a2ef2ac2845a8090309c5d184e7

It seems to work quite well. It assumes that the sql server connector is used. Also, it's using a pre-release of Microsoft.Data.SqlClient to issue batch commands.
There are probably some gotchas that I haven't considered but it seems to work well and the performance seems quite good.
That illustrates the power of interceptors!

UPDATE 2024-02-29 Microsoft.Data.SqlClient 5.2 stable was just released so no pre-release packages are needed anymore

[ajcvickers]

Note from team triage: the idea here is good; we just need a decent API.

[bachratyg]

This should probably have the same shape as #10879 (whatever that turns out to be) and differ only in the return type i.e. string or DbBatchCommand instead of the actual result. Just like ToQueryString/CreateDbCommand vs ToList.

[azan-n]

+1 for this. It will surely make rudimentary testing easier for some of our business logic that I have been meaning to move to ExecuteUpdate.

[roji]

@azan-n I'd recommend against relying on ToQueryString() for testing; either actually execute the query (against a test database managed via e.g. testcontainers.net), or if you're trying to do unit testing without the database, implement the repository pattern to remove EF entirely. See our testing docs for more information.