public User GetUserByEmail(string email) { using (var conn = _sqlConnectionFactory.GetOpenAuthDatabaseSqlConnection()) { var user = conn.QueryFirstOrDefault <User>("SELECT SecurityUserId, SecurityUserGuid, RefreshTokenId, Email, PasswordHash FROM MarketAuth.tblSecurityUser WHERE Email = @Email", new { Email = email }); if (user == null) { return(null); } var claimValues = conn.Query <string>("SELECT DISTINCT c.Name FROM MarketAuth.tblSecurityUser u JOIN MarketAuth.tblSecurityRoleUser ur ON ur.SecurityUserId = u.SecurityUserId JOIN MarketAuth.tblSecurityRole r ON r.SecurityRoleId = ur.SecurityRoleId JOIN MarketAuth.tblSecurityRoleClaim rc ON rc.SecurityRoleId = r.SecurityRoleId JOIN MarketAuth.tblSecurityClaim c ON c.SecurityClaimId = rc.SecurityClaimId WHERE u.Email = @Email", new { Email = email }); user.SecurityClaims = new List <string>(claimValues); return(user); } }