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>()); } }