notebooks/0106-LINQ.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Session 6: LINQ and Extension Methods\n",
    "\n",
    "<img align=\"right\" width=\"150\" height=\"113\" src=\"img/csharp_savetheday.png\">\n",
    "\n",
    "[LINQ (Language Integrated Query)](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/?WT.mc_id=visualstudio-twitch-jefritz) is a collection of methods and language features that allow you to interact with collections of data.  In our last session, we focused on **LINQ to Objects** which allows us to use method predicates to interact with those collections.\n",
    "\n",
    "Let's setup our `Card` class and `FritzSet` collection object to work with again in this workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Card {\n",
    "    public Card(string def) {\n",
    "        var values = def.Split('-');\n",
    "        Rank = values[0];\n",
    "        Suit = values[1];\n",
    "    }\n",
    "    public string Rank;\n",
    "    public int RankValue { \n",
    "        get { \n",
    "            var faceCards = new Dictionary<string,int> { {\"J\", 11}, {\"Q\", 12}, {\"K\", 13}, {\"A\", 14} };\n",
    "            return faceCards.ContainsKey(Rank) ? faceCards[Rank] : int.Parse(Rank); \n",
    "        }\n",
    "    }\n",
    "    public string Suit;\n",
    "    public override string ToString() {\n",
    "        return $\"{Rank}-{Suit}\";\n",
    "    }\n",
    "    \n",
    "    private static bool IsLegalCardNotation(string notation) {\n",
    "        var segments = notation.Split('-');\n",
    "        if (segments.Length != 2) return false;\n",
    "\n",
    "        var validSuits = new [] {\"c\",\"d\",\"h\",\"s\"};\n",
    "        if (!validSuits.Any(s => s == segments[1])) return false;\n",
    "        \n",
    "        var validRanks = new [] {\"A\",\"2\",\"3\",\"4\",\"5\",\"6\",\"7\",\"8\",\"9\",\"10\",\"J\",\"Q\",\"K\"};\n",
    "        if (!validRanks.Any(r => r == segments[0])) return false;\n",
    "        return true;\n",
    "        \n",
    "    }\n",
    "    \n",
    "    public static implicit operator Card(string id) {\n",
    "        if (IsLegalCardNotation(id)) return new Card(id);\n",
    "        return null;\n",
    "    }\n",
    "}\n",
    "\n",
    "class FritzSet<T> : IEnumerable<T> {\n",
    "\n",
    "    private List<T> _Inner = new List<T>();\n",
    "\n",
    "    public IEnumerator<T> GetEnumerator()\n",
    "    {\n",
    "        return _Inner.GetEnumerator();\n",
    "    }\n",
    "\n",
    "    IEnumerator IEnumerable.GetEnumerator()\n",
    "    {\n",
    "        return _Inner.GetEnumerator();\n",
    "    }\n",
    "\n",
    "    public FritzSet<T> Add(T newItem) {\n",
    "        var insertAt = _Inner.Count == 0 ? 0 : new Random().Next(0,_Inner.Count+1);\n",
    "        _Inner.Insert(insertAt, newItem);\n",
    "        return this;\n",
    "    }\n",
    "    \n",
    "    public FritzSet<T> Shuffle() {\n",
    "        _Inner = _Inner.OrderBy(_ => Guid.NewGuid()).ToList();\n",
    "        return this;\n",
    "    }\n",
    "    \n",
    "}\n",
    "\n",
    "var TheDeck = new FritzSet<Card>();\n",
    "TheDeck.Add(\"A-c\").Add(\"A-d\");TheDeck.Add(\"A-h\");TheDeck.Add(\"A-s\");TheDeck.Add(\"2-c\");TheDeck.Add(\"2-d\");TheDeck.Add(\"2-h\");TheDeck.Add(\"2-s\");TheDeck.Add(\"3-c\");TheDeck.Add(\"3-d\");TheDeck.Add(\"3-h\");TheDeck.Add(\"3-s\");TheDeck.Add(\"4-c\");TheDeck.Add(\"4-d\");TheDeck.Add(\"4-h\");TheDeck.Add(\"4-s\");\n",
    "TheDeck.Add(\"5-c\");TheDeck.Add(\"5-d\");TheDeck.Add(\"5-h\");TheDeck.Add(\"5-s\");TheDeck.Add(\"6-c\");TheDeck.Add(\"6-d\");TheDeck.Add(\"6-h\");TheDeck.Add(\"6-s\");TheDeck.Add(\"7-c\");TheDeck.Add(\"7-d\");TheDeck.Add(\"7-h\");TheDeck.Add(\"7-s\");TheDeck.Add(\"8-c\");TheDeck.Add(\"8-d\");TheDeck.Add(\"8-h\");TheDeck.Add(\"8-s\");\n",
    "TheDeck.Add(\"9-c\");TheDeck.Add(\"9-d\");TheDeck.Add(\"9-h\");TheDeck.Add(\"9-s\");TheDeck.Add(\"10-c\");TheDeck.Add(\"10-d\");TheDeck.Add(\"10-h\");TheDeck.Add(\"10-s\");TheDeck.Add(\"J-c\");TheDeck.Add(\"J-d\");TheDeck.Add(\"J-h\");TheDeck.Add(\"J-s\");\n",
    "TheDeck.Add(\"Q-c\");TheDeck.Add(\"Q-d\");TheDeck.Add(\"Q-h\");TheDeck.Add(\"Q-s\");TheDeck.Add(\"K-c\");TheDeck.Add(\"K-d\");TheDeck.Add(\"K-h\");TheDeck.Add(\"K-s\");\n",
    "\n",
    "// TheDeck\n",
    "TheDeck.Shuffle().Shuffle().Shuffle().Shuffle().Shuffle();\n",
    "//TheDeck\n",
    "\n",
    "Card PriyanksCard = \"Joker\"; // Fix this\n",
    "//display(PriyanksCard ?? \"No card assigned\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In review, we can write a little bit of code to work with this collection to deal cards appropriately for a Texas Hold 'em poker game:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Hand 1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">s</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Hand 2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">J</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">J</div></td><td><div class=\"dni-plaintext\">h</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Hand 3"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">J</div></td><td><div class=\"dni-plaintext\">d</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "The Flop"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">c</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">14</div></td><td><div class=\"dni-plaintext\">A</div></td><td><div class=\"dni-plaintext\">c</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">9</div></td><td><div class=\"dni-plaintext\">9</div></td><td><div class=\"dni-plaintext\">h</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "The Turn"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">h</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "The River"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td><div class=\"dni-plaintext\">7</div></td><td><div class=\"dni-plaintext\">7</div></td><td><div class=\"dni-plaintext\">d</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "var ourDeck = TheDeck.Shuffle().Shuffle();\n",
    "\n",
    "var hand1 = new List<Card>();\n",
    "var hand2 = new List<Card>();\n",
    "var hand3 = new List<Card>();\n",
    "hand1.Add(ourDeck.Skip(1).First());\n",
    "hand2.Add(ourDeck.Skip(2).First());\n",
    "hand3.Add(ourDeck.Skip(3).First());\n",
    "hand1.Add(ourDeck.Skip(4).First());\n",
    "hand2.Add(ourDeck.Skip(5).First());\n",
    "hand3.Add(ourDeck.Skip(6).First());\n",
    "\n",
    "display(\"Hand 1\");\n",
    "display(hand1);\n",
    "display(\"Hand 2\");\n",
    "display(hand2);\n",
    "display(\"Hand 3\");\n",
    "display(hand3);\n",
    "\n",
    "// Burn a card and deal the next 3 cards called 'the flop'\n",
    "display(\"The Flop\");\n",
    "display(ourDeck.Skip(8).Take(3));\n",
    "    \n",
    "// Burn a card and take one card called 'the turn'\n",
    "display(\"The Turn\");\n",
    "display(ourDeck.Skip(12).First());\n",
    "\n",
    "// Burn a card and take the final card called 'the river'\n",
    "display(\"The River\");\n",
    "display(ourDeck.Skip(14).First());\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Language Integrated Query\n",
    "\n",
    "You can build [expressions](https://docs.microsoft.com/dotnet/csharp/linq/query-expression-basics?WT.mc_id=visualstudio-twitch-jefritz#what-is-a-query-and-what-does-it-do) in the middle of your C# code that _LOOKS_ like SQL turned sideways.  Query Expressions begin with a `from` clause and there's also a mandatory `select` clause to specify the values to return.  By convention, many C# developers who use this syntax align the clauses to the right of the `=` symbol.  Let's dig into that syntax a bit more:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">d</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">3</div></td><td><div class=\"dni-plaintext\">3</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">c</div></td></tr><tr><td>4</td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>5</td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>6</td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">d</div></td></tr><tr><td>7</td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">d</div></td></tr><tr><td>8</td><td><div class=\"dni-plaintext\">13</div></td><td><div class=\"dni-plaintext\">K</div></td><td><div class=\"dni-plaintext\">c</div></td></tr><tr><td>9</td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">c</div></td></tr><tr><td>10</td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">c</div></td></tr><tr><td>11</td><td><div class=\"dni-plaintext\">13</div></td><td><div class=\"dni-plaintext\">K</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>12</td><td><div class=\"dni-plaintext\">14</div></td><td><div class=\"dni-plaintext\">A</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>13</td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>14</td><td><div class=\"dni-plaintext\">12</div></td><td><div class=\"dni-plaintext\">Q</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>15</td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">s</div></td></tr><tr><td>16</td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">d</div></td></tr><tr><td>17</td><td><div class=\"dni-plaintext\">12</div></td><td><div class=\"dni-plaintext\">Q</div></td><td><div class=\"dni-plaintext\">d</div></td></tr><tr><td>18</td><td><div class=\"dni-plaintext\">3</div></td><td><div class=\"dni-plaintext\">3</div></td><td><div class=\"dni-plaintext\">d</div></td></tr><tr><td>19</td><td><div class=\"dni-plaintext\">13</div></td><td><div class=\"dni-plaintext\">K</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td colspan=\"4\">(32 more)</td></tr></tbody></table>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// The simplest query\n",
    "var outValues = from card in TheDeck // the required collection we are querying\n",
    "                select card;         // the values to be returned\n",
    "\n",
    "outValues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Where and OrderBy clauses\n",
    "\n",
    "That's a boring and non-productive query.  You can start to make queries more interesting by adding a [where](https://docs.microsoft.com/dotnet/csharp/language-reference/keywords/where-clause?WT.mc_id=visualstudio-twitch-jefritz) clause with an appropriate test in a format similar to that you would find in an `if` statement.  You can also optionally add an [orderby](https://docs.microsoft.com/dotnet/csharp/language-reference/keywords/orderby-clause?WT.mc_id=visualstudio-twitch-jefritz) clause with an **ALSO** optional [descending](https://docs.microsoft.com/dotnet/csharp/language-reference/keywords/descending?WT.mc_id=visualstudio-twitch-jefritz) keyword.  Tinker with the query in the next block to learn more about these clauses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>RankValue</th><th>Rank</th><th>Suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">14</div></td><td><div class=\"dni-plaintext\">A</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">13</div></td><td><div class=\"dni-plaintext\">K</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">12</div></td><td><div class=\"dni-plaintext\">Q</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">J</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>4</td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>5</td><td><div class=\"dni-plaintext\">9</div></td><td><div class=\"dni-plaintext\">9</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>6</td><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>7</td><td><div class=\"dni-plaintext\">7</div></td><td><div class=\"dni-plaintext\">7</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>8</td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>9</td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">5</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>10</td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>11</td><td><div class=\"dni-plaintext\">3</div></td><td><div class=\"dni-plaintext\">3</div></td><td><div class=\"dni-plaintext\">h</div></td></tr><tr><td>12</td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">h</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from card in TheDeck\n",
    "              where card.Suit == \"h\"    // Return just the Hearts\n",
    "              orderby card.RankValue descending\n",
    "              select card;\n",
    "              \n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Additionally, nothing is requiring you to return the object in the collection.  You can return different properties and values by changing up the `select` clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>value</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">A</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">K</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">J</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">Q</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from card in TheDeck\n",
    "              where card.Suit == \"h\" && card.RankValue > 10\n",
    "              select card.Rank;\n",
    "              \n",
    "results              "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joins\n",
    "\n",
    "Just like SQL syntax, you can correlate two collections and work with the combined result. The [Join keyword](https://docs.microsoft.com/dotnet/csharp/language-reference/keywords/join-clause?WT.mc_id=visualstudio-twitch-jefritz) allows you to relate two collections based on a matching key value in each collection.  There is a similar [Join method in LINQ to Objects](https://docs.microsoft.com/dotnet/api/system.linq.enumerable.join?view=netcore-3.1&WT.mc_id=visualstudio-twitch-jefritz) that delivers the same feature.  \n",
    "\n",
    "Joins are slightly more involved and can be confusing topic, and we've embedded the official sample from the docs here.  This sample relates `Person` records to their `Pets` that they own.  The `Join` method receives each collection and uses two expression bodied members to select the key properties from each collection.  Finally, it provides a projection method to create the resultant object.\n",
    "\n",
    "I have annotated this sample and the `Join` method to make it clearer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Hedlund, Magnus - Daisy"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Adams, Terry - Barley"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Adams, Terry - Boots"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Weiss, Charlotte - Whiskers"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "class Person\n",
    "{\n",
    "    public string Name { get; set; }\n",
    "}\n",
    "\n",
    "class Pet\n",
    "{\n",
    "    public string Name { get; set; }\n",
    "    public Person Owner { get; set; }\n",
    "}\n",
    "\n",
    "    Person magnus = new Person { Name = \"Hedlund, Magnus\" };\n",
    "    Person terry = new Person { Name = \"Adams, Terry\" };\n",
    "    Person charlotte = new Person { Name = \"Weiss, Charlotte\" };\n",
    "\n",
    "    // Declare the set of 4 pets and their owners\n",
    "    Pet barley = new Pet { Name = \"Barley\", Owner = terry };\n",
    "    Pet boots = new Pet { Name = \"Boots\", Owner = terry };\n",
    "    Pet whiskers = new Pet { Name = \"Whiskers\", Owner = charlotte };\n",
    "    Pet daisy = new Pet { Name = \"Daisy\", Owner = magnus };\n",
    "\n",
    "    List<Person> people = new List<Person> { magnus, terry, charlotte };\n",
    "    List<Pet> pets = new List<Pet> { barley, boots, whiskers, daisy };\n",
    "\n",
    "    // Create a list of Person-Pet pairs where\n",
    "    // each element is an anonymous type that contains a\n",
    "    // Pet's name and the name of the Person that owns the Pet.\n",
    "    var query =\n",
    "        people.Join(pets,                 // Join the People and Pets collections\n",
    "                    person => person,     // We will match the Person object\n",
    "                    pet => pet.Owner,     // with the Owner property in the Pet record\n",
    "                    (person, pet) =>      // The combined output of Person and Pet\n",
    "                                          // is an object with OwnerName and the Pet's Name\n",
    "                        new { OwnerName = person.Name, Pet = pet.Name });\n",
    "\n",
    "    foreach (var obj in query)\n",
    "    {\n",
    "        display(string.Format(\"{0} - {1}\",\n",
    "            obj.OwnerName,\n",
    "            obj.Pet));\n",
    "    }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping data with the Group clause\n",
    "\n",
    "Data in your query can be grouped together using the [group clause](https://docs.microsoft.com/dotnet/csharp/language-reference/keywords/group-clause?WT.mc_id=visualstudio-twitch-jefritz).  The `group` clause can be used in place of the `select` clause or can be used with the `select` clause to aggregate data in various groupings.  Let's try using the `group` keywords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span><a href=\"https://docs.microsoft.com/dotnet/api/system.linq.groupedenumerable-2?view=netcore-3.0\">System.Linq.GroupedEnumerable&lt;Submission#4+Card,System.String&gt;</a></span>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>value</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">[ 10-s, 3-s, 6-s, 5-s, K-s, Q-s, 2-s, 4-s, 7-s, J-s, 9-s, 8-s, A-s ]</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">[ 4-d, 5-d, 2-d, 10-d, Q-d, 3-d, J-d, 7-d, 9-d, K-d, A-d, 8-d, 6-d ]</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">[ 4-c, K-c, 6-c, 5-c, A-c, 9-c, 10-c, 3-c, 8-c, 7-c, Q-c, J-c, 2-c ]</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">[ A-h, 10-h, K-h, J-h, 4-h, 7-h, 2-h, Q-h, 5-h, 9-h, 8-h, 3-h, 6-h ]</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from card in TheDeck\n",
    "              group card by card.Suit;\n",
    "\n",
    "display(results.GetType());\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Interestingly, we are returned a collection with all of the cards grouped by their suits.  If we also wanted to select the suit and create a grouped result we could expand our query like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span><a href=\"https://docs.microsoft.com/dotnet/api/system.linq.enumerable.selectenumerableiterator-2?view=netcore-3.0\">System.Linq.Enumerable+SelectEnumerableIterator&lt;System.Linq.IGrouping&lt;System.String,Submission#34+Card&gt;,&lt;&gt;f__AnonymousType0#7&lt;System.String,System.Linq.IGrouping&lt;System.String,Submission#34+Card&gt;&gt;&gt;</a></span>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>TheSuit</th><th>suit</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">d</div></td><td><div class=\"dni-plaintext\">[ 9-d, 5-d, 2-d, A-d, 6-d, 8-d, K-d, 4-d, Q-d, 10-d, 3-d, 7-d, J-d ]</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">c</div></td><td><div class=\"dni-plaintext\">[ Q-c, J-c, 6-c, 3-c, 8-c, A-c, 5-c, 9-c, K-c, 2-c, 7-c, 10-c, 4-c ]</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">h</div></td><td><div class=\"dni-plaintext\">[ J-h, 7-h, 5-h, 8-h, 2-h, Q-h, A-h, 3-h, 6-h, 4-h, 10-h, 9-h, K-h ]</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">s</div></td><td><div class=\"dni-plaintext\">[ 3-s, 6-s, 8-s, Q-s, A-s, 5-s, K-s, 4-s, J-s, 7-s, 9-s, 2-s, 10-s ]</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from card in TheDeck\n",
    "              group card by card.Suit into suit\n",
    "              select new {TheSuit=suit.Key, suit};\n",
    "\n",
    "display(results.GetType());\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"img/facecards.png\" align=\"right\">\n",
    "\n",
    "Now this is **VERY INTERESTING** we have created an [Anonymous Type](https://docs.microsoft.com/dotnet/csharp/programming-guide/classes-and-structs/anonymous-types?WT.mc_id=visualstudio-twitch-jefritz), a type on the fly that contains a string field for `TheSuit` and a collection of `Card` objects in a field called `suit`.  We'll get more into **Anonymous Types** next week, but you need to know that you can use the `new` keyword with curly braces `{ }` to create a type and make it available in your code.  Many C# veterans will recommend against exposing the anonymous type outside of the method it is created in and instead suggest creating a concrete type to return in that `select` clause."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our groupings can take some interesting calculations.  Let's write a grouping for all of the face cards (and the Ace too):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>TheSuit</th><th>facecards</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">False</div></td><td><div class=\"dni-plaintext\">[ 10-s, 4-d, 3-s, 4-c, 6-s, 5-s, 5-d, 2-d, 6-c, 5-c, 10-h, 2-s, 10-d, 3-d, 9-c, 4-h, 4-s, 7-s, 7-h, 10-c ... (16 more) ]</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">True</div></td><td><div class=\"dni-plaintext\">[ K-c, K-s, A-h, Q-s, Q-d, K-h, A-c, J-h, Q-h, J-s, Q-c, J-d, J-c, K-d, A-d, A-s ]</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from card in TheDeck\n",
    "              group card by card.RankValue > 10 into facecards\n",
    "              select new {TheSuit=facecards.Key, facecards};\n",
    "\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That looks strange, but we have two groups:  1 group that are the numeric cards and a second group that are the face cards.  Let's tinker with that method a little more:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>Face</th><th>facecards</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">K</div></td><td><div class=\"dni-plaintext\">[ K-c, K-s, K-h, K-d ]</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">A</div></td><td><div class=\"dni-plaintext\">[ A-h, A-c, A-d, A-s ]</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">Q</div></td><td><div class=\"dni-plaintext\">[ Q-s, Q-d, Q-h, Q-c ]</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">J</div></td><td><div class=\"dni-plaintext\">[ J-h, J-s, J-d, J-c ]</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from card in TheDeck\n",
    "              where card.RankValue > 10\n",
    "              group card by card.Rank into facecards\n",
    "              select new {Face=facecards.Key, facecards};\n",
    "\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now this sets up for a simplified **Sam the Bellhop** classic card trick.  Take a few minutes and enjoy magician and former Philadelphia Eagles player [Jon Dorenbos performing this trick](https://www.youtube.com/watch?v=fwKPDrtgXRs) where he sorts and finds cards while telling the story of Sam the Bellhop."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading data from CSV\n",
    "\n",
    "We've worked with objects and data that we've specified here in the notebook. Let's use an external library, in .NET we call them **NuGet Packages** from www.nuget.org called [LINQtoCSV](https://www.nuget.org/packages/LinqToCsv/) to load Atlantic Hurricane Season data (courtesy of [Wikipedia](https://en.wikipedia.org/wiki/Atlantic_hurricane_season))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>Year</th><th>TropicalStormCount</th><th>HurricaneCount</th><th>StrongestStorm</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">2020</div></td><td><div class=\"dni-plaintext\">23</div></td><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">Laura</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">2019</div></td><td><div class=\"dni-plaintext\">18</div></td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">Dorian</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">2018</div></td><td><div class=\"dni-plaintext\">15</div></td><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">Michael</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">2017</div></td><td><div class=\"dni-plaintext\">17</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">Maria</div></td></tr><tr><td>4</td><td><div class=\"dni-plaintext\">2016</div></td><td><div class=\"dni-plaintext\">15</div></td><td><div class=\"dni-plaintext\">7</div></td><td><div class=\"dni-plaintext\">Matthew</div></td></tr><tr><td>5</td><td><div class=\"dni-plaintext\">2015</div></td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">4</div></td><td><div class=\"dni-plaintext\">Joaquin</div></td></tr><tr><td>6</td><td><div class=\"dni-plaintext\">2014</div></td><td><div class=\"dni-plaintext\">8</div></td><td><div class=\"dni-plaintext\">6</div></td><td><div class=\"dni-plaintext\">Gonzalo</div></td></tr><tr><td>7</td><td><div class=\"dni-plaintext\">2013</div></td><td><div class=\"dni-plaintext\">14</div></td><td><div class=\"dni-plaintext\">2</div></td><td><div class=\"dni-plaintext\">Humberto</div></td></tr><tr><td>8</td><td><div class=\"dni-plaintext\">2012</div></td><td><div class=\"dni-plaintext\">19</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">Sandy</div></td></tr><tr><td>9</td><td><div class=\"dni-plaintext\">2011</div></td><td><div class=\"dni-plaintext\">19</div></td><td><div class=\"dni-plaintext\">7</div></td><td><div class=\"dni-plaintext\">Ophelia</div></td></tr></tbody></table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#r \"nuget:LinqToCsv\"\n",
    "using LINQtoCSV;\n",
    "\n",
    "class MyDataRow { \n",
    "    [CsvColumn(Name = \"Year\", FieldIndex = 1)]\n",
    "    public int Year {get; set;}\n",
    "    [CsvColumn(Name = \"Number of tropical storms\", FieldIndex = 2)]\n",
    "    public byte TropicalStormCount { get; set;}\n",
    "    [CsvColumn(Name = \"Number of hurricanes\", FieldIndex = 3)]\n",
    "    public byte HurricaneCount { get; set;}\n",
    "    [CsvColumn(Name = \"Number of major hurricanes\", FieldIndex = 4)]\n",
    "    public byte MajorHurricaneCount { get; set;}\n",
    "    \n",
    "    // Accumulated Cyclone Energy\n",
    "    [CsvColumn(Name = \"ACE\", FieldIndex = 5)]\n",
    "    public decimal ACE { get; set; }\n",
    "    \n",
    "    [CsvColumn(Name = \"Deaths\", FieldIndex = 6)]\n",
    "    public int Deaths { get; set; }\n",
    "\n",
    "    [CsvColumn(Name=\"Strongest storm\", FieldIndex = 7)]\n",
    "    public string StrongestStorm { get; set; }\n",
    "    \n",
    "    [CsvColumn(Name = \"Damage USD\", FieldIndex = 8)]\n",
    "    public string DamageUSD { get; set; }\n",
    "\n",
    "    [CsvColumn(Name = \"Retired names\", FieldIndex = 9)]\n",
    "    public string RetiredNames { get; set; }\n",
    "\n",
    "    [CsvColumn(Name = \"Notes\", FieldIndex = 10)]\n",
    "    public string Notes { get; set; }\n",
    "\n",
    "    \n",
    "}\n",
    "var inputFileDescription = new CsvFileDescription\n",
    "{\n",
    "    SeparatorChar = ',', \n",
    "    FirstLineHasColumnNames = true\n",
    "};\n",
    "var context = new CsvContext();\n",
    "var hurricanes = context.Read<MyDataRow>(\"data/atlantic_hurricanes.csv\", inputFileDescription);\n",
    "display(hurricanes.OrderByDescending(h => h.Year).Take(10).Select(h => new {h.Year, h.TropicalStormCount, h.HurricaneCount, h.StrongestStorm}));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><thead><tr><th><i>index</i></th><th>Year</th><th>HurricaneCount</th><th>ACE</th><th>StrongestStorm</th><th>DamageUSD</th></tr></thead><tbody><tr><td>0</td><td><div class=\"dni-plaintext\">2017</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">224.88</div></td><td><div class=\"dni-plaintext\">Maria</div></td><td><div class=\"dni-plaintext\">≥ $294.67 billion</div></td></tr><tr><td>1</td><td><div class=\"dni-plaintext\">1995</div></td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">227.10</div></td><td><div class=\"dni-plaintext\">Opal</div></td><td><div class=\"dni-plaintext\">$9.3&#160;billion</div></td></tr><tr><td>2</td><td><div class=\"dni-plaintext\">2012</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">132.63</div></td><td><div class=\"dni-plaintext\">Sandy</div></td><td><div class=\"dni-plaintext\">$72.32 billion</div></td></tr><tr><td>3</td><td><div class=\"dni-plaintext\">2010</div></td><td><div class=\"dni-plaintext\">12</div></td><td><div class=\"dni-plaintext\">165.48</div></td><td><div class=\"dni-plaintext\">Igor</div></td><td><div class=\"dni-plaintext\">$7.4&#160;billion</div></td></tr><tr><td>4</td><td><div class=\"dni-plaintext\">1950</div></td><td><div class=\"dni-plaintext\">11</div></td><td><div class=\"dni-plaintext\">211.28</div></td><td><div class=\"dni-plaintext\">Dog</div></td><td><div class=\"dni-plaintext\">$37&#160;million</div></td></tr><tr><td>5</td><td><div class=\"dni-plaintext\">2005</div></td><td><div class=\"dni-plaintext\">15</div></td><td><div class=\"dni-plaintext\">250.13</div></td><td><div class=\"dni-plaintext\">Wilma</div></td><td><div class=\"dni-plaintext\">$180.7&#160;billion</div></td></tr><tr><td>6</td><td><div class=\"dni-plaintext\">1998</div></td><td><div class=\"dni-plaintext\">10</div></td><td><div class=\"dni-plaintext\">181.77</div></td><td><div class=\"dni-plaintext\">Mitch</div></td><td><div class=\"dni-plaintext\">$12.2&#160;billion</div></td></tr><tr><td>7</td><td><div class=\"dni-plaintext\">1969</div></td><td><div class=\"dni-plaintext\">12</div></td><td><div class=\"dni-plaintext\">165.74</div></td><td><div class=\"dni-plaintext\">Camille</div></td><td><div class=\"dni-plaintext\">$1.7&#160;billion</div></td></tr></tbody></table>"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "var results = from storm in hurricanes\n",
    "              orderby storm.DamageUSD descending\n",
    "              where storm.HurricaneCount >= 10\n",
    "              select new {storm.Year, storm.HurricaneCount, storm.ACE, storm.StrongestStorm, storm.DamageUSD};\n",
    "\n",
    "results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extension Methods\n",
    "\n",
    "[Extension Methods](https://docs.microsoft.com/dotnet/csharp/programming-guide/classes-and-structs/extension-methods?WT.mc_id=visualstudio-twitch-jefritz) are a way to extend the functionality of a type without modifying the existing type.  You don't even need access to the original type's source code to add on a feature to the type.  We've seen examples of extension methods in use with the various predicate methods in the LINQ to Objects discussion above.  The `FritzSet<T>` object did not have all of the query interaction methods writte on it, but they were available to manipulate the collection.\n",
    "\n",
    "To create our own extension methods, you create them in a `static class` as methods with a signature where the first argument is prefixed by `this` to indicate the type being amended.  That class must be at the top-level and not hosted inside of another class.\n",
    "\n",
    "**NOTE:** This code does not work in .NET Interactive with Jupyter Notebooks due to how .NET Interactive compiles and hosts code.  The code is formatted here and does run in .NET applications\n",
    "\n",
    "```csharp\n",
    "static class CardExtensions {\n",
    "    \n",
    "    public static string ToFormattedValue(this Card theCard) {\n",
    "        \n",
    "        var outSuit = theCard.Suit == \"c\" ? \"♣\" : theCard.Suit == \"d\" ? \"♦\" : theCard.Suit == \"h\" ? \"♥\" : \"♠\";\n",
    "        return theCard.Rank + outSuit;\n",
    "        \n",
    "    }\n",
    "    \n",
    "}\n",
    "\n",
    "\n",
    "Card myCard = new Card(\"A-h\");\n",
    "myCard.ToFormattedValue();\n",
    "```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "file_extension": ".cs",
   "mimetype": "text/x-csharp",
   "name": "C#",
   "pygments_lexer": "csharp",
   "version": "8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}