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

score 4 · Answer 1 · answered Sep 09 '09 at 01:19

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.

score 3 · Accepted Answer · answered Sep 08 '09 at 13:06

Maybe you could try taking a step back and seeing what you want to do with the relation? I'm assuming you want to display this information to the user in e.g. "modified 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've used an anonymous type there for (imo) clarity. You could add those properties to your User type if you preferred.

As for your second question, your normal LoadWith(x => x.AddedByUser) etc. should work just fine - although I tend to prefer storing the description string directly in the database - you've got a trade-off between your description updating when ChangedByUser.FullName changes and having to do something complicated and possibly counterintuitive if the ChangedByUser gets deleted (e.g. ON DELETE CASCADE, or dealing with a null ChangedByUser in your code).

Yes I do want to display that info in my GUI. I was hoping to solve this problem without the use of anonymous types. But i guess it IS one way way of solving the problem. Is my problem related to the Linq to SQL's inability to cope with many to many relationships? — Saajid Ismail, Sep 08 '09 at 14:27
Na, you could add User.AddedDescription and User.ChangedDescription fields to the User class and then you wouldn't have to use an anon type. It's not really anything to do with the many-many relationship (each relation in your example is 1:1). If you were writing your SQL directly, you wouldn't want to get all the properties of the AddedByUser (including his AddedByUser and so on into infinity). You'd probably just grab his name and/or whatever details you needed for your view. Imo it's a more general O/RM problem. I think you'd have the same issue with L2E or Hibernate. — Iain Galloway, Sep 09 '09 at 10:56

score 0 · Answer 3 · answered Sep 08 '09 at 04:09

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

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

3 Answers3