title: Eager loading of Linq to SQL Entities in a self referencing table
body: <p>I have 2 related Linq to SQL questions. Please see the image below to see what my Model looks like.</p>
  
  <p><strong>Question 1</strong></p>
  
  <p>I am trying to figure how to eager load the <code>User.AddedByUser</code> field on my <code>User</code>   class/table. This field is generated from the relationship on the <code>User.AddedByUserId</code> field. The table is self-referencing, and I am trying to figure out how to get Linq to SQL to load up the <code>User.AddedByUser</code> property eagerly, i.e. whenever any <code>User</code> entity is loaded/fetched, it must also fetch the User.AddedByUser and User.ChangedByUser. However, I understand that this could become a recursive problem... </p>
  
  <p><em>Update 1.1:</em></p>
  
  <p>I've tried to use the DataLoadOptions as follows:</p>
  
  <pre><code>var options = new DataLoadOptions();
  options.LoadWith&lt;User&gt;(u =&gt; u.ChangedByUser);
  options.LoadWith&lt;User&gt;(u =&gt; u.AddedByUser);
  
  db = new ModelDataContext(connectionString);
  db.LoadOptions = options;
  </code></pre>
  
  <p>But this doesn't work, I get the following exception on Line 2:</p>
  
  <pre><code>System.InvalidOperationException occurred
    Message="Cycles not allowed in LoadOptions LoadWith type graph."
    Source="System.Data.Linq"
    StackTrace:
         at System.Data.Linq.DataLoadOptions.ValidateTypeGraphAcyclic()
         at System.Data.Linq.DataLoadOptions.Preload(MemberInfo association)
         at System.Data.Linq.DataLoadOptions.LoadWith[T](Expression`1 expression)
         at i3t.KpCosting.Service.Library.Repositories.UserRepository..ctor(String connectionString) in C:\Development\KP Costing\Trunk\Code\i3t.KpCosting.Service.Library\Repositories\UserRepository.cs:line 15
    InnerException:
  </code></pre>
  
  <p>The exception is quite self-explanatory - the object graph isn't allowed to be Cyclic.
  Also, assuming Line 2 didn't throw an exception, I'm pretty sure Line 3 would, since they are duplicate keys.</p>
  
  <p><em>Update 1.2</em>:</p>
  
  <p>The following doesn't work either (not used in conjuction with <em>Update 1.1</em> above):</p>
  
  <pre><code>var query = from u in db.Users
              select new User()
              {
                  Id = u.Id,
                  // other fields removed for brevityy
                  AddedByUser = u.AddedByUser,
                  ChangedByUser = u.ChangedByUser,
  
              };
  return query.ToList();
  </code></pre>
  
  <p>It throws the following, self-explanatory exception:</p>
  
  <pre><code>System.NotSupportedException occurred
  Message="Explicit construction of entity type 'i3t.KpCosting.Shared.Model.User' in query is not allowed."
  </code></pre>
  
  <p>I am now REALLY at a loss on how to solve this. Please help!</p>
  
  <p><strong>Question 2</strong></p>
  
  <p>On every other table in my DB, and hence Linq to SQL model, I have two fields, <code>Entity.ChangedByUser</code> (linked to <code>Entity.ChangedByUserId</code> foreign key/relationship) and <code>Entity.AddedByUser</code> (linked to <code>Entity.AddedByUserId</code> foreign key/relationship) </p>
  
  <p>How do I get Linq to SQL to eageryly load these fields for me? Do I need to do a simple join on my queries?, or is there some other way?</p>
  
  <p><a href="http://img245.imageshack.us/img245/5631/linqtosql.jpg" rel="nofollow noreferrer">Linq to SQL eager loading on self referencing table http://img245.imageshack.us/img245/5631/linqtosql.jpg</a></p>