Building a DropDown in MVC

I have an self-referencing entity which maintains a list of like entities that are it’s children. What I need to do is build a dropdown that lists all possible parents so when I add or edit a new entity of that type, I can select it’s parent (or no parent). The problem lies in the sql. I simply don’t know how to word it.

IList<ParentForumModel> parentForums = new List<ParentForumsModel>();

foreach (Forum forum in forumRepository.GetAll())
{
// not sure how to handle this here because it isn’t in the correct order. I need them to be grouped by parent and sorted by the sequence field. It also needs to be a flat dataset, not something uses IGroupedQueryable or some such.
}

ForumAddModel model = new ForumAddModel
{
ParentForums = parentForums,
// other properties
}


public IQueryable<Forum> GetAll()
{
return context.Forums.??? // what Linq do I need here?
}

If this simply isn’t possible with Linq2Sql, then is there a system where it is possible? Please don’t say hnibernate…I hate nhibernate…

I not getting what you want to do? you want to build the “Tree” of forums from a flat list?

like this:
forum 1
…|_ forum 2
…|_ forum 3
…|forum 4
forum 5
…|
forum 6

or you have the tree and need to flat it out?

cheers,
rui

Very close (except that I don’t plan to indent per level). I know how to build the actual display. What I don’t know is the correct Linq needed to get the results I want, and in a flat list.

Your “tree” above is an accurate representation of what I want to display, but the closest I can get (in terms of l2s) is:

IQueryable<IGrouping<int,Forum>> forumTree = venueContext.Forums.OrderBy(x => x.Sequence).GroupBy(x => x.ParentId);

But what I want is a flat list, not a nested result. And I’m not even sure the result of the above is correct. Each forum has a Sequence field, so if “Forum 3” is set to appear before “Forum 2” (in your tree), the results need to be in that order.

It needs to be a flat list so I can simply pass it into a SelectList constructor.

No “real” alternative in LinqToSql…

here is a thread with a similar problem…
http://www.mail-archive.com/advanced-dotnet@discuss.develop.com/msg14172.html

Recursive Queries Using Common Table Expressions

Ok… that only works for one nested level.
It has to be recursive…

select a category then select its childs and for each child do the same thing -> select a category then select its childs.

I know how to do this with T-SQL, T-SQL has a specials constructs for building recursive queries.

The full code of my example made on LinqPad…


void Main()
{
 var categories = new List<Category>{
  new Category { Id = 1, Name = "Cat 1", Sequence = 1 },
  new Category { Id = 2, Name = "Cat 2", Sequence = 2, ParentId = 1 },
  new Category { Id = 3, Name = "Cat 3", Sequence = 1, ParentId = 1 },
  new Category { Id = 4, Name = "Cat 4", Sequence = 2 },
  new Category { Id = 5, Name = "Cat 5", Sequence = 3 },
  new Category { Id = 6, Name = "Cat 6", Sequence = 4 },
  new Category { Id = 7, Name = "Cat 7", Sequence = 1, ParentId = 5 },
  new Category { Id = 8, Name = "Cat 8", Sequence = 3, ParentId = 5 },
  new Category { Id = 9, Name = "Cat 9", Sequence = 2, ParentId = 5 },
  new Category { Id = 10, Name = "Cat 10", Sequence = 9 },
  new Category { Id = 11, Name = "Cat 11", Sequence = 10 },
  new Category { Id = 12, Name = "Cat 12", Sequence = 11 }
 };
 
 var xpto = categories.SelectMany(c => categories.Where(parent => parent.Id == c.Id && parent.ParentId == null).Concat(categories.Where(child => child.ParentId == c.Id).OrderBy(child => child.Sequence)));
 
 xpto.Dump();
}
 
// Define other methods and classes here
public class Category
{
 public int Id { get; set; }
 public string Name { get; set; }
 public int? ParentId { get; set; }
 public int Sequence { get; set; }
}
 

maybe its this :stuck_out_tongue:


var xpto = categories.SelectMany(c => categories.Where(parent => parent.Id == c.Id && parent.ParentId == null).Concat(categories.Where(child => child.ParentId == c.Id).OrderBy(child => child.Sequence)));


Thanks a great deal for the attention you gave this pufa. I’ll play around with your advice in mind. I too came to the conclusion the the original linq wasn’t going to work because I couldn’t use a single foreach to process it.

There are several places in the app where this scenario raises it’s head, and I am real temped at this point to eliminate the parenting aspect of things. Though this would require a concrete Category entity to house individual forums, and a Thread entity to house the individual Posts. Not a real hard thing to do, but it would save a lot of headache on my part.

Part of the ideology I adopted with this project is simplicity (the K.I.S.S principle). This was too include database design, coding, UI design and user experience. I’m not sure it really needs multiple levels of forum nesting or the ability to show a thread in true “threaded” mode. Linear works just fine, while paging and cutoff dates keep the forums slim.

That’s my backup plan anyway.

Then I think you only need to order by parent and then by sequence

cheers,
rui