App

LINQ Tutorial (Language INtegrated Query)

Tutorial Home › LINQ
LINQ Basics
#2 LINQ Relationships - Children, Parents, Grandchildren, ...
Working with JSON Data
Resources

LINQ Relationships - Children, Parents, Grandchildren, ...

The samples can differ based on your Razor base class or if you're running an old version.
Selected: Typed (2sxc 16+) Switch to Dynamic (Razor14 or below)

Accessing Authors, a List inside of Books

This example shows the books, and we want to LINQ on the Authors. We must tell the compiler it's a list, so that LINQ can use Select(...).

⬇️ Result | Source ➡️

  1. Hitchhikers Guide to the Galaxy by Douglas Adams
  2. Good Omens by Neil Gaiman, Terry Pratchett
  3. Phishing for Phools by George Akerlof
  4. The Last Continent by Terry Pratchett 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
<ol>
  @foreach (var book in books) {
    var authors = AsItems(book.Children("Authors"))
      .Select(a => a.String("FirstName") + " " + a.String("LastName"));
    <li><strong>@book.Get("Title")</strong>
      by @string.Join(", ", authors.OrderBy(ln => ln)) 
    </li>
  }
</ol>

This example shows Z-A ordering, where we count the authors to sort.

⬇️ Result | Source ➡️

  1. Good Omens by Neil Gaiman,Terry Pratchett (2 author)
  2. Hitchhikers Guide to the Galaxy by Douglas Adams (1 author)
  3. Phishing for Phools by George Akerlof (1 author)
  4. The Last Continent by Terry Pratchett (1 author) 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
<ol>
  @foreach (var book in books
    .OrderByDescending(p => p.Children("Authors").Count())) 
  {
    var authors = AsItems(book.Children("Authors"))
      .Select(a => a.String("FirstName") + " " + a.String("LastName"));
    <li><strong>@book.Get("Title")</strong> 
      by @string.Join(",", authors.OrderBy(ln => ln)) 
      (@book.Children("Authors").Count() author) 
    </li>
  }
</ol>

Grouping by Authors, a List inside of Books

This example will group the books by the Authors property. This example doesn't give us what we want, because authors that exist in sets and alone are grouped separately.

⬇️ Result | Source ➡️

  1. Group
    • Hitchhikers Guide to the Galaxy by Douglas Adams
  2. Group
    • Good Omens by Neil Gaiman,Terry Pratchett
  3. Group
    • Phishing for Phools by George Akerlof
  4. Group
    • The Last Continent by Terry Pratchett 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
<ol>
  @foreach (var group in books.GroupBy(b => b.Child("Authors"))) {
    <li>
      Group
      <ul>
        @foreach (var book in group) {
          <li>
            <strong>@book.Get("Title")</strong> by
            @string.Join(",", AsItems(book.Children("Authors"))
            .Select(a => a.String("FirstName") + " " + a.String("LastName")))
          </li>
        }
      </ul>
    </li>
  }
</ol>

Let's do it better, and group by each Author individually

⬇️ Result | Source ➡️

  1. Author: Douglas Adams
    • Hitchhikers Guide to the Galaxy by Douglas Adams
  2. Author: Neil Gaiman
    • Good Omens by Neil Gaiman,Terry Pratchett
  3. Author: Terry Pratchett
    • Good Omens by Neil Gaiman,Terry Pratchett
    • The Last Continent by Terry Pratchett
  4. Author: George Akerlof
    • Phishing for Phools by George Akerlof 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
<ol>
  @{ 
    var booksGroupedByAuthor = books
      .SelectMany(b => AsItems(b.Children("Authors"))
      .Select(a => new { Book = b, Author = a }))
      .GroupBy(set => set.Author);
    
    foreach (var group in booksGroupedByAuthor) {
      <li>
        Author: @group.Key.String("FirstName") @group.Key.String("LastName")
        <ul>
          @foreach (var bundle in group) {
            var names = AsItems(bundle.Book.Children("Authors"))
              .Select(a => a.String("FirstName") + " " + a.String("LastName"));
            <li>
              <strong>@bundle.Book.Get("Title")</strong> by
              @string.Join(",", names)
            </li>
          }
        </ul>
      </li>
    }
  }
</ol>

In this example, we'll start with the authors list. This is probably not ideal - as some people are not authors, but it's a good learning example. To find the books we have to navigate through Parents(...) because in our data-model, the books reference authors, not the authors to books.

⬇️ Result | Source ➡️

  1. Douglas Adams
    • Hitchhikers Guide to the Galaxy co-authored by Douglas Adams
  2. Terry Pratchett
    • Good Omens co-authored by Neil Gaiman,Terry Pratchett
    • The Last Continent co-authored by Terry Pratchett
  3. Neil Gaiman
    • Good Omens co-authored by Neil Gaiman,Terry Pratchett
  4. George Akerlof
    • Phishing for Phools co-authored by George Akerlof
  5. Raphael Müller (not an author)
  6. Ed Hardy 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
