Complex query in order (entity structure)

Question

Okay, so I’ll start by saying that I am new to all this and I’m doing my best to work on this project. I have an employee object that contains a supervisor field. When someone enters a search on my page, the datagrid displays employees whose name matches the search. But I need them to display all employees who report to them, and third-level employees who report to subordinate employees. I need only three levels. To make this easier, employees only come in 3 categories, therefore, if rank == 3, this employee is not responsible for others. I suggest that the best way to retrieve all of these employees from my employee table would be something like

from employee in context.employees where employee.name == search || employee.boss.name == search || employee.boss.boss.name == search 

But I'm not sure how to make the order look the way I want. I need it to appear in tiers. So, it will look like this: Big Boss- Boss- underling- underling- Boss- underling- Boss- Boss- Big Boss -

As I said, there may be an easier way to approach this problem, and if there is, I’m all ears. Any advice you can give would be greatly appreciated.

Answer 1

This seems like a difficult requirement to solve using any particular ORM structure in at least one simple step. A multi-step process is likely to be required.

Something similar can be achieved using an approach to repeating the search results and searching for their children (and children-children) and aligning the hierarchy into one list. An implementation example here, this is done using a simple list in memory:

 class Employee { public int Id { get; private set; } public int? BossId { get; private set; } public string Name { get; private set; } public Employee(int id, int? bossId, string name) { Id = id; BossId = bossId; Name = name; } } 

Sample data:

 List<Employee> employees = new List<Employee>(); employees.Add(new Employee(1, null, "Tom Smith")); employees.Add(new Employee(2, null, "Susan Jones")); employees.Add(new Employee(3, 1, "Sally Davis")); employees.Add(new Employee(4, 1, "Robert Roberts")); employees.Add(new Employee(5, 3, "John Smith")); employees.Add(new Employee(6, 2, "Tonya Little")); employees.Add(new Employee(7, 3, "Ty Bell")); employees.Add(new Employee(8, 4, "Helen Andrews")); employees.Add(new Employee(9, 2, "Matt Huang")); employees.Add(new Employee(10, 6, "Lisa Wilson")); 

Process:

 string searchTerm = "Smith"; var searchResults = employees.Where(e => e.Name.Contains(searchTerm)); List<Employee> outputList = new List<Employee>(); Action<IEnumerable<Employee>, List<Employee>> findUnderlings = null; findUnderlings = (input, list) => { foreach (Employee employee in input) { list.Add(employee); var underlings = employees.Where(e => e.BossId == employee.Id); findUnderlings(underlings, list); } }; findUnderlings(searchResults, outputList); 

Show output:

 foreach (Employee employee in outputList) { Console.WriteLine("{0}\t{1}\t{2}", employee.Id, employee.Name, employee.BossId); } 

Results:

 1 Tom Smith 3 Sally Davis 1 5 John Smith 3 7 Ty Bell 3 4 Robert Roberts 1 8 Helen Andrews 4 5 John Smith 3 

And you can see that this follows the top result, subversion, subordination of subordinates, next result, any subordinates, etc. It works for any number of levels.

I'm not sure how this can be done in “order” in Linq or even in plain SQL, but this may mean that I'm not smart enough to do this, and not just.

Answer 2

Hey, I thought I would post as I decided to solve this problem, if it could be useful to someone else.

 var list = productQuery.ToList(); var productList = Functions.sortResultsList(list); public static List<SolutionsModel.Version> sortResultsList(List<SolutionsModel.Version> list) { var productList = new List<SolutionsModel.Version>(); int total = list.Count(); int solutions = 0; int objects = 0; for (int length = 0; length < list.Count(); length++) { if (list[length].Product.TypeID == 1) { ++solutions; } else if (list[length].Product.TypeID == 2) { ++objects; } } //These nested for-loops create a list that is //correctly ordered to fit correctly into the grid. //Perhaps consider more efficient improvision at a later time. //These for loops can't be used if there are not any solutions //in the results if (solutions != 0) { for (int x = 0; x < list.Count; x++) { if (list[x].Product.TypeID == 1) { productList.Add(list[x]); for (int y = 0; y < list.Count; y++) { if (list[y].Product.TypeID != 1) { if (list[y].Product.Product2.ID == list[x].Product.ID && list[y].VersionNumber == list[x].VersionNumber) { productList.Add(list[y]); for (int j = 0; j < list.Count; j++) { if (list[j].Product.TypeID == 3) { if (list[j].Product.Product2.ID == list[y].Product.ID && list[j].VersionNumber == list[y].VersionNumber) { productList.Add(list[j]); } } } } } } } } } //This can't be used if the results do not contain any objects. if (objects != 0 && productList.Count != total) { for (int y = 0; y < list.Count; y++) { if (list[y].Product.TypeID == 2) { productList.Add(list[y]); for (int j = 0; j < list.Count; j++) { if (list[j].Product.TypeID == 3) { if (list[j].Product.Product2.ID == list[y].productID && list[j].VersionNumber == list[y].VersionNumber) { productList.Add(list[j]); } } } } } } //If the results contain only modules, no sorting is required and the original list can be used. if (productList.Count != total) { return list; } return productList; } 

Answer 3

I don't know much about LINQ. But why can't this be done using a stored procedure that can be attributed to your EF model?

I think you can’t use LINQ for everything :)

EDIT: the reason why I say that using a stored procedure makes sense is because EF will have to generate an SQL query to do what you want and what you want to do can be better expressed and controlled in SQL