Is it possible to combine small queries into single query?

user_522663 0
Viewed 62

I have the following queries:

var truckCount = await DbContext.Trucks
    .Where(t => t.Departure == null)
    .CountAsync();

var firstTruck = await DbContext.Trucks
    .Where(t => t.Departure == null)
    .MinAsync(t => t.Arrival);

var railcarCount = await DbContext.Railcars
    .Where(r => r.Departure == null)
    .CountAsync();

var firstRailcar = await DbContext.Railcars
    .Where(t => t.Departure == null)
    .MinAsync(t => t.Arrival);

Can anyone tell me if it's possible to combine these queries into one so that there is only one round trip to the database?

I'd be looking to generate a query something like this.

select
    (select count(*) from Trucks where Departure is null) as TruckCount,
    (select min(Arrival) from Trucks where Departure is null) as FirstTruck,
    (select count(*) from Railcars where Departure is null) as RailcarCount,
    (select min(Arrival) from Railcars where Departure is null) as FirstRailcar

My backend is SQL Server.

.net-6.0 entity-framework-core entity-framework c#
3 Answers

You need to use a third party library which enables to execute multiple queries in a single roundtrip to the database. Maybe this extension with it's future queries works for you.

Otherwise you could implement a stored-procedure which encapsulates the queries (as subqueries) and returns the desired information.

Another option might be to just use 2 queries instead of 4:

var truckInfo = await DbContext.Trucks
    .GroupBy(t => t.Departure == null)
    .Where(g => g.Key == true)
    .Select(g => new { Count = g.Count(), FirstTruck = g.Min(t => t.Arrival) })
    .FirstOrDefaultAsync() ?? new { Count = 0, FirstTruck = DateTime.MinValue };

   // same for Railcars
user_2842400

Not with linq, no. Why? Because of two reasons:

  1. Query syntax has no way to get count and use union to get from one query
  2. Method count, is immediate execution and not deferred, so you can't chain into one query

To be honest, that would be difficult to achieve even with a sql query as the data has different data types and columns.

user_16436760

Just in case, there is EF Core extension linq2db.EntityFrameworkCore (note that I'm one of the creators) which can run this query and almost any SQL ANSI query via LINQ

using var db = DbContext.CreateLinqToDBConnection();

var trucks = DbContext.Trucks
    .Where(t => t.Departure == null);

var railcars = DbContext.Railcars
    .Where(r => r.Departure == null);

var result = await db.SelectAsync(() => new 
{
    TruckCount = trucks.Ccount(),
    FirstTruck = trucks.Min(t => t.Arrival),
    RailcarCount = railcars.Count(),
    FirstRailcar = railcars.Min(t => t.Arrival)
});
user_106463160
Related