converting TSQL with count on join to LINQ

Question

I have worked out the Azure SQL statement I need, now I would like to convert it to Linq code. I am working with .Net 6. Concept is simple, there are customers and those customers can have rules, don't need to. Some have the feature turned off altogether (customer.AutoAssignPaRequests) other customers can have set to true (1) but not have any active rules in the rules table.

The query returns all the customers that have the feature enabled and has rules:

SELECT c.CustomerId
     , c.AutoAssignPaRequests
     , COUNT(aar.AutoAssignmentRuleId) AS RuleCount
  FROM Customers                         c
           LEFT JOIN AutoAssignmentRules aar ON aar.CustomerId = c.CustomerId AND aar.IsActive = 1
 WHERE c.AutoAssignPaRequests = 1
 GROUP BY c.CustomerId, c.AutoAssignPaRequests
HAVING COUNT(aar.AutoAssignmentRuleId) > 0
 ORDER c.CustomerId;

I tried to convert it to Linq and it worked fine in LinqPAD 7:

  var customerQuery = 
     from c in context.Customers.AsNoTracking()
     where c.AutoAssignPaRequests == true
     join aar in context.AutoAssignmentRules on 
        new { c.CustomerId, IsActive = true } equals new{ aar.CustomerId, aar.IsActive } into rulesGroup
     where rulesGroup.Any()
     select c.CustomerId;

  var customerList = await customerQuery.ToListAsync();

But when it is put in to the ASP.Net 6 app, it generates this error:

      System.InvalidOperationException: The LINQ expression 'DbSet<Customer>()
          .Where(c => c.AutoAssignPaRequests == True)
          .GroupJoin(
              inner: DbSet<AutoAssignmentRule>(),
              outerKeySelector: c => new {
                  CustomerId = c.CustomerId,
                  IsActive = True
               },
              innerKeySelector: aar => new {
                  CustomerId = aar.CustomerId,
                  IsActive = aar.IsActive
               },
              resultSelector: (c, rulesGroup) => new {
                  c = c,
                  rulesGroup = rulesGroup
               })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Answer 1

Looks like you do not need LEFT JOIN and filter by HAVING. I have rewritten your query and it should return the same result:

var customerQuery = 
   from c in context.Customers
   where c.AutoAssignPaRequests == true
   from aar in context.AutoAssignmentRules.Where(aar => c.CustomerId == aar.CustomerId && aar.IsActive)
   group c by new { c.CustomerId, c.AutoAssignPaRequests } into g
   select new 
   {
      g.Key.CustomerId,
      g.Key.AutoAssignPaRequests,
      RuleCount = g.Count()
   } into s
   orderby s.CustomerId
   select s;

var customerList = await customerQuery.ToListAsync();