C#•7mo ago

Query improvement suggestions

    /// <summary>
    ///     Retrieves roles assigned to the user within organizations.
    /// </summary>
    /// <param name="userId">The ID of the user.</param>
    /// <param name="cancellationToken">The cancellation token.</param>
    /// <returns>The roles assigned to the user within organizations.</returns>
    private async Task<Dictionary<Guid, MemberRole>> GetOrganizationRolesAsync(Guid userId,
        CancellationToken cancellationToken)
    {
        // Get all organizations the user is a member of
        var userOrganizations = await db.OrganizationMembers
            .Where(om => om.UserId == userId)
            .Select(om => om.OrganizationId)
            .ToListAsync(cancellationToken);

        // Get roles in organizations where the user has a role
        var organizationRoles = await db.OrganizationMemberRoles
            .Where(omr => omr.OrganizationMember.UserId == userId)
            .Select(omr => new { omr.OrganizationId, omr.OrganizationRole.Name })
            .ToListAsync(cancellationToken);

        // Parse roles and add to dictionary
        var parsedRoles = organizationRoles.ToDictionary(
            omr => omr.OrganizationId,
            omr =>
            {
                if (Enum.TryParse<MemberRole>(omr.Name, true, out var role))
                    return role;

                throw new InvalidCastException();
            });

        // For organizations where the user doesn't have a role, add with MemberRole.None
        foreach (var orgId in userOrganizations) parsedRoles.TryAdd(orgId, MemberRole.None);

        return parsedRoles;
    }

    /// <summary>
    ///     Retrieves roles assigned to the user within organizations.
    /// </summary>
    /// <param name="userId">The ID of the user.</param>
    /// <param name="cancellationToken">The cancellation token.</param>
    /// <returns>The roles assigned to the user within organizations.</returns>
    private async Task<Dictionary<Guid, MemberRole>> GetOrganizationRolesAsync(Guid userId,
        CancellationToken cancellationToken)
    {
        // Get all organizations the user is a member of
        var userOrganizations = await db.OrganizationMembers
            .Where(om => om.UserId == userId)
            .Select(om => om.OrganizationId)
            .ToListAsync(cancellationToken);

        // Get roles in organizations where the user has a role
        var organizationRoles = await db.OrganizationMemberRoles
            .Where(omr => omr.OrganizationMember.UserId == userId)
            .Select(omr => new { omr.OrganizationId, omr.OrganizationRole.Name })
            .ToListAsync(cancellationToken);

        // Parse roles and add to dictionary
        var parsedRoles = organizationRoles.ToDictionary(
            omr => omr.OrganizationId,
            omr =>
            {
                if (Enum.TryParse<MemberRole>(omr.Name, true, out var role))
                    return role;

                throw new InvalidCastException();
            });

        // For organizations where the user doesn't have a role, add with MemberRole.None
        foreach (var orgId in userOrganizations) parsedRoles.TryAdd(orgId, MemberRole.None);

        return parsedRoles;
    }

Is there a way I can improve this query?

2 Replies

viceroypenguin•7mo ago

db.OrganizationMembers
  .Where(om => om.UserId == userId)
  .SelectMany(
    om => om.OrganizationMemberRoles
      .DefaultIfEmpty(),
    (om, omr) => new
    {
      om.OrganizationId,
      omr.OrganizationRole.Name,
    }
  )
  .ToListAsync();

db.OrganizationMembers
  .Where(om => om.UserId == userId)
  .SelectMany(
    om => om.OrganizationMemberRoles
      .DefaultIfEmpty(),
    (om, omr) => new
    {
      om.OrganizationId,
      omr.OrganizationRole.Name,
    }
  )
  .ToListAsync();

this shoudl work for you. you'll get a single list, with every organization that the user is a member of, along with the roles. if there are no roles, then Name will be null, adn you can react to that accordingily.

V0FBU1VMOP•7mo ago

@viceroypenguin | 🦋🐧🌟 Thanks for the insight. Any reasons as to why I'm getting this warning by rider Function is not convertible to SQL and must not be called in the database context. I got this, when I switched the query with the suggested one. I actually like yours better, there is no need for 2 queries.

Gaming

Programming

Query improvement suggestions