I’ve always solved this with Multiple Active Result Sets and stored procedures. Collect the data in the stored procedure with temp/in-memory tables and return

I’ve always solved this with Multiple Active Result Sets and stored procedures. Collect the data in the stored procedure with temp/in-memory tables and return minimal, non-duplicated, related result sets. Single round trip, still accumulates results in efficient bulk batches, and allows results to be processed by the client as they stream in.