Broken Redshift compatibility in 7.0.1

Hi,

We use Npgsql.EntityFrameworkCore.PostgreSQL to access AWS Redshift using the following configuration:

services.AddDbContextPool<AnalyticalDbContext>(options =>
{
    options.UseNpgsql(StaticConfiguration.GetPostgreSQLConnectionString("Redshift"), builder => builder.UseRedshift());
});

And after we updated the nuget package from 6.0.8 to 7.0.1 it looks like the compatibility is broken. For example, we have the linq query

  var activityItems = _dbContext.ClientActions.AsNoTracking()
      .Where(predicate)
      .GroupBy(x => new
      {
          x.CurrencyId,
          x.Product.GameProviderId
      })
      .Select(x => new GameProviderActivityItem
      {
          WinCount = x.Count(v => v.FinalAmount > 0),
          BonusAmount = x.Where(v => v.BonusId.HasValue).Sum(m => m.FinalAmount ),
      });

Nuget version 6.0.8 translates linq to this:

-- @__filter_From_0='2023-02-01T09:59:26.8180000Z' (DbType = DateTime)
-- @__filter_To_1='2023-02-01T09:59:26.8180000Z' (DbType = DateTime)
-- @__partnerId_2='2'
SELECT COUNT(CASE WHEN ca."FinalAmount" > 0.0 THEN 1 END)::INT AS "WinCount", 
       COALESCE(SUM(CASE WHEN (ca."BonusId" IS NOT NULL) THEN ca."ActionAmount" END), 0.0) AS "BonusAmount"
FROM mdb."ClientActions" AS ca
LEFT JOIN mdb."Client" AS c ON ca."ClientId" = c."Id"
INNER JOIN mdb."Product" AS p ON ca."ProductId" = p."Id"
WHERE ((ca."ActionTime" >= @__filter_From_0) AND (ca."ActionTime" < @__filter_To_1)) AND (c."PartnerId" = @__partnerId_2)
GROUP BY ca."CurrencyId", p."GameProviderId"

and version 7.0.1 translates the same linq to this:

-- @__filter_From_0='2023-02-01T09:59:26.8180000Z' (DbType = DateTime)
-- @__filter_To_1='2023-02-01T09:59:26.8180000Z' (DbType = DateTime)
-- @__partnerId_2='2'
SELECT count(*) FILTER (WHERE ca."FinalAmount" > 0.0)::int AS "WinCount", 
       COALESCE(sum(ca."ActionAmount") FILTER (WHERE ca."BonusId" IS NOT NULL), 0.0) AS "BonusAmount"
FROM mdb."ClientActions" AS ca
LEFT JOIN mdb."Client" AS c ON ca."ClientId" = c."Id"
INNER JOIN mdb."Product" AS p ON ca."ProductId" = p."Id"
WHERE ca."ActionTime" >= @__filter_From_0 AND ca."ActionTime" < @__filter_To_1 AND c."PartnerId" = @__partnerId_2
GROUP BY ca."CurrencyId", p."GameProviderId"

The 7.0.1 version uses the FILTER clause which is unsupported by redshift and causes an 'invalid syntax' error.

Could you please fix the compatibility issue?

npgsql / efcore.pg

Broken Redshift compatibility in 7.0.1 #2634