title: Resource usage in interconnected web of objects
body: <p>I have on hand a system of database-driven entities which very seriously use the relational aspect of the database.</p>
  
  <p>Like for example, a table to describe animals.
  Then a table to describe insects.
  Then a table that describes relationships between animals and insects and the nature of it. This table is keyed by foreign keys from the first two tables.</p>
  
  <p>In the end, I get an entity Animal that looks like this:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Animal {
  Guid ID;
  String Name;
  Range&lt;Int&gt; mass;
  Enum physique;
  Enum dietType;
  Enum lifestyleType;
  IQUeryable&lt;Insects&gt; insects;
  }
  </code></pre>
  
  <p>Where each Insects is like this:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Insects{
  Guid ID;
  Guid animalID;
  Guid insectID;
  Animal animal;
  Insect insect;
  Enum relationship;
  }
  </code></pre>
  
  <p>Relationships can be something like “animal eats insect”, or “insect feeds on animal corpse”, or “symbiosis”, or “parasitic”, etc…</p>
  
  <p>The entity Insect is as follows:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class Insect{
  Guid ID;
  String name;
  Enum wingType;
  Enum socialStructure;
  String camouflage;
  IQueryable&lt;Animals&gt; animals;
  }
  </code></pre>
  
  <p>Where each Animals is an alias for entity “Insects” we saw previously.</p>
  
  <p>So when I create a new EcosystemEntities db, I can query it for… let’s say <code>db.Animals.Where(x =&gt; x.Name.Contains("Lupus")).ToList()</code></p>
  
  <p>This gives me a List where each Animal contains a List, where each Insects contains an Insect, where Insect contains List, where each Animals contains an Animal, where each Animal … et cetera.</p>
  
  <p>Potentially I can have any single Animal, through these links, connected to all other insects and animals in the database. When I have a single Animal variable like that, is it de-facto equivalent to loading whole <code>EcosystemEntities db</code> into memory, just more difficult to traverse? What happens to a List? Does it essentially have <code>EcosystemEntities db</code> repeated i times? Would I be better off using a single <code>EcosystemEntities db</code> and multiple <code>List&lt;Guid&gt;</code> by which I would use <code>db.Insects.Where(i =&gt; db.Relationships.Where(r =&gt; animalIDs.Contains(r.animalID)).Contains(i.ID)).Select(i =&gt; i.name).ToList();</code>? Should I be worried about performance when I'm using Linq in this context? </p>
  
  <p>My database can describe tens of thousands of distinct entities across, say, 15 types, and the number of connections between just two <strong>types</strong> of entities is in hundreds of thousands or even over a million.</p>