C#•3w ago

✅ Ef-core query efficiency?

I have a question regarding querying data. Which one is more efficient since I only need the department ids. The first one

    private async Task<OrganizationMemberDTO?> RetrieveMemberAsync(CancellationToken ct)
    {
        return await db.OrganizationMembers
            .AsNoTracking()
            .Where(e => e.Id == MemberId && e.OrganizationId == OrganizationId)
            .Select(e => new OrganizationMemberDTO
            {
                Id = e.Id,
                Position = e.Position,
                Departments = e.Departments.Select(d => d.DepartmentId)
            }).FirstOrDefaultAsync(ct);
    }

    private async Task<OrganizationMemberDTO?> RetrieveMemberAsync(CancellationToken ct)
    {
        return await db.OrganizationMembers
            .AsNoTracking()
            .Where(e => e.Id == MemberId && e.OrganizationId == OrganizationId)
            .Select(e => new OrganizationMemberDTO
            {
                Id = e.Id,
                Position = e.Position,
                Departments = e.Departments.Select(d => d.DepartmentId)
            }).FirstOrDefaultAsync(ct);
    }

or second one

    private async Task<OrganizationMember?> RetrieveMemberAsync(CancellationToken ct)
    {
        return await db.OrganizationMembers
            .AsNoTracking()
            .Include(e => e.Departments)
            .FirstOrDefaultAsync(e => e.Id == MemberId && e.OrganizationId == OrganizationId, ct);
    }

    private async Task<OrganizationMember?> RetrieveMemberAsync(CancellationToken ct)
    {
        return await db.OrganizationMembers
            .AsNoTracking()
            .Include(e => e.Departments)
            .FirstOrDefaultAsync(e => e.Id == MemberId && e.OrganizationId == OrganizationId, ct);
    }

15 Replies

Core•3w ago

The first one, since you only select specific fields. Note that in the first statement AsNoTracking is redundant, an entity is not tracked when Select is used

WAASULOP•3w ago

Good to hear. Thanks for the input mate.

Angius•3w ago

First one hands down The second one will just load everything, including the related data So, why

WAASULOP•3w ago

I see, when I write queries like this, it also means the database selects the properties I want right?

Angius•3w ago

Yep You can check what exact query EF will generate

Core•3w ago

yes, EF will log the translated SQL queries to the console if the log level is Information

WAASULOP•3w ago

Ok, glad to hear. Thank you very much guys. Cheers birdyes

WAASULOP•3w ago

@Angius Sorry for pinging you, how come the log looks like this

 Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@__MemberId_0='?' (DbType = Int32), @__OrganizationId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT o."Id", o."Archived", o."CreatedAt", o."FullName", o."InvitationStatus", o."InvitationToken", o."NormalizedFullName", o."OrganizationId", o."Position", o."Role", o."UpdatedAt", o."UserId"
      FROM "OrganizationMembers" AS o
      WHERE o."Id" = @__MemberId_0 AND o."OrganizationId" = @__OrganizationId_1
      LIMIT 1

 Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@__MemberId_0='?' (DbType = Int32), @__OrganizationId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT o."Id", o."Archived", o."CreatedAt", o."FullName", o."InvitationStatus", o."InvitationToken", o."NormalizedFullName", o."OrganizationId", o."Position", o."Role", o."UpdatedAt", o."UserId"
      FROM "OrganizationMembers" AS o
      WHERE o."Id" = @__MemberId_0 AND o."OrganizationId" = @__OrganizationId_1
      LIMIT 1

Angius•3w ago

From which query?

WAASULOP•3w ago

the first one.

    private async Task<OrganizationMemberDTO?> RetrieveMemberAsync(CancellationToken ct)
    {
        return await db.OrganizationMembers
            .Where(e => e.Id == MemberId && e.OrganizationId == OrganizationId)
            .Select(e => MapToDto(e))
            .FirstOrDefaultAsync(ct);
    }

    private static OrganizationMemberDTO MapToDto(OrganizationMember member)
    {
        return new OrganizationMemberDTO
        {
            Id = member.Id,
            Position = member.Position,
            Departments = member.Departments.Select(d => d.DepartmentId)
        };
    }

    private async Task<OrganizationMemberDTO?> RetrieveMemberAsync(CancellationToken ct)
    {
        return await db.OrganizationMembers
            .Where(e => e.Id == MemberId && e.OrganizationId == OrganizationId)
            .Select(e => MapToDto(e))
            .FirstOrDefaultAsync(ct);
    }

    private static OrganizationMemberDTO MapToDto(OrganizationMember member)
    {
        return new OrganizationMemberDTO
        {
            Id = member.Id,
            Position = member.Position,
            Departments = member.Departments.Select(d => d.DepartmentId)
        };
    }

Angius•3w ago

Ah Probably because of your use of this method Try doing the transformation into a DTO directly in that Select

WAASULOP•3w ago

Ok let me try it.

Angius•3w ago

Alternativaly, use an Expression<Func<TSource, TTarget>>

private static Expression<Func<OrganizationMember, OrganizationMemberDto>> MapToDto = member => new OrganizationMemberDto {
    // map the props
};

private static Expression<Func<OrganizationMember, OrganizationMemberDto>> MapToDto = member => new OrganizationMemberDto {
    // map the props
};

And pass that to .Select Or use something like this: https://github.com/ClaveConsulting/Expressionify

WAASULOP•3w ago

 Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (81ms) [Parameters=[@__MemberId_0='?' (DbType = Int32), @__OrganizationId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT o0."Id", o0."Position", d."DepartmentId", d."Id"
      FROM (
          SELECT o."Id", o."Position"
          FROM "OrganizationMembers" AS o
          WHERE o."Id" = @__MemberId_0 AND o."OrganizationId" = @__OrganizationId_1
          LIMIT 1
      ) AS o0
      LEFT JOIN "DepartmentMembers" AS d ON o0."Id" = d."MemberId"
      ORDER BY o0."Id"

 Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (81ms) [Parameters=[@__MemberId_0='?' (DbType = Int32), @__OrganizationId_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT o0."Id", o0."Position", d."DepartmentId", d."Id"
      FROM (
          SELECT o."Id", o."Position"
          FROM "OrganizationMembers" AS o
          WHERE o."Id" = @__MemberId_0 AND o."OrganizationId" = @__OrganizationId_1
          LIMIT 1
      ) AS o0
      LEFT JOIN "DepartmentMembers" AS d ON o0."Id" = d."MemberId"
      ORDER BY o0."Id"

Yeah that solved it. Thank you very much. I had no Idea that MapToDto was the issue.

Angius•3w ago

:Ok:

Gaming

Programming

✅ Ef-core query efficiency?