How to check if LINQ query will be translated to one or multiple SQL queries/statements when using AsSplitQuery in application runtime, before executing query in database

[PetrovicGoran]

I was wondering whether there is a way to check if LINQ query will be translated to single SQL query/statement or multiple SQL queries/statements when using AsSplitQuery() extension method or QuerySplittingBehavior.SplitQuery option in my DbContext class. It would be very helpful if I could get that information in application runtime (ideally, I would need to know that information before the query/statement is executed against database - ie. on query translation level).

Explanation

The reason for this is optimization I would like to implement in my application, where data filtering would be performed using subquery if only one SQL query would be produced. In case of generating multiple SQL queries, I would first execute query that filters data (and return only Ids of records matching condition), and then use that resulting enumerable object with record Ids in: .Where(p => ids.Contains(p.Id)). In my scenario, query that filters data is always translated to single SQL query/statement.

Problematic scenario

Lets assume we have the following classes inside our Application:

public class Foo
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    ...
    public virtual Xyz XyzProperty { get; set; }
}

public class Bar
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    ...
    public virtual ICollection<Xyz> XyzProperties { get; set; }
}

Then, lets assume we write the following LINQ queries for Foo and Bar:

var foos = _context.Foo
    .Include(p => p.XyzProperty)
    .Where(...)
    .AsSplitQuery()
    .AsQueryable();

var bars = _context.Bar
    .Include(p => p.XyzProperties)
    .Where(...)
    .AsSplitQuery()
    .AsQueryable();

Both foos and bars queries are written as split queries and when we try to run ToQueryString() method, we will get the message stating:

This LINQ query is being executed in split-query mode, and the SQL shown is for the first query to be executed. Additional queries may also be executed depending on the results of the first query

However, when we examine actually generated and performed queries (ie. using SQL Server profiler), we will see that foos query was translated to single SQL query, whereas bars query was translated to 2 SQL queries.

Provider and version information

EF Core version: 8.0.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer 8.0.4
Target framework: .NET 8.0
Operating system: windows
IDE: vscode

[roji]

Whether a LINQ query is translated to multiple SQL queries is determined by rules; if AsSplitQuery() is specified (or is the DbContext default), and a collection Include is present (or a collection navigation is projected out), then that collection will be loaded via a separate SQL query. In other words, wherever a regular query (without AsSplitQuery) would contain a JOIN for a collection, as split query uses a separate SQL query instead of the JOIN.

In your example above, XyzProperty isn't a collection, it's a reference navigation, and therefore there's no need for a split query. This is all explained in the docs:

One-to-one related entities are always loaded via JOINs in the same query, as it has no performance impact.

To summarize, there's no way to programmatically know how a query will be executed before its executed, nor should there be a need for that.

Specifically on what you're trying to do, this maybe sounds a bit similar to #12776; in any case, it sounds like quite a big, fundamental change in how queries are handled, and it's unlikely to be doable as a "simple application optimization". If you provide a bit more context on exactly what you're planning to attempt - and why (including benchmarks) - please post that information and we can discuss further.

[PetrovicGoran]

Sorry for my late reply and thank you for your helpful answer. In that case, I think I'll take a different approach and will try to solve the issue mentioned with explicitly stating option I will use.

As for benchmarks, I still do not have any concrete numbers. At this point, that is something I'm looking at to try optimizing my application (from a SQL performance point of view)

[roji]

@PetrovicGoran I'd strongly advise against exploring optimizations without very clear profiling/benchmarking that shows the benefit of doing what you're planning. If you do manage to benchmark the two approaches, it would be great to see the the results (and the benchmark itself).