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.
Not with linq, no. Why? Because of two reasons:
To be honest, that would be difficult to achieve even with a sql query as the data has different data types and columns.
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
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)
});