User Repository Performance Problem

[behiunforgiven]

I want to select only some properties of the User repository.
with this repository (I have added some extra properties to the User class)

public class MiniUserDto : EntityDto<long>
{
    public string FullName { get; set; }
    public string ActiveUnitChart { get; set; }
    public string Image { get; set; }
}

so if I want to select these properties from User with this query (or every other query)

var users = _userRepository.GetAll().OrderByDescending(x => x.Score).Take(5);
return new ListResultDto<MiniUserDto>
{
    Items = users.ProjectTo<MiniUserDto>(_mapper.ConfigurationProvider).ToList()
};

in the SQL profiler, all the User (AbpUser) Table columns are in the logged TextData.

I tried so many ways:
Automapper config in many ways
Using IQueriable.Select()
, etc.

but every relation to the User Entity produces a query that includes all the columns.
this problem does not exist for other entities.

[ismcagdas]

This is a common LinQ question. You can use .Select method to select fields you want, have you tried that ? You can take a look at https://www.brentozar.com/archive/2016/09/select-specific-columns-entity-framework-query/ for a sample.

[behiunforgiven]

I searched a lot before I posted the issue, I tried (Select) solution but SQL Profiler shows the same result.
In both situations (Direct select or Relational Select) SQL Profiler shows that the generated SQL query contains all the columns.

[ismcagdas]

Could you share your final query with Select statement ? I can take a deeper look into this.

[behiunforgiven]

this is the generated sql query for entity framework query above:

SELECT TOP(@__p_0) [a].[Id], [a].[AccessFailedCount], [a].[AccessLevel], [a].[ActiveUnitChart], [a].[ActiveUnitChartId], [a].[AuthenticationSource], [a].[ConcurrencyStamp], [a].[CreationTime], [a].[CreatorUserId], [a].[DeleterUserId], [a].[DeletionTime], [a].[Education], [a].[EmailAddress], [a].[EmailConfirmationCode], [a].[ExtensionData], [a].[Image], [a].[IsActive], [a].[IsDeleted], [a].[IsEmailConfirmed], [a].[IsLockoutEnabled], [a].[IsPhoneNumberConfirmed], [a].[IsTwoFactorEnabled], [a].[LastModificationTime], [a].[LastModifierUserId], [a].[LastPasswordChangedDate], [a].[LockoutEndDateUtc], [a].[Name], [a].[NormalizedEmailAddress], [a].[NormalizedUserName], [a].[Password], [a].[PasswordResetCode], [a].[PersonelCode], [a].[Phone], [a].[PhoneNumber], [a].[Resume], [a].[Score], [a].[SecurityStamp], [a].[Surname], [a].[TenantId], [a].[UserName]
      FROM [AbpUsers] AS [a]
      WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [a].[IsDeleted] = CAST(0 AS bit)) AND (@__ef_filter__p_1 = CAST(1 AS bit) OR [a].[TenantId] = @__ef_filter__CurrentTenantId_2)
      ORDER BY [a].[Score] DESC

and this is result by Select:

var users = _userRepository.GetAll().OrderByDescending(x => x.Score).Take(5).Select(x => new MiniUserDto
            {
                Id = x.Id,
                FullName = x.FullName,
                Image = x.Image,
                ActiveUnitChart = x.ActiveUnitChart
            });