<ol>
  @foreach (var author in persons) {
    // this line would work, if Books only had people in the Authors. 
    // but it doesn't, there are also illustrators, 
    // which is why we use the second example instead
    var peoplesBooks = author.Parents(type: "Books");
    var authorsBooks = author.Parents(type: "Books", field: "Authors");

    <li>
      @author.String("FirstName") @author.String("LastName")
      <ul>
        @foreach (var book in authorsBooks) {
          var coAuthors = AsItems(book.Children("Authors"))
          .Where(a => a != author);
          <li>
            <strong>@book.Get("Title")</strong> 
            @if (coAuthors.Any()) {
              <span>co-authored by 
                @string.Join(",", coAuthors
                .Select(a => a.String("FirstName") + " " + a.String("LastName")))
              </span>
            }
          </li>
        }
      </ul>
    </li>
  }
</ol>

Books.Authors.Awards, a List in a List in a List

This example first gets book, checks the authors and checks if they have awards with LINQ Any().

⬇️ Result | Source ➡️

  1. Hitchhikers Guide to the Galaxy with Hugo Award,Inkpot Award (2 awards)
  2. Phishing for Phools with Nobel Peace Prize (1 awards) 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code

  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
@{
  // this keeps all books whose authors have awards
  var booksWithAwardedAuthors = books
    .Where(b => AsItems(b.Children("Authors"))
    .Any(a => a.Children("Awards").Count() > 0));
}
<ol>
  @foreach (var book in booksWithAwardedAuthors) {
    var awards = AsItems(book.Children("Authors"))
      .SelectMany(a => AsItems(a.Children("Awards")));
    <li><strong>@book.Get("Title")</strong> 
      with @string.Join(",", awards.Select(a => a.String("Name"))) 
      (@awards.Count() awards) 
    </li>
  }
</ol>

And now the opposite list, so all books which don't contain one of the books with authors. It gets the "other" books by filtering the list to exclude the ones it found first. That demonstrates how to use Contains(x as object). The Contains(...) cannot work with dynamic, so we must tell it it's an object for it to work.

⬇️ Result | Source ➡️

  1. Good Omens
  2. The Last Continent
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
@{
  // this keeps all books whose authors have awards
  var booksWithAwardedAuthors = books
    .Where(b => AsItems(b.Children("Authors"))
    .Any(a => a.Children("Awards").Count() > 0));
  var otherBooks = books.Where(b => !booksWithAwardedAuthors
    .Contains(b as object));
}
<ol>
  @foreach (var book in otherBooks) {
    <li><strong>@book.Get("Title")</strong></li>
  }
</ol>

Now let's do the same, but using GroupBy to group by awarded authors and not-awarded authors:

⬇️ Result | Source ➡️

  • Authors with Awards: True
    • Hitchhikers Guide to the Galaxy
    • Phishing for Phools
  • Authors with Awards: False
    • Good Omens
    • The Last Continent 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
@{
  var booksGroupedByAuthorAwards = books
    .GroupBy(b => (AsItems(b.Children("Authors"))
    .Any(a => a.Children("Awards").Count() > 0)));
}
<ul>
  @foreach (var group in booksGroupedByAuthorAwards) {
    <li>
      Authors with Awards: @group.Key
      <ul>
        @foreach (var book in group) {
          <li>
            @book.Get("Title")
          </li>
        }
      </ul>
    </li>
  }
</ul>

Find Parents of Authors - Things that point to Authors

In this example, we'll start with the authors list. This is probably not ideal - as some people are not authors, but it's a good learning example. To find the books we have to navigate through Parents(...) because in our data-model, the books reference authors, not the authors to books.

⬇️ Result | Source ➡️

  1. Douglas Adams
    • Hitchhikers Guide to the Galaxy co-authored by Douglas Adams
  2. Terry Pratchett
    • Good Omens co-authored by Neil Gaiman,Terry Pratchett
    • The Last Continent co-authored by Terry Pratchett
  3. Neil Gaiman
    • Good Omens co-authored by Neil Gaiman,Terry Pratchett
  4. George Akerlof
    • Phishing for Phools co-authored by George Akerlof
  5. Raphael Müller (not an author)
  6. Ed Hardy 
@inherits Custom.Hybrid.RazorTyped
@using System.Linq;

@{
  // Initial Code
  var persons = AsItems(App.Data.GetStream("Persons"));
  var books = AsItems(App.Data.GetStream("Books"));
}
<ol>
  @foreach (var author in persons) {
    // this line would work, if Books only 
    // had people in the Authors. 
    // but it doesn't, there are also illustrators, 
    // which is why we use the second example instead
    var peoplesBooks = author.Parents(type:"Books");
    var authorsBooks = author.Parents(type:"Books", field: "Authors");

    <li>
      @author.Get("FirstName") @author.Get("LastName")
      <ul>
        @foreach (var book in authorsBooks) {
          var coAuthors = AsItems(book.Children("Authors"))
          .Where(a => a != author);
          <li>
            <strong>@book.Get("Title")</strong> 
            @if (coAuthors.Any()) {
              <span>co-authored by 
                @string.Join(",", coAuthors
                .Select(a => a.String("FirstName") + " " + a.String("LastName")))
              </span>
            }
          </li>
        }
      </ul>
    </li>
  }
</ol>

 

 

LINQ Basics
#2 LINQ Relationships - Children, Parents, Grandchildren, ...
Working with JSON Data