The following dataset is available:

var Data = new[] { new Data{Id = 1, ParentId = 0, Value = "value1"}, new Data{Id = 2, ParentId = 1, Value = "value1"}, new Data{Id = 3, ParentId = 1, Value = "value3"}, new Data{Id = 4, ParentId = 3, Value = "value4"} };

and the method that needs to be implemented in such a way that, given Id, I could get a list of all its ParentId and preferably Value

Spit time:

 Dictionary<int, string> dict = new Dictionary<int, string>(); public void AllParents(IEnumerable<Data> Data, int id) { var parent = Data.First(x=>x.Id==id); dict.Add(parent.Id, parent.Value); if(parent.ParentId == 0) return; AllParents(Data, parent.ParentId); } AllParents(Data, 4); AllParents(Data, 2); +---------+---------+ +----------+---------+ |Key |Value | | Key | Value | |---------|---------| |----------|---------| |4 |value4 | | 2 | value2 | |3 |value3 | | 1 | value1 | |1 |value1 | | | | | | | | | | | | | | | | +---------+---------+ +----------+---------+

But how to do the same if the data is in a table in the database? Splitting one table into two or changing the data structure in any other way is the only way out?

  • You can use LinqToSql or EntityFramework there also support linq. You will receive the Data parameter in the same form, only from the database. AllParents function will remain unchanged. - UserTest
  • all this is clear, but I will not recursively refer to the database, or do you advise you to pull all the data out of the database, convert it into objects and then work with it? not an option - Specter
  • There is a variant with minimal changes to the database structure - creation of a stored procedure, which in batch mode (without recursion) forms a temporary table in key / value format - UserTest
  • NHibernate + Fluent NHibernate is another alternative to EF. - Costantino Rupert

1 answer 1

There is a variant on the example of the Entity Framework:

 private void AllParents(int id, int maxLevel) { using (TestDbEntities context = new TestDbEntities()) { var query = context.Data .Where(d => d.Id == id); for (int i = 2; i <= maxLevel; i++) { query = query.Union( context.Data .Join(query.Select(d => d.ParentId), d => d.Id, pId => pId, (d, pId) => d)); } dict = query.ToDictionary(d => d.Id, d => d.Value); } }

The point is that a single query is generated based on the depth of the search. For your examples with id = 4 and id = 2 and search depth maxLevel = 3, the results are similar. Plus solutions are that all data is obtained in one request. The downside is that this query is not frail, and there is a hard limit on the depth of the search.

  • Yeah-hh, the query and the truth is not quite frail, but an increase in the depth of the search complicates it exponentially, with 10 the query was executed for 8 seconds, and its appearance caused fear and horror! But still, thank you so much, although I think it will be necessary to change the structure of the table =) - Specter