CODEWITHSUNDEEP
c#.net.net-coreentity-framework-coreef-core-3.1
anuith
anuith

Reputation: 739

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

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?

Upvotes: 1

Views: 938

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

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)    
    });

Upvotes: 2

Related Questions