I need to run the following query on my DB context.
SELECT u.[Email],
u.[EmailConfirmed],
u.[PhoneNumber],
STRING_AGG(r.[Name], ', ') AS Roles
FROM [AspNetUsers] u
LEFT OUTER JOIN [AspNetUserRoles] ur ON ur.UserId = u.Id
LEFT OUTER JOIN [AspNetRoles] r ON r.Id = ur.RoleId
GROUP BY u.[Email], u.[EmailConfirmed], u.[PhoneNumber]
When I run this in the query window, I get four results. So I came up with the following code.
string UserQuery = @"SELECT u.[Email],
u.[EmailConfirmed],
u.[PhoneNumber],
STRING_AGG(r.[Name], ', ') AS Roles
FROM [AspNetUsers] u
LEFT OUTER JOIN [AspNetUserRoles] ur ON ur.UserId = u.Id
LEFT OUTER JOIN [AspNetRoles] r ON r.Id = ur.RoleId
GROUP BY u.[Email], u.[EmailConfirmed], u.[PhoneNumber]";
var conn = DbContext.Database.GetDbConnection();
using var cmd = conn.CreateCommand();
cmd.CommandText = UserQuery;
DbContext.Database.OpenConnection();
using var reader = cmd.ExecuteReader();
while (reader.NextResult())
{
Users.Add(new UserViewModel
{
Email = (string)reader["Email"],
EmailConfirmed = (bool)reader["EmailConfirmed"],
Phone = (string)reader["PhoneNumber"],
Role = (string)reader["Roles"]
});
}
This code runs without error, but the first call to reader.NextResult() returns false!
Can anyone see what I'm missing?
