- 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)
-
0
hi
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.
This is EF Core behavior, And it has some limitations. See https://learn.microsoft.com/en-us/ef/core/querying/filters https://learn.microsoft.com/en-us/ef/core/querying/filters#limitations
-
0
Do you have any suggestion like disable datafiltre and write conditions manually or anything else?
-
0
hi
You can disable a special filter by https://docs.abp.io/en/abp/latest/Data-Filtering#idatafilter-service-enable-disable-data-filters
Add your custom LINQ query to the repository.
