- Abp 6.4
- Angular
- .NET 5
Hi support team,
I'm currently focusing on EF queries optimization, specially for read only queries. I realized that EF queries like this one :
await _entityRepository.getAll().Include(e => e.NestedEntity).OrderBy(input.Sorting).PageBy(input).ToListAsync()
is causing database delays because SQL engine tries to get every properties from entity (event more if a nested entity is included in the query).
After some tests, I achieve a query 4x times faster by selecting only needed entity properties like that :
await _entityRepository.getAll().Include(e => e.NestedEntity).Select(e => new EntityDto { Id = e.Id, Description = e.Description}).OrderBy(input.Sorting).PageBy(input).ToListAsync()
As you can see, I'm using a DTO (EntityDto) to map from Entity object (this is called "projection"). My goal is to reuse all existing DTO's to optimize all select queries ; BUT I'm doing the mapping manually, which can cause errors in the future (ex : if a new property is added to the DTO). I need this to be more maintainable and optimized.
Do you have a solution to automatically map the DTO in the SELECT statement of the query ?
3 Answer(s)
-
0
Hi,
After some research on ABP github repo, I found that automapper projection is already available by using :
var dtos = await ObjectMapper.ProjectTo<EntityDto>(entities) .OrderBy(input.Sorting) .PageBy(input) .ToListAsync();The generated SQL is better ; only needed properties are available. This seems to be better also in performance but as fast as my first tests.
@support_team : do you agree that object projection before querying is better than mapping objects after querying ? If so, you should add a note in the docs.
-
0
Hi @ricavir
Please follow https://github.com/aspnetzero/aspnet-zero-core/issues/4274, we will work on this issue to idendify the problem.
do you agree that object projection before querying is better than mapping objects after querying ?
Most of the times yes, but this is a common concept, so I think, we shouldn't add this to our documentation. It might be a misleading suggestion for some others.
-
0
Hi @ismcagdas,
Tks for pointing out this issue. I already noticed better performance with projectoin before querrying so that means it will be even better after this resolution.
