here's what's happening

Relational Data and a very strong case for Typed 2sxc

Part 1 - the data, the problem, and why Typed is better

2sxc and LINQ are built for easy access to, and manipulation of, structured and related data. Since 2sxc is not SQL, the structured content approach allows you to think outside the traditional SQL box. For now, if you learned about data in the SQL world, just imagine how different things could be if your tables had built in methods. That is a hint as to where we are going.

In this article, we use "content type" and "table" interchangeably and the following two sentences say the same thing.

In SQL, the table named BadgesAssigned has a field named Person, which is a pointer to a record in the Person table.

In 2sxc, the content type named BadgesAssigned has a field named Person, which is a link to an item (or entity) in the Person content type.

Badges. On their DNN-based Intranet, the client wanted to have their staff self-manage assigning Badges to themselves from a (fairly large) list of available badges. These personally selected badges would appear on their team member profile page. Fun stuff!

After thinking through the existing data for the staff and how it was used, we came up with the following approach for storing the Badges and assigning them to the staff. In this example, the related data is not pointed to in the main content type (table) named Person. Instead, there is an outside (foreign) content type that does have a link back to our main table. Here is the structured data we are using. There are 3 Content Types:

So when you are in code and working with a list of Persons, a single Person, or Badges, you do not locally have links to the outside data. For those that grew up using SQL, it should be obvious we are talking about a many-to-many relationship. BadgesAssigned would sometimes be called a junction table and (with more fields) the basis for associative data. So let's jump in to our situation and look at this from the run-time code's point of view:

1. We are in code in a View on a Detail page with /person/30378 in the URL
   (or ?person=30378).
2. If there are any, we need to get a list of Badges assigned to the specified Person.

With no local entity link pointing from Person to Badge (or Badge to person), how do we do this? We'll present a solution, discuss why, and mention a few pros and cons along the way.

Note: I wanted to more clearly point out the other method of doing this that we are avoiding (see the addendum in part 2 for the answer to why). The Person content type could have a field named Badges of type Entity and set to allow multiple items. Then we always have easy access to the Person's Badges because we have a list of links to them built right in (local), and the code to access them is a simple as, var badges = currPerson.Children("Badge");.

First, we'll highlight how elegantly "Typed" 2sxc lets us get that value from the URL so we have our detail record, the Person with Id 30378. In the old days [chuckle], we would mess with QueryString and wrangle the name/value pair (person/30378) from string to integer... Now, we simply go fetch it via Page.Parameters, confident we will get back an .Int() (integer) even if there is nothing to get...

  @inherits Custom.Hybrid.RazorTyped

  @* store all our Active Persons in a variable since reuse is likely *@
  var activePersons = AsItems(App.Data["Person"])
    .Where(p => p.Bool("Active"))
  ;
  var currPerson = activePersons 
    .Where(p => p.Id == MyPage.Parameters.Int("person"))
    .FirstOrDefault()
  ;

The result of the highlighted code ensures that we get the value of person as an integer, even if it is non-numeric, not there, /person is not in the URL, ... because .Int() has a default fallback of 0 (zero, an integer). So 7 to 10 lines of code**, usually including Int.TryParse(), simplified to only worrying about whether or not the result of the LINQ expression returned one of the Person items or not. In other words, since we are guaranteed an interger, currPerson will be the entity of the Person specified with the URL param, or null. I cannot stress what a great example of "why Typed is better" this is.

For the purposes of this demo, we will handle the possible null by simply grabbing the first Person we have in our data. The only condition we are not accounting for is if there are no items (records) in our Person content type (table). 

  if ( currPerson == null ) {
    currPerson = activePersons.FirstOrDefault();
  }

In a live/production scenario, we would probably show an error and provide a link, or log the error. Then simply return to the list page+View. So now we've proved the greatness of Typed and can move on to step 2, how do we get our (stinkin') Badges for Person.Id 30378?

By grabbing all the items in the BadgesAssigned content type pointing to our current Person:

  var badges = AsItems(App.Data["BadgesAssigned"])
    .Where(p => p.Child("Person").Id == currPerson.Id)
  ;

If you are not familiar with what .Where(p => is doing, (briefly) we are using LINQ to evaluate every record in (for each) BadgesAssigned, so lets review the highlighted part in plain English, left to right:

For the current BadgesAssigned record (p),

1. call the .Child() method 
2. passing in the name of the field, "Person"
3. fetch the Person (internally, BadgesAssigned.Person contains a GUID) and return that one item
4. then get it's property, .Id 
5. is it the same as our current Person's .Id?
6. if yes, keep this item (adding it to the list we are building and returning).

After that runs, badges now contains none, one, or multiple records from the BadgesAssigned table, but only the ones for the current person. So we got that goin' for us. Which is nice.

We will be back tomorrow with Part 2 and get those Badges displayed!

The fun continues in Related Data in 2sxc - Part 2