DataFilter adds it's condition in parenthesis #7220

enes.koroglu created 7 months ago

ABP Framework version: v5.3.3
UI Type: Angular
Database System: EF Core (PostgreSQL)
Tiered (for MVC) or Auth Server Separated (for Angular): no

I have two FullAuditedAggregateRoot entity (Gowih and Company) and MultiTenant and MultiProject (our custom filter). When i join them query is generated as Query 1. I would expect Company to be joined directly like marketPlace without parentesis. MarktePlace is AuditedAggregateRoot entity. As i see dataFilter adds where condition in a parenthesis then join it with prior tables, not adding where condition on the last where clause. Query 2 is which i want the query to be like. Query 1 causes miss index usage or wrong index usage, so causes performance problems.

Query 1:

SELECT
  a."Id",  a."AccountId",  a."AvailabilityStateId",  a."ConcurrencyStamp",  a."CreationTime",  a."CreatorId",  a."DeleterId",  a."DeletionTime",  a."ExtraProperties",  a."LastModificationTime",  a."LastModifierId",  t."Id",
  t."Address",  t."CitizenshipId",  t."City",  t."ConcurrencyStamp",  t."ContactName",  t."CreationTime",  t."CreatorId",  t."DeleterId",  t."DeletionTime",  t."ExtraProperties",  t."IsDeleted",  t."LastModificationTime",  t."LastModifierId"
FROM
  public."AppGowihs" AS a
LEFT JOIN
  public."AppMarketPlaces" AS a0
ON
  a."MarketPlaceId" = a0."Id"
LEFT JOIN 
(
  SELECT
    a1."Id",    a1."Address",    a1."CitizenshipId",    a1."City",    a1."ConcurrencyStamp",    a1."ContactName",    a1."CreationTime",    a1."CreatorId",    a1."DeleterId",    a1."DeletionTime",    a1."ExtraProperties",    a1."IsDeleted",    a1."LastModificationTime",    a1."LastModifierId"
  FROM
    "AppCompanies" AS a1
  WHERE
    (a1."Type" = $14)
    AND ((($1
          OR NOT (a1."IsDeleted"))
        AND ($2
          OR (a1."TenantId" = $3)))
      AND ($4
        OR (a1."ProjectId" = $5))) 
) AS t ON a."InvoceToId" = t."Id"
WHERE
  ((($6
        OR NOT (a."IsDeleted"))
      AND ($7
        OR (a."TenantId" = $8)))
    AND ($9
      OR (a."ProjectId" = $10)))
  AND (((($11 = $16)
        OR ((a."Id" LIKE $11 || $17 ESCAPE $18)
          AND (LEFT(a."Id", LENGTH($11))::character varying(15) = $11::character varying(15))))
      OR (($11 = $19)
        OR ((a."OrderNo" LIKE $11 || $20 ESCAPE $21)
          AND (LEFT(a."OrderNo", LENGTH($11))::character varying(40) = $11::character varying(40)))))
    OR (($11 = $22)
      OR (((a."MarketPlaceOrderNo" IS NOT NULL))
        AND ((a."MarketPlaceOrderNo" LIKE $11 || $23 ESCAPE $24)
          AND (LEFT(a."MarketPlaceOrderNo", LENGTH($11))::character varying(60) = $11::character varying(60))))))
ORDER BY
  a."CreationTime" DESC,
  a."Id",
  t."Id"
LIMIT
  $12
OFFSET
  $13

Query 2:

SELECT
  a."Id",  a."AccountId",  a."AvailabilityStateId",  a."ConcurrencyStamp",  a."CreationTime",  a."CreatorId",  a."DeleterId",  a."DeletionTime",  a."ExtraProperties",  a."LastModificationTime",  a."LastModifierId",  t."Id",
  t."Address",  t."CitizenshipId",  t."City",  t."ConcurrencyStamp",  t."ContactName",  t."CreationTime",  t."CreatorId",  t."DeleterId",  t."DeletionTime",  t."ExtraProperties",  t."IsDeleted",  t."LastModificationTime",  t."LastModifierId"
FROM
  public."AppGowihs" AS a
LEFT JOIN
  public."AppMarketPlaces" AS a0
ON
  a."MarketPlaceId" = a0."Id"
LEFT JOIN 
    "AppCompanies" AS t ON a."InvoceToId" = t."Id"
WHERE
  ((($6
        OR NOT (a."IsDeleted"))
      AND ($7
        OR (a."TenantId" = $8)))
    AND ($9
      OR (a."ProjectId" = $10)))
  AND (((($11 = $16)
        OR ((a."Id" LIKE $11 || $17 ESCAPE $18)
          AND (LEFT(a."Id", LENGTH($11))::character varying(15) = $11::character varying(15))))
      OR (($11 = $19)
        OR ((a."OrderNo" LIKE $11 || $20 ESCAPE $21)
          AND (LEFT(a."OrderNo", LENGTH($11))::character varying(40) = $11::character varying(40)))))
    OR (($11 = $22)
      OR (((a."MarketPlaceOrderNo" IS NOT NULL))
        AND ((a."MarketPlaceOrderNo" LIKE $11 || $23 ESCAPE $24)
          AND (LEFT(a."MarketPlaceOrderNo", LENGTH($11))::character varying(60) = $11::character varying(60))))))
    AND (t."Type" = $14)
    AND ((($1
          OR NOT (t."IsDeleted"))
        AND ($2
          OR (t."TenantId" = $3)))
      AND ($4
        OR (t."ProjectId" = $5))) 
ORDER BY
  a."CreationTime" DESC,
  a."Id",
  t."Id"
LIMIT
  $12
OFFSET
  $13

3 Answer(s)