Bright loading of Linq in SQL entities into the self-reference table

Question

I have 2 related Linq to SQL questions. See the image below to see how my model looks.

Question 1

I am trying to figure out how to eagerly load the User.AddedByUser field into my User class / table class. This field is generated from the relationship in the User.AddedByUserId field. The table is self-reflection, and I'm trying to figure out how to get Linq to SQL to load the User.AddedByUser property with impatience, i.e. whenever a User object is loaded / retrieved, it should also retrieve User.AddedByUser and User.ChangedByUser. However, I understand that this can be a recursive problem ...

Update 1.1:

I tried using 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 does not 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 goes without saying - the object graph is not allowed to be cyclic. Also, assuming line 2 does not throw an exception, I'm sure line 3 will be, as these are duplicate keys.

Update 1.2:

Another thing does not work (it is not used in combination 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-evident exception:

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

Now I REALLY do not understand how to solve this. Please, help!

Question 2

In every other table in my DB database and therefore Linq to SQL, I have two fields: Entity.ChangedByUser (associated with Entity.ChangedByUserId with foreign key / relation) and Entity.AddedByUser (associated with Entity.AddedByUserId with foreign key / relationship)

How do I get Linq to SQL to load these fields for me? Do I need to make a simple connection according to my requests ?, or is there some other way?

Linq to SQL loads self-promotion table http://img245.imageshack.us/img245/5631/linqtosql.jpg

Answer 1

Maybe you could try to take a step back and see what you want to do with this attitude? I assume that you want to display this information to the user, for example. "edited by Iain Galloway 8 hours ago."

Could something like the following work ?: -

 var users = from u in db.Users select new { /* other stuff... */ AddedTimestamp = u.AddedTimestamp, AddedDescription = u.AddedByUser.FullName, ChangedTimestamp = u.ChangedTimestamp, ChangedDescription = u.ChangedByUser.FullName }; 

I used an anonymous type for (imo) clarity. You can add these properties to your user type if you want.

As for your second question, your normal LoadWith (x => x.AddedByUser), etc. should work fine - although I prefer to store the description string directly in the database - you have a trade-off between updating your description when changing ChangedByUser.FullName and having to do something complicated and possibly contradictory if ChangedByUser is deleted (for example, ON DELETE CASCADE or working with null ChangedByUser in your code).

Answer 2

Any type of loop is simply not allowed . Because LoadWith<T> or AssociateWith<T> are applied to each type in context, there is no internal way to prevent an infinite loop. More precisely, he is simply confused about how to create SQL, because SQL Server does not have a CONNECT BY and CTE really went through what Linq can automatically generate with the provided infrastructure.

The best option that is available to you is to manually connect level 1 to the user table for both children and an anonymous type to return them. Sorry, this is not a simple / straightforward solution, but it is really all that is still available with Linq.

Answer 3

Not sure if there is a solution to this problem with Linq to Sql. If you are using Sql Server 2005, you can define a (recursive, like) Stored Procecdure that uses common table expressions to get the result you want and then execute using DataContext.ExecuteQuery.