Dave has been
posting some good stuff relating to a project we're working on right now that leverages
SubSonic in the data tier with
MVC providing all the shiny goodness in the web tier. SubSonic has been an excellent code generator for our DAL and handles somewhere between 75-90% of the cases for our data schema. Specifically it hasn't liked multiple inner joins with attribute queries that include more than one of the join tables. Secondly, it's not real good about self-referencing (or recursive) foreign keys on tables.
The Data
I dealt with the inner join issue in a previous post
here. But let's consider the recursive foreign key issue. Consider a portion of our data model that specifies which user roles get which application menus when they log in. It looks something like this:
Data Model
Note that the menu itself, and all children are Menu_Item rows in the data schema. Relationships are enforced using the Parent_ID attribute. Getting the nested structure out in a straightforward manner is really a fools errand with the SubSonic API. The one option for using SubSonic would be to create a
recursive CTE as a stored proc, certainly not a tall order, and call it using SubSonic's StoreProcedure object. But that still won't get you a set of objects; the tree-like relationship of menu items is still flat. What we'd like is to be able to efficiently get an object or IList of objects with the children already nested so that building the Menus in an MVC controller is more straightforward.
Just Get the Menus Please
What I elected to do is just fetch a distinct list of all menu items, ignoring parent/child relationships with a SubSonic API call, and then let LINQ handle building an object graph that traverses the recursive relate. Keeping in mind that SubSonic's SqlQuery object doesn't support DISTINCT queries, the code looks something like the following:
//current SubSonic does not support Distinct on SqlQuery object
//so we need to get the full list and do distinct using Linq
IList<DAL.Menu_Item> menuList = DB.Select("*").From<Roles_MenuItems>().
InnerJoin<Menu_Item>().
Where(Roles_MenuItems.Role_IdColumn).
In(roleIds).
ExecuteTypedList<DAL.Menu_Item>();
//Distinct doesn't work with individual object's GetHashCode comparer
//so we need a little work around here to group the menus by ID and then select
//the first object in each grouping...that get's us our distinct
if (menuList != null && menuList.Count > 0)
{
var distinctMenus = menuList.GroupBy(x => x.Menu_Item_Id).Select(x => x.First()); }
LINQ Recursion Smackdown
Now that I'm equipped with a list of distinct menu items the user is allowed to see, I can use a nifty little LINQ recursion extension method that I found over on
Stephan Cruysberghs blog. Just grab his code and slap it into a new class in your project or your favorite common assembly and off you go! Essentially, what Stephan's elegant little solution is doing is allowing you to specify lambda's for how the recursive relate is enforced and then handing you back an IEnumerable<HierarchyNode<YourObjectHere>> graph of the object tree. Nifty! Here's the call to Stephan's code:
//so we now have enumerable list of distinct menus by ID
//now we need to recurse and build the tree of menu items
var hierarchy = distinctMenus.ToList().AsHierarchy(m => m.Menu_Item_Id, m => m.Parent_Id);
Recurse the POCOs Please
So where has this gotten us? We now have an object graph of DAL objects describing how the menus should be built in the UI for a given authenticated and authorized user. There is one more wrinkle in that we can't hand intelligent DAL objects across our IRepository boundary. The MVC controller wants an IList of domain MenuItem objects (POCOs) that don't look exactly like our data schema. So I rolled a little recursive function of my own to build our MenuItem POCOs and hand 'em back.
/// <summary>
/// Builds the menu items from an IEnumerable hierarchy of DAL objects.
/// </summary>
/// <param name="menuObject">The menu object.</param>
/// <returns></returns>
private IList<Domain.MenuItem> BuildMenuItemsFromDAL(IEnumerable<HierarchyNode<Menu_Item>> menuObject, string baseUrl)
{
IList<Domain.MenuItem> result = new List<Domain.MenuItem>();
Domain.MenuItem domainMenu;
//for string trimming...include dot and slash just in case db has relative pathing in it
char[] trim = "./".ToCharArray();
if (menuObject != null && menuObject.Count() > 0)
{
foreach (HierarchyNode<Menu_Item> mi in menuObject)
{
domainMenu = new MenuItem(mi.Entity.Item_Text,
String.IsNullOrEmpty(mi.Entity.Action_Url) ? null : baseUrl.TrimEnd(trim) + "/" + mi.Entity.Action_Url.TrimStart(trim),
String.IsNullOrEmpty(mi.Entity.Image_Url) ? null : baseUrl.TrimEnd(trim) + "/" + mi.Entity.Image_Url.TrimStart(trim),
mi.Entity.OnClickFunction);
domainMenu.Text = mi.Entity.Item_Text;
domainMenu.OnClickFunction = mi.Entity.OnClickFunction;
if (mi.ChildNodes != null && mi.ChildNodes.Count() > 0)
{
domainMenu.Children = BuildMenuItemsFromDAL(mi.ChildNodes, baseUrl);
}
result.Add(domainMenu);
}
}
return result;
}
And that's all there is to it. The nested menu items then get passed up to an MVC controller which will post them into a view to be rendered via Dojo, JQuery, etc.
