How to query aggregated values from multiple table in EF Core in one query?-CodePudding

I want to query aggregated values from multiple tables like this.

SELECT
  (SELECT MAX(`A`) FROM `TableA`) as `MaxA`,
  (SELECT COUNT(`A`) FROM `TableA` WHERE A > 55) as `CountA`,
  (SELECT MIN(`B`) FROM `TableB`) as `MinB`

Is there a way to do something like this in EF Core in one query?

CodePudding user response：

You can do UNION ALL for queries.

var query = ctx.TableA.GroupBy(x => 1)
    .Select(g => g.Max(a => a.A))
    .Concat(
        ctx.TableB.GroupBy(x => 1)
        .Select(g => g.Max(b => b.B))
    );

Also if You are not restricted with third party etensions usage: linq2db.EntityFrameworkCore, note that I'm one of the creators.

Then this query can be written in simple way

using var db = ctx.CreateLinqToDBConnection();

var result = db.Select(() => new 
    { 
        A = ctx.TableA.Max(a => a.A), 
        B = ctx.TableB.Max(b => b.B)    
    });