Raw SQL queries in .NET Core controller methods for NotMapped properties

Question

I was up until 1am trying to figure out a major hurdle I'm having. I'm not finding anything online, or able to, come up with something that resolves it.

After getting some feedback from coworkers, the problem lies in needing a raw SQL map the user roles.

And this is where I'm stumped. I'm not terribly familiar with writing raw calls, so yep. If anyone has some direction on this, I'd appreciate it! This is my first post on StackOverflow, so I apologize if this isn't the best way to format my question

Also, is this level of SQL knowledge common / expected for a fresh jr .NET Core dev, with 7 months in the field?

It's related to the to my NotifyManagers and NotifyUsers properties being not mapped and nullable. I can't get them in the initial call for allClientUsers ( ? as far as I'm aware).

allClientUsers is returning a value for the list, but when it hits the next it keeps returning null.

This is all the pertinent information, to note, this is a .NET Core application.

if (entity.NotifyManagers is true || entity.NotifyUsers is true)
{
    List<ApplicationUser> allClientUsers = await DbContext.Users
                                .Where(x => x.ClientId == entity!.Dashboard!.ClientId)
                                .ToListAsync();

    // callCoachingManagers still returning null :(

    List<ApplicationUser> callCoachingManagers = allClientUsers
                                .Where(x => x.Roles != null && x.Roles.Contains("Call Coaching Manager"))
                                .ToList();
                            
    List<ApplicationUser> callCoachingUsers = allClientUsers
                                .Where(x => x.Roles!.Contains("Call Coaching User"))
                                .ToList();

    List<ApplicationUser> recipients = [];

    if (callCoachingManagers.Count != 0 && entity.NotifyManagers is true)
    {
        recipients.AddRange(callCoachingManagers);
    }

    if (callCoachingUsers.Count != 0 && entity.NotifyUsers is true)
    {
        recipients.AddRange(callCoachingUsers);
    }
}

[NotMapped]
public List<string>? Roles { get; set; }

A similar call in the users controller

string[] arrayOfIds = [.. entities.Select(x => x.Id)];
string? connectionString = Configuration.GetConnectionString("DefaultConnection");

List<UserRoleMapping> userRoleMappings = [];

string sqlQuery = "SELECT u.Id AS UserId, STRING_AGG(r.Name, ', ') AS Roles " +
                  "FROM AspNetUsers u " +
                  "JOIN AspNetUserRoles ur ON u.Id = ur.UserId " +
                  "JOIN AspNetRoles r ON ur.RoleId = r.Id " +
                       (filter ? " And u.Id in @ids " : "") +
                  "GROUP BY u.id";

using (SqlConnection sqlConnection = new(connectionString))
{
    userRoleMappings = sqlConnection.Query<UserRoleMapping>(sqlQuery, filter ? new { ids = arrayOfIds } : null).ToList();
}

entities = (from entity in entities.ToList()
            join mapping in userRoleMappings on entity.Id equals mapping.UserId into g
            from x in g.DefaultIfEmpty()
            select new ApplicationUser
                       {
                            Active = entity.Active,
                            Client = entity.Client,
                            ClientId = entity.ClientId,
                            ClientIds = entity.ClientIds,
                            DateCreated = entity.DateCreated,
                            DateModified = entity.DateModified,
                            Email = entity.Email,
                            FirstName = entity.FirstName,
                            Id = entity.Id,
                            LastName = entity.LastName,
                            LoginCount = entity.LoginCount,
                            LoginDate = entity.LoginDate,
                            RoleList = x == null ? string.Empty : x.Roles
                        })
                       .OrderBy(x => x.FirstName).ThenBy(x => x.LastName)
                       .AsQueryable();

Let me know if there is anything else needed to make sense of this garbage

Answer 1

[NotMapped]: This is a data annotation that indicates that a property or class should be excluded from database mapping, which tells EF to ignore the roles attribute when generating SQL queries.

Therefore, if you need to get the data of roles in the database, you need to manually write SQL queries to control data access. You can refer to this document: https://learn.microsoft.com/en-us/ef/ef6/querying/raw-sql.