Eager loading of Linq to SQL Entities in a self referencing table

Question

I have 2 related Linq to SQL questions. Please see the image below to see what my Model looks like.

Question 1

I am trying to figure how to eager load the User.AddedByUser field on my User class/table. This field is generated from the relationship on the User.AddedByUserId field. The table is self-referencing, and I am trying to figure out how to get Linq to SQL to load up the User.AddedByUser property eagerly, i.e. whenever any User entity is loaded/fetched, it must also fetch the User.AddedByUser and User.ChangedByUser. However, I understand that this could become a recursive problem...

Update 1.1:

I've tried to use the DataLoadOptions as follows:

var options = new DataLoadOptions();
options.LoadWith<User>(u => u.ChangedByUser);
options.LoadWith<User>(u => u.AddedByUser);

db = new ModelDataContext(connectionString);
db.LoadOptions = options;

But this doesn't work, I get the following exception on Line 2:

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:

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.

Update 1.2:

The following doesn't work either (not used in conjuction with Update 1.1 above):

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();

It throws the following, self-explanatory exception:

System.NotSupportedException occurred
Message="Explicit construction of entity type 'i3t.KpCosting.Shared.Model.User' in query is not allowed."

I am now REALLY at a loss on how to solve this. Please help!

Question 2

On every other table in my DB, and hence Linq to SQL model, I have two fields, Entity.ChangedByUser (linked to Entity.ChangedByUserId foreign key/relationship) and Entity.AddedByUser (linked to Entity.AddedByUserId foreign key/relationship)

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?

Linq to SQL eager loading on self referencing table http://img245.imageshack.us/img245/5631/linqtosql.jpg

Answer 1

Any type of cycles just aren't allowed. Since the LoadWith<T> or AssociateWith<T> are applied to every type on the context, there's no internal way to prevent an endless loop. More accurately, it's just confused on how to create the SQL since SQL Server doesn't have CONNECT BY and CTEs are really past what Linq can generate automatically with the provided framework.

The best option available to you is to manually do the 1 level join down to the user table for both of the children and an anonymous type to return them. Sorry it's not a clean/easy solution, but it's really all that's available thus far with Linq.