StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>SELECT table1.* , table2.* INNER JOIN table2 ON table2.SomeFkId = table1.SomeId </code></pre> <p>That gets you a result set where child rows in table2 cause duplication by returning the table1 results for each child row in table2. O/R mappers should differentiate table1 instances based on a unique key field, then use all the table2 columns to populate child instances.</p> <pre><code>SELECT table1.* SELECT table2.* WHERE SomeFkId = # </code></pre> <p>The N+1 is where the first query populates the primary object and the second query populates all the child objects for each of the unique primary objects returned.</p> <p>Consider:</p> <pre><code>class House { int Id { get; set; } string Address { get; set; } Person[] Inhabitants { get; set; } } class Person { string Name { get; set; } int HouseId { get; set; } } </code></pre> <p>and tables with a similar structure. A single query for the address "22 Valley St" may return:</p> <pre><code>Id Address Name HouseId 1 22 Valley St Dave 1 1 22 Valley St John 1 1 22 Valley St Mike 1 </code></pre> <p>The O/RM should fill an instance of Home with ID=1, Address="22 Valley St" and then populate the Inhabitants array with People instances for Dave, John, and Mike with just one query.</p> <p>A N+1 query for the same address used above would result in:</p> <pre><code>Id Address 1 22 Valley St </code></pre> <p>with a separate query like</p> <pre><code>SELECT * FROM Person WHERE HouseId = 1 </code></pre> <p>and resulting in a separate data set like</p> <pre><code>Name HouseId Dave 1 John 1 Mike 1 </code></pre> <p>and the final result being the same as above with the single query.</p> <p>The advantages to single select is that you get all the data up front which may be what you ultimately desire. The advantages to N+1 is query complexity is reduced and you can use lazy loading where the child result sets are only loaded upon first request.</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