Join where lambda query in EF Core 3.1-CodePudding

I have a problem to write in lambda query this sql query:

select c.Id, c.Name, c.SomeNumber, count(*) from TableA a
  inner join TableB b
  on a.Id = b.aId
  inner join TableC c
  on c.BId = b.Id
  where b.Status = N'Approved'
  and c.Scope = N'Scope1'
  group by a.Id, a.Name, a.SomeNumber

Can you guys help me with this one ? I want to write lambda query to execute this in code. I'm using EF Core 3.1

This is what I ended up so far:

var query = await _dbContext.TableA.Where(a => a.TableB.Any(b => b.Status.Equals("Approved")
                && b.TableC.Any(c => c.Scope.Equals("Scope1"))))
                .GroupBy(g => new { Id = g.Id, Name = g.Name, SomeNumber = g.SomeNumber })
                .Select(s => new { Id = s.Key.Id, Name = s.Key.Name, SomeNumber = s.Key.SomeNumber, Count = s.Count() })
                .GroupBy(g => g.Id).Select(s => new {Id = s.Key, Count = s.Count()}).ToListAsync();

CodePudding user response：

Well, this is corrected query. I have used Query syntax which is more readable when query has lot of joins or SelectMany.

var query = 
    from a in _dbContext.TableA
    from b in a.TableB
    from c in b.TableC
    where b.Status == "Approved" && c.Scope == "Scope1"
    group a by new { a.Id, a.Name, a.SomeNumber } into g
    select new 
    {
        g.Key.Id,
        g.Key.Name,
        g.Key.SomeNumber,
        Count = g.Count()
    }

var result = await query.ToListAsync();