Do you have any suggestion like disable datafiltre and write conditions manually or anything else?
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
Thanks, it worked
We have below query in logfile but i want to see parameters' details. I tried adding .EnableSensitiveDataLogging(); at then end of var builder = new DbContextOptionsBuilder<FFEDbContext>().UseNpgsql(configuration.GetConnectionString("Default")).EnableSensitiveDataLogging(); command in ContextFactory class' CreateDbContext method.
Also tried maliming's suggestion on github like below in FFEEntityFrameworkCoreModule class' ConfigureServices method, sequentially commented 2 lines and the line which is commented out as 3rd option. But nothing changed in logs. How can i see parameter values in logs?
Configure<AbpDbContextOptions>(options =>
{
options.Configure(context =>
{
//context.DbContextOptions.UseLoggerFactory(new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) }));
//context.DbContextOptions.UseLoggerFactory(context.ServiceProvider.GetService<ILoggerFactory>());
context.DbContextOptions.UseLoggerFactory(LoggerFactory.Create(builder => { builder.AddConsole(); }));
context.DbContextOptions.EnableDetailedErrors();
context.DbContextOptions.EnableSensitiveDataLogging();
});
options.UseNpgsql();
});
Log content:
Executed DbCommand (7,080ms) [Parameters=[@__ef_filter__p_5='?' (DbType = Boolean), @__ef_filter__p_6='?' (DbType = Boolean), @__ef_filter__CurrentTenantId_7='?' (DbType = Guid), @__ef_filter__p_8='?' (DbType = Boolean), @__ef_filter__CurrentProjectId_9='?' (DbType = Int32), @__ef_filter__p_0='?' (DbType = Boolean), @__ef_filter__p_1='?' (DbType = Boolean), @__ef_filter__CurrentTenantId_2='?' (DbType = Guid), @__ef_filter__p_3='?' (DbType = Boolean), @__ef_filter__CurrentProjectId_4='?' (DbType = Int32), @__ToLower_0='?', @__filterText_1='?', @__NormalizeFFE_2='?', @__p_4='?' (DbType = Int32), @__p_3='?' (DbType = Int32)], CommandType='"Text"', CommandTimeout='30']
SELECT a."Id", a0."Id" AS "Id0", t."Id" AS "Id1", t0."Id" AS "Id2", a."CreationTime"
FROM public."AppGowihs" AS a
LEFT JOIN public."AppMarketPlaces" AS a0 ON a."MarketPlaceId" = a0."Id"
LEFT JOIN (
SELECT a1."Id", a1."Name"
FROM "AppCompanies" AS a1
WHERE (a1."Type" = 6) AND (((@__ef_filter__p_5 OR NOT (a1."IsDeleted")) AND (@__ef_filter__p_6 OR (a1."TenantId" = @__ef_filter__CurrentTenantId_7))) AND (@__ef_filter__p_8 OR (a1."ProjectId" = @__ef_filter__CurrentProjectId_9)))
) AS t ON a."InvoceToId" = t."Id"
INNER JOIN (
SELECT a2."Id", a2."NameNormalized"
FROM "AppCompanies" AS a2
WHERE (a2."Type" = 4) AND (((@__ef_filter__p_5 OR NOT (a2."IsDeleted")) AND (@__ef_filter__p_6 OR (a2."TenantId" = @__ef_filter__CurrentTenantId_7))) AND (@__ef_filter__p_8 OR (a2."ProjectId" = @__ef_filter__CurrentProjectId_9)))
) AS t0 ON a."ShipToId" = t0."Id"
WHERE (((@__ef_filter__p_0 OR NOT (a."IsDeleted")) AND (@__ef_filter__p_1 OR (a."TenantId" = @__ef_filter__CurrentTenantId_2))) AND (@__ef_filter__p_3 OR (a."ProjectId" = @__ef_filter__CurrentProjectId_4))) AND (((((((@__ToLower_0 = '') OR (strpos(lower(a."Id"), @__ToLower_0) > 0)) OR ((@__filterText_1 = '') OR (strpos(a."OrderNo", @__filterText_1) > 0))) OR ((@__ToLower_0 = '') OR (strpos(lower(a."MarketPlaceOrderNo"), @__ToLower_0) > 0))) OR ((@__ToLower_0 = '') OR (strpos(lower(a0."Name"), @__ToLower_0) > 0))) OR ((@__NormalizeFFE_2 = '') OR (strpos(t0."NameNormalized", @__NormalizeFFE_2) > 0))) OR ((@__ToLower_0 = '') OR (strpos(lower(t."Name"), @__ToLower_0) > 0)))
ORDER BY a."CreationTime" DESC
LIMIT @__p_4 OFFSET @__p_3
Send deployment scripts to mail address
We have deployment scripts nearly same (except server name and build directory). If you share email address, i can send build scripts for both environment.
I will also try your logging code and share result but i tried a method and results are like below. Can you please check below log.
Valid URL: https://myproject-api.ekol.com/api/account-admin/settings/two-factor Invalid URL (HTTP404): https://myproject-api.ekol.com/swagger/v1/api/account-admin/settings/two-factor
2023-08-28 16:39:50.203 +03:00 [INF] Request:
Protocol: HTTP/1.1
Method: PUT
Scheme: https
PathBase:
Path: /swagger/v1/api/account-admin/settings/two-factor
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Connection: keep-alive
Host: myproject-api.ekol.com
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36
Accept-Encoding: gzip,deflate,br
Content-Type: application/json
Cookie: [Redacted]
Content-Length: 210
X-Real-IP: [Redacted]
X-Forwarded-For: [Redacted]
X-Forwarded-Proto: [Redacted]
2023-08-28 16:39:50.206 +03:00 [ERR] An unhandled exception has occurred while executing the request.
System.UriFormatException: Invalid URI: The hostname could not be parsed.
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind, UriCreationOptions& creationOptions)
at System.Uri..ctor(String uriString, UriKind uriKind)
at OpenIddict.Validation.AspNetCore.OpenIddictValidationAspNetCoreHandlers.ResolveRequestUri.HandleAsync(ProcessRequestContext context)
at OpenIddict.Validation.OpenIddictValidationDispatcher.DispatchAsync[TContext](TContext context)
at OpenIddict.Validation.OpenIddictValidationDispatcher.DispatchAsync[TContext](TContext context)
at OpenIddict.Validation.AspNetCore.OpenIddictValidationAspNetCoreHandler.HandleRequestAsync()
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Volo.Abp.AspNetCore.Security.AbpSecurityHeadersMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.<>c__DisplayClass6_1.<<UseMiddlewareInterface>b__1>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.HttpLogging.HttpLoggingMiddleware.InvokeInternal(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddlewareImpl.<Invoke>g__Awaited|8_0(ExceptionHandlerMiddlewareImpl middleware, HttpContext context, Task task)
Can you please share a demo or a sample code?
Hi @maliming
We are trying to go live and this mistake preventing us. We need urgent support.
Hi Anjali_Musmade,
You can find below:
{
"Settings": {
"Abp.Mailing.DefaultFromAddress": "noreply.information.myprojectname@mydomain.com",
"Abp.Mailing.DefaultFromDisplayName": "MyCompany myprojectname"
},
"App": {
"SelfUrl": "https://myprojectname-api.mydomain.com",
"AngularUrl": "https://myprojectname.mydomain.com",
"CorsOrigins": "https://*.myprojectname.com,http://localhost:4200,https://myprojectname.mydomain.com",
"RedirectAllowedUrls": "https://myprojectname.mydomain.com",
"DisablePII": "true",
"HealthCheckUrl": "/health-status"
},
"ConnectionStrings": {
"Default": "Host=mydb.mydomain.com;Port=5432;Database=myprojectname;User ID=myprojectname_app;Password=*******;"
},
"AuthServer": {
"Authority": "https://myprojectname-api.mydomain.com",
"RequireHttpsMetadata": "true",
"SwaggerClientId": "myprojectname_Swagger"
},
"StringEncryption": {
"DefaultPassPhrase": "*************"
}
}