SELECT TOP(@__p_0) [a].[Id], [a].[AccessFailedCount], [a].[AccessLevel], [a].[ActiveUnitChart], [a].[ActiveUnitChartId], [a].[AuthenticationSource], [a].[ConcurrencyStamp], [a].[CreationTime], [a].[CreatorUserId], [a].[DeleterUserId], [a].[DeletionTime], [a].[Education], [a].[EmailAddress], [a].[EmailConfirmationCode], [a].[ExtensionData], [a].[Image], [a].[IsActive], [a].[IsDeleted], [a].[IsEmailConfirmed], [a].[IsLockoutEnabled], [a].[IsPhoneNumberConfirmed], [a].[IsTwoFactorEnabled], [a].[LastModificationTime], [a].[LastModifierUserId], [a].[LastPasswordChangedDate], [a].[LockoutEndDateUtc], [a].[Name], [a].[NormalizedEmailAddress], [a].[NormalizedUserName], [a].[Password], [a].[PasswordResetCode], [a].[PersonelCode], [a].[Phone], [a].[PhoneNumber], [a].[Resume], [a].[Score], [a].[SecurityStamp], [a].[Surname], [a].[TenantId], [a].[UserName]
      FROM [AbpUsers] AS [a]
      WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [a].[IsDeleted] = CAST(0 AS bit)) AND (@__ef_filter__p_1 = CAST(1 AS bit) OR [a].[TenantId] = @__ef_filter__CurrentTenantId_2)
      ORDER BY [a].[Score] DESC```

[behiunforgiven]

and if an Entity has a relation to the Users table
e.g.

....
public virtual User CreatorUser { get; set; }
....

every query that a refers to the Users table, causes all the columns to be in the generated query:

[AutoMapFrom(typeof(Lesson))]
    public class MiniLessonDto : EntityDto<long>
    {
        public MiniUserDto CreatorUser { get; set; }
        public string Title { get; set; }
    }

public ListResultDto<MiniLessonDto> GetLatestLessons()
        {
            var lessons = _lessonRepository.GetAll().AsNoTracking().Where(x => x.CurrentState == LessonState.Published).OrderByDescending(x => x.PublishDate).Take(5);

            return new ListResultDto<MiniLessonDto>
            {
                Items = lessons.ProjectTo<MiniLessonDto>(_mapper.ConfigurationProvider).ToList()
            };
        }

Generated SQL:

SELECT CASE
          WHEN [t0].[Id] IS NULL THEN CAST(1 AS bit)
          ELSE CAST(0 AS bit)
      END, [t0].[Id], [t0].[AccessFailedCount], [t0].[AccessLevel], [t0].[ActiveUnitChart], [t0].[ActiveUnitChartId], [t0].[AuthenticationSource], [t0].[ConcurrencyStamp], [t0].[CreationTime], [t0].[CreatorUserId], [t0].[DeleterUserId], [t0].[DeletionTime], [t0].[Education], [t0].[EmailAddress], [t0].[EmailConfirmationCode], [t0].[ExtensionData], [t0].[Image], [t0].[IsActive], [t0].[IsDeleted], [t0].[IsEmailConfirmed], [t0].[IsLockoutEnabled], [t0].[IsPhoneNumberConfirmed], [t0].[IsTwoFactorEnabled], [t0].[LastModificationTime], [t0].[LastModifierUserId], [t0].[LastPasswordChangedDate], [t0].[LockoutEndDateUtc], [t0].[Name], [t0].[NormalizedEmailAddress], [t0].[NormalizedUserName], [t0].[Password], [t0].[PasswordResetCode], [t0].[PersonelCode], [t0].[Phone], [t0].[PhoneNumber], [t0].[Resume], [t0].[Score], [t0].[SecurityStamp], [t0].[Surname], [t0].[TenantId], [t0].[UserName], [t].[Title], [t].[Id]
      FROM (
          SELECT TOP(@__p_0) [l].[Id], [l].[CreatorUserId], [l].[PublishDate], [l].[Title]
          FROM [Lessons] AS [l]
          WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [l].[IsDeleted] = CAST(0 AS bit)) AND [l].[CurrentState] = 3
          ORDER BY [l].[PublishDate] DESC
      ) AS [t]
      LEFT JOIN (
          SELECT [a].[Id], [a].[AccessFailedCount], [a].[AccessLevel], [a].[ActiveUnitChart], [a].[ActiveUnitChartId], [a].[AuthenticationSource], [a].[ConcurrencyStamp], [a].[CreationTime], [a].[CreatorUserId], [a].[DeleterUserId], [a].[DeletionTime], [a].[Education], [a].[EmailAddress], [a].[EmailConfirmationCode], [a].[ExtensionData], [a].[Image], [a].[IsActive], [a].[IsDeleted], [a].[IsEmailConfirmed], [a].[IsLockoutEnabled], [a].[IsPhoneNumberConfirmed], [a].[IsTwoFactorEnabled], [a].[LastModificationTime], [a].[LastModifierUserId], [a].[LastPasswordChangedDate], [a].[LockoutEndDateUtc], [a].[Name], [a].[NormalizedEmailAddress], [a].[NormalizedUserName], [a].[Password], [a].[PasswordResetCode], [a].[PersonelCode], [a].[Phone], [a].[PhoneNumber], [a].[Resume], [a].[Score], [a].[SecurityStamp], [a].[Surname], [a].[TenantId], [a].[UserName]
          FROM [AbpUsers] AS [a]
          WHERE (@__ef_filter__p_1 = CAST(1 AS bit) OR [a].[IsDeleted] = CAST(0 AS bit)) AND (@__ef_filter__p_2 = CAST(1 AS bit) OR [a].[TenantId] = @__ef_filter__CurrentTenantId_3)
      ) AS [t0] ON [t].[CreatorUserId] = [t0].[Id]
      ORDER BY [t].[PublishDate] DESC

[ismcagdas]

@behiunforgiven I tested this case and it seems like this problem happens when you select FullName field of User entity. I assume, since FullName is not a mapped field but a calculated field, EF Core queries all fields. I suggest you to exclude FullName field from your query and add it to your Dto and query Name and Surname fields separetaly.

https://github.com/aspnetboilerplate/aspnetboilerplate/blob/dev/src/Abp.Zero.Common/Authorization/Users/AbpUserBase.cs#L126

[behiunforgiven]

As you see above, the FullName is in my DTO, should I ignore it in CreatingMaP Profile?
which part should I change in this section?
I removed FullName from DTO and nothing changed.

#6849 (comment)

[ismcagdas]

I mean something like this;

_userRepository.GetAll().OrderByDescending(x => x.Score).Take(5).Select(x=> {
Id = x.Id,
Image = x.Image,
ActiveUnitChart = x.ActiveUnitChart
FirstName = x.FirstName,
LastName = x.LastName
}).ToList();

As you can see, FullName is not selected, instead FirstName and LastName are selected. You can use those fields to set FullName in your Dto after executing the query.

[behiunforgiven]

OK, I had another calculated field in User Entity, I removed it and the problem seems to be solved in some places. (still entities with User Entity relation have problem)
also, I removed FullName from my DTO

so, is this an Abp problem or lack of my knowledge?

[ismcagdas]

It is not related to ABP. I'm not %100 sure but I guess, EF Core does this.