public IEnumerable <UserDto> GetUsersByNameByRecursion([FromQuery] RequestUserByName model)
{
try
{
var foundUsers = _context.Users
.AsNoTracking()
.Include(c => c.UserContact)
.Include(t => t.Tags)
.Where(u => EF.Functions.Like(u.UserContact.FirstName, model.FirstName) ||
EF.Functions.Like(u.UserContact.LastName, model.LastName))
.ToList();
var result = new List <User>();
foreach (var user in foundUsers)
{
result.Add(user);
if (user.ManagerId != null)
{
result.AddRange(GetManager(user.ManagerId.Value));
}
}
var users = _mapper.Map <List <UserDto> >(result);
return(users);
}
catch (Exception e)
{
_logger.LogError(e.Message);
return(new List <UserDto>());
}
}
public IEnumerable <UserDto> GetUsersByNameByTree([FromQuery] RequestUserByName model)
{
try
{
var all = _context.Users.AsNoTracking().Include(c => c.UserContact).Include(t => t.Tags).ToList();
TreeExtensions.ITree <User> virtualRootNode = all.ToTree((parent, child) => child.ManagerId == parent.Id);
List <TreeExtensions.ITree <User> > rootLevelFoldersWithSubTree = virtualRootNode.Children.ToList();
List <TreeExtensions.ITree <User> > flattenedListOfFolderNodes = virtualRootNode.Children.Flatten(node => node.Children).ToList();
TreeExtensions.ITree <User> userNode = flattenedListOfFolderNodes.First(node => node.Data.UserContact.FirstName == model.FirstName || node.Data.UserContact.LastName == model.LastName);
List <User> usr = new List <User>();
usr.Add(userNode.Data);
usr.AddRange(GetParents(userNode));
//var users = _mapper.ProjectTo<UserDto>(usr.AsQueryable());
var users = _mapper.Map <List <UserDto> >(usr);
return(users);
}
catch (Exception e)
{
_logger.LogError(e.Message);
return(new List <UserDto>());
}
}
public IEnumerable <UserDto> GetUsersByNameBySqlRaw([FromQuery] RequestUserByName model)
{
try
{
if (!string.IsNullOrWhiteSpace(model.FirstName))
{
model.FirstName = $"{model.FirstName}%";
}
if (!string.IsNullOrWhiteSpace(model.LastName))
{
model.LastName = $"{model.LastName}%";
}
var rawQuery = $"WITH UserCTE\n" +
"AS\n" +
"( \n" +
"SELECT [u].[Id], [u].[CreatedDate], [u].[DeleteDate], [u].[Fired], [u].[ManagerId], [u0].[Address], [u0].[City], [u0].[Country], [u0].[DriverLicense], [u0].[Email], [u0].[FirstName], [u0].[LastName], [u0].[MobilePhone], [u0].[Phone], [u0].[SSN], [u0].[UserId]\n" +
"FROM [dbo].[Users] AS [u] LEFT JOIN [dbo].[UserContacts] AS [u0] ON [u0].UserId = [u].Id\n" +
$"WHERE [u0].FirstName LIKE N'{model.FirstName}' OR [u0].LastName LIKE N'{model.LastName}'\n" +
"UNION ALL\n" +
"SELECT [u].[Id], [u].[CreatedDate], [u].[DeleteDate], [u].[Fired], [u].[ManagerId], [u0].[Address], [u0].[City], [u0].[Country], [u0].[DriverLicense], [u0].[Email], [u0].[FirstName], [u0].[LastName], [u0].[MobilePhone], [u0].[Phone], [u0].[SSN], [u0].[UserId]\n" +
"FROM UserCTE AS M\n" +
"JOIN [dbo].[Users] AS [u] ON [u].Id = M.ManagerId\n" +
"JOIN [dbo].[UserContacts] AS [u0] ON [u0].UserId = [u].Id\n" +
")\n" +
"SELECT * FROM UserCTE\n";
var query = _context.Users.FromSqlRaw(rawQuery).ToList();
var users = _mapper.Map <List <UserDto> >(query);
return(users);
}
catch (Exception e)
{
_logger.LogError(e.Message);
return(new List <UserDto>());
}
}
