StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h2>Behaviour</h2> <p>Suppose you have two lists:</p> <pre><code>Id Value 1 A 2 B 3 C Id ChildValue 1 a1 1 a2 1 a3 2 b1 2 b2 </code></pre> <p>When you <strong><code>Join</code></strong> the two lists on the <code>Id</code> field the result will be:</p> <pre><code>Value ChildValue A a1 A a2 A a3 B b1 B b2 </code></pre> <p>When you <strong><code>GroupJoin</code></strong> the two lists on the <code>Id</code> field the result will be:</p> <pre><code>Value ChildValues A [a1, a2, a3] B [b1, b2] C [] </code></pre> <p>So <code>Join</code> produces a flat (tabular) result of parent and child values.<br> <code>GroupJoin</code> produces a list of entries in the first list, each with a group of joined entries in the second list.</p> <p>That's why <code>Join</code> is the equivalent of <code>INNER JOIN</code> in SQL: there are no entries for <code>C</code>. While <code>GroupJoin</code> is the equivalent of <code>OUTER JOIN</code>: <code>C</code> is in the result set, but with an empty list of related entries (in an SQL result set there would be a row <code>C - null</code>).</p> <h2>Syntax</h2> <p>So let the two lists be <code>IEnumerable<Parent></code> and <code>IEnumerable<Child></code> respectively. (In case of Linq to Entities: <code>IQueryable<T></code>). </p> <p><strong><code>Join</code></strong> syntax would be</p> <pre class="lang-cs prettyprint-override"><code>from p in Parent join c in Child on p.Id equals c.Id select new { p.Value, c.ChildValue } </code></pre> <p>returning an <code>IEnumerable<X></code> where X is an anonymous type with two properties, <code>Value</code> and <code>ChildValue</code>. This query syntax uses the <a href="http://msdn.microsoft.com/en-us/library/bb534675.aspx" rel="noreferrer"><code>Join</code></a> method under the hood.</p> <p><strong><code>GroupJoin</code></strong> syntax would be</p> <pre class="lang-cs prettyprint-override"><code>from p in Parent join c in Child on p.Id equals c.Id into g select new { Parent = p, Children = g } </code></pre> <p>returning an <code>IEnumerable<Y></code> where Y is an anonymous type consisting of one property of type <code>Parent</code> and a property of type <code>IEnumerable<Child></code>. This query syntax uses the <a href="http://msdn.microsoft.com/en-us/library/bb534297.aspx" rel="noreferrer"><code>GroupJoin</code></a> method under the hood.</p> <p>We could just do <code>select g</code> in the latter query, which would select an <code>IEnumerable<IEnumerable<Child>></code>, say a list of lists. In many cases the select with the parent included is more useful.</p> <h2>Some use cases</h2> <h3>1. Producing a flat outer join.</h3> <p>As said, the statement ...</p> <pre class="lang-cs prettyprint-override"><code>from p in Parent join c in Child on p.Id equals c.Id into g select new { Parent = p, Children = g } </code></pre> <p>... produces a list of parents with child groups. This can be turned into a flat list of parent-child pairs by two small additions:</p> <pre class="lang-cs prettyprint-override"><code>from p in parents join c in children on p.Id equals c.Id into g // <= into from c in g.DefaultIfEmpty() // <= flattens the groups select new { Parent = p.Value, Child = c?.ChildValue } </code></pre> <p>The result is similar to</p> <pre class="lang-cs prettyprint-override"><code>Value Child A a1 A a2 A a3 B b1 B b2 C (null) </code></pre> <p>Note that the <em>range variable</em> <code>c</code> is reused in the above statement. Doing this, any <code>join</code> statement can simply be converted to an <code>outer join</code> by adding the equivalent of <code>into g from c in g.DefaultIfEmpty()</code> to an existing <code>join</code> statement.</p> <p>This is where query (or comprehensive) syntax shines. Method (or fluent) syntax shows what really happens, but it's hard to write:</p> <pre class="lang-cs prettyprint-override"><code>parents.GroupJoin(children, p => p.Id, c => c.Id, (p, c) => new { p, c }) .SelectMany(x => x.c.DefaultIfEmpty(), (x,c) => new { x.p.Value, c?.ChildValue } ) </code></pre> <p>So a flat <code>outer join</code> in LINQ is a <code>GroupJoin</code>, flattened by <code>SelectMany</code>.</p> <h3>2. Preserving order</h3> <p>Suppose the list of parents is a bit longer. Some UI produces a list of selected parents as <code>Id</code> values in a fixed order. Let's use:</p> <pre class="lang-cs prettyprint-override"><code>var ids = new[] { 3,7,2,4 }; </code></pre> <p>Now the selected parents must be filtered from the parents list in this exact order.</p> <p>If we do ...</p> <pre class="lang-cs prettyprint-override"><code>var result = parents.Where(p => ids.Contains(p.Id)); </code></pre> <p>... the order of <code>parents</code> will determine the result. If the parents are ordered by <code>Id</code>, the result will be parents 2, 3, 4, 7. Not good. However, we can also use <code>join</code> to filter the list. And by using <code>ids</code> as first list, the order will be preserved:</p> <pre class="lang-cs prettyprint-override"><code>from id in ids join p in parents on id equals p.Id select p </code></pre> <p>The result is parents 3, 7, 2, 4.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